Hi, I have table (TABLE1) like: EVENT_ID | CODE | DESCRIPTION
When I insert into such a table I don't include DESCRIPTION: "INSERT INTO TABLE1 (EVENT_ID,CODE) VALUES (100,1);"
I want that, before the insert action, the DESCRIPTION field get populated from a domain table (TABLE2) like: CODE | DESCRIPTION, depending on inserted CODE field.
So I'm writing a trigger, but I can't find the right syntax:
REPLACE TRIGGER MYTRIGGER
BEFORE INSERT ON TABLE1
REFERENCING NEW AS NewRow
FOR EACH ROW
SET NewRow.DESCRIPTION = (SELECT TABLE2.DESCRIPTION FROM TABLE2 WHERE TABLE2.CODE = NewRow.CODE);
However I get Error 3612 Incorrect use of a subquery in SET statement of a BEFORE trigger. The wrong syntax seems to be in the "WHERE" statement, because if I replace "WHERE TABLE2.CODE = NewRow.CODE" with "WHERE TABLE2.CODE = 100" it works.
How should I do this?
(P.S.: the example is a nonsense, it is just to point the problem out)
Thanks a lot