August 13th, 2003, 07:21 AM
Insert to another table in trigger code
I want to log changes from a table to a separate log table. I tried using triggers but the code that insert values from current updated table to the log table don't work, the server return "Column does not belong to referenced table" error.
It is possible?
Last edited by badukist; August 13th, 2003 at 08:29 AM.
August 13th, 2003, 11:30 AM
create a stored procedure and call it from trigger, it could answer to problem.
August 14th, 2003, 01:55 AM
It works, thanks a lot.
And is even better, I can call the same procedure from insert, update and delete trigger.
August 27th, 2003, 08:16 AM
Do not understand your problem. I can access whatever from triggers in my code. And why don't you use universal triggers (same trigger for insert, update and/or delete) ?
August 28th, 2003, 04:36 AM
The truth is that I am newbie in SP and triggers language.
I tried to log changes to a separate table with the same structure and 2 extra columns , rowid (the PK) and transaction_type, which can be I,U or D
The code :
INSERT INTO MY_TABLE_LOG (transaction_type , some_field) VALUES ('I', new.some_field) didn't worked in Insert trigger, gave me "Column doesn't belong to referenced table" error message.
If I use universal triggers, how can I know which operation is done (I,U or D) ?
Last edited by badukist; August 28th, 2003 at 04:43 AM.
August 28th, 2003, 05:58 AM
the code you have written should work. Just tried:
create table my_table (
create table my_table_log (
create trigger my_table_insert for my_table before insert as
insert into my_table_log(transaction_type, some_field)
and it worked (tested with Firebird 1.5 RC5 on Linux).
When you declare universal triggers you can check INSERTING/UPDATING/DELETING boolean pseudo-variables to determine action. Here is a simple example:
create or alter trigger my_table_all for my_table before insert or update or delete as
insert into my_table_log(transaction_type, some_field) values(
when inserting then 'I'
when updating then 'U'
when inserting or updating then new.some_field
August 28th, 2003, 07:19 AM
Thanks , I must check again.
I used IBExpert , maybe the way it format keywords/names has to do with the error message.
Seems that I have a lot to learn.
Last edited by badukist; August 28th, 2003 at 07:30 AM.