#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    62
    Rep Power
    13

    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 09:29 AM.
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    Bucharest
    Posts
    72
    Rep Power
    12
    create a stored procedure and call it from trigger, it could answer to problem.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    62
    Rep Power
    13
    It works, thanks a lot.
    And is even better, I can call the same procedure from insert, update and delete trigger.
    Thanks again.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    15
    Rep Power
    0
    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) ?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    62
    Rep Power
    13
    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 05:43 AM.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    15
    Rep Power
    0
    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
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    62
    Rep Power
    13
    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 08:30 AM.

IMN logo majestic logo threadwatch logo seochat tools logo