I have three tables Guidance, Assignment and Review. Assignment is a detail table to Guidance and Review is a detail table to Assignment. When a new record is entered into the Guidance table I want to an after insert trigger on the Guidance table to insert default values into Assignment and Review. When the trigger inserts a new record in the assignment table how do I store the primary key for that record in the trigger so I can use it when the trigger goes on to insert a new record in the Review table. I've tried defining the trigger as below SET TERM ^ ; CREATE OR ALTER TRIGGER AI_GDLID FOR GUIDANCE ACTIVE AFTER INSERT POSITION 20 AS DECLARE MyAssId BIGINT; BEGIN MyAssId = insert into assignment(assid,GDLID, OCHID,ASSIGNED_DTE) Values (< error occurs here) (GEN_ID(AssKey_Gen,1),new.GDLID,1,CURRENT_DATE) RETURNING assid
END ^ COMMIT WORK ^ SET TERM ; ^ But I get the following error:Error at line 2: Dynamic SQL ErrorSQL error code = -104Token unknown - line 6, column 13insert I've also tried defining the trigger as: SET TERM ^ ;CREATE OR ALTER TRIGGER AI_GDLID FOR GUIDANCE ACTIVE AFTER INSERT POSITION 20ASdeclare Asskey bigint;BEGIN Asskey = select GEN_ID(AssKey_Gen,1)from rdb$database; insert into assignment(assid,GDLID, OCHID,ASSIGNED_DTE) Values (<--- Error occurs here) (Asskey,new.GDLID,1,CURRENT_DATE); insert into Review(ASSID, REVIEW_DTE, GOTID, NOTE) Values (asskey, CURRENT_Date, 1, 'New Guidance Added');END ^ COMMIT WORK ^SET TERM ;^ However I then get this error message: Error at line 2: Dynamic SQL ErrorSQL error code = -104Token unknown - line 7, column 12select What is the correct way to achieve what I want to do?
