Error mysql syntax trigger

Question

According phpmyadmin, I have a syntax error in this trigger :

CREATE TRIGGER insert_device
AFTER INSERT ON table_e
FOR EACH ROW

BEGIN

DECLARE m_id_a INTEGER;
DECLARE m_id_d INTEGER;
m_id_d := 0;

SELECT id_a INTO m_id_a FROM table_ua WHERE ua_eui = NEW.eui LIMIT 1;

SELECT id_d INTO m_id_d FROM table_d WHERE d_idapp = m_id_a ORDER BY id asc LIMIT 1;
IF (m_id_d == 0) THEN
    INSERT INTO table_d (d_addr, d_eui, d_apps, d_nwks, d_idapp)
    VALUE (NEW.addr, NEW.eui, NEW.apps, NEW.nwks, m_id_a);
ELSE
    UPDATE TABLE table_d
    SET
      d_addr = NEW.addr,
      d_eui = NEW.eui,
      d_apps = NEW.apps,
      d_nwks = NEW.nwks
    WHERE id_d = m_id_d;
END IF;

END

The error is :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= 0;

What is the error ? I don't understand what I am doing wrong..

Thanks.

Blank · Accepted Answer · 2017-06-29 12:24:04Z

There are several syntax errors in your trigger:

declare a variable, you should invoke it with set;
IF expression can not use ==, just one equal is ok;
update syntax only need to specify table name like UPDATE table_d SET ....

So please try following trigger:

delimiter $$
CREATE TRIGGER insert_device
AFTER INSERT ON table_e
FOR EACH ROW

BEGIN

DECLARE m_id_a INTEGER;
DECLARE m_id_d INTEGER;
set m_id_d = 0;
SELECT id_a INTO m_id_a FROM table_ua WHERE ua_eui = NEW.eui LIMIT 1;

SELECT id_d INTO m_id_d FROM table_d WHERE d_idapp = m_id_a ORDER BY id asc LIMIT 1;
IF (m_id_d = 0) THEN
    INSERT INTO table_d (d_addr, d_eui, d_apps, d_nwks, d_idapp)
    VALUE (NEW.addr, NEW.eui, NEW.apps, NEW.nwks, m_id_a);
ELSE
    UPDATE table_d
    SET
      d_addr = NEW.addr,
      d_eui = NEW.eui,
      d_apps = NEW.apps,
      d_nwks = NEW.nwks
    WHERE id_d = m_id_d;
END IF;

END
$$

Collectives™ on Stack Overflow

Error mysql syntax trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related