Hi, Easiest is as follows:
define your sequence trigger to be: IF (coalesce(new.idcolumn, 0) = 0) then new.idcolumn = gen_id(mysequence, 1); Then, in your trigger: declare myassid bigint; begin myassid = gen_id(mysequence, 1); insert into assignment(assid, ... other columns ... ) values (:myassid, ... other columns ... ) /* other inserts */ insert into ... end or insert into assignments(... other columns ... ) values ( ... ) returning_values assid into :myassid; With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! From: mailto:firebird-support@yahoogroups.com Sent: Tuesday, October 06, 2015 1:30 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] storing a sequence value in a trigger 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 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 ; ^ SQL error code = -104 Token unknown - line 6, column 13 insert I've also tried defining the trigger as: SET TERM ^ ; CREATE OR ALTER TRIGGER AI_GDLID FOR GUIDANCE ACTIVE AFTER INSERT POSITION 20 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 ^ However I then get this error message: Error at line 2: Dynamic SQL Error SQL error code = -104 Token unknown - line 7, column 12 select What is the correct way to achieve what I want to do?
