| Subject | storing a sequence value in a trigger |
|---|---|
| Author | G G |
| Post date | 2015-10-06T11:30:46Z |
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 ; ^
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 Error
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
AS
declare 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 Error
SQL error code = -104
Token unknown - line 7, column 12
select
What is the correct way to achieve what I want to do?