lundi 21 novembre 2016

SQL if condition inside a Trigger

I have 2 tables, lets name it table A and table B, everytime something is inserted in the table A I want some of the data (id, name, comment) automatically inserts also in the table B but only if there is no raw in table B with the same name.

Example:

If I insert OBJECT1 in A with values: id=1, colour=2, name=example, price=5€, comment="blablabla"

it should be inserted in the table B a row with this values: id=1, name=example, comment="blablabla"

but if then I insert OBJECT2 in A with values: id=2, colour=5, name=example, price=10€, comment="aaa";

nothing should happen in the table B, because there is already a row in table B with name=example.

This is my code:

CREATE TRIGGER mytrigger BEFORE INSERT ON tablea
    IF NOT EXISTS (SELECT * FROM tableb WHERE tableb.name = NEW.name)
    THEN
        INSERT INTO tableb (id, name, comment)
            VALUES (NEW.id, NEW.name, NEW.comment)
    END IF

And this is the error I receive: "You have an error in your SQL Syntax..." I also tried adding BEGING and END at the starting and end of the definition, also tried with END instead of END IF, also tried BEGIN instead of THEN

¿Someone knows what am I doing wrong?

Aucun commentaire:

Enregistrer un commentaire