Subject Re: [firebird-support] storing a sequence value in a trigger
Author Martijn Tonies (Upscene Productions)
Post date 2015-10-06T11:41:52Z
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!
 
Sent: Tuesday, October 06, 2015 1:30 PM
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?