Before Insert trigger with "join"

Database
Fan

Before Insert trigger with "join"

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


1 REPLY
Fan

Re: Before Insert trigger with "join"

up

Thanks a lot