|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
|
|||
|
|||
|
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. |
|
#2
|
||||
|
||||
|
create a stored procedure and call it from trigger, it could answer to problem.
|
|
#3
|
|||
|
|||
|
It works, thanks a lot.
And is even better, I can call the same procedure from insert, update and delete trigger. Thanks again. |
|
#4
|
|||
|
|||
|
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) ?
|
|
#5
|
|||
|
|||
|
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. |
|
#6
|
|||
|
|||
|
the code you have written should work. Just tried:
create table my_table ( some_field integer ); create table my_table_log ( transaction_type char(1), some_field integer ); commit; create trigger my_table_insert for my_table before insert as begin insert into my_table_log(transaction_type, some_field) values('I', new.some_field); end; commit; 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 begin insert into my_table_log(transaction_type, some_field) values( case when inserting then 'I' when updating then 'U' else 'D' end, case when inserting or updating then new.some_field else old.some_field end); end |
|
#7
|
|||
|
|||
|
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. |
![]() |
| Viewing: Dev Shed Forums > Databases > Firebird SQL Development > Insert to another table in trigger code |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|