Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Unanswered: Problems with Triggers

    Hi,

    I want to make a trigger that makes the following when updating a record,
    let's say the table is A_table:

    -Insert some values of A_table into table B_table.

    -If the value of a determinated field (field1) of A_table changes, then make an insert into C_table.

    The problem is that the insert into B_table it's done everytime, and the insert into C_table only when the value of a field of a_tables changes.

    I have tried with "create trigger A_UPDATE update of field1 on A_table" but then the insert into B_table is done only when field1 changes, not everytime.

    I have tried to make a storedprocedure for C_table, looking if field1 changes, but it makes the insert into C_table everytime a field of A_table changes (I only need it if field1 of A_tables changes).

    Any idea?

    Thanks in advance,

    John Lewis.

  2. #2
    Join Date
    May 2004
    Location
    Barcelona, Spain
    Posts
    54
    Hi John

    You can define something like:

    create trigger name update on A_table referencing old as prv new as nxt
    for each row(

    note that the trigger will launch any time A_table gets updated, no matter which column you update. here you should always update B_table and also update C_table only if prv.field1 <> nxt.field1
    );

    good luck

    iaguigon

  3. #3
    Join Date
    Aug 2004
    Posts
    2
    Yes, but how and where can I write the if sentence?

    In a stored procedure?


    Thanks,

    John Lewis.

  4. #4
    Join Date
    May 2004
    Location
    Barcelona, Spain
    Posts
    54
    John,

    You can write the trigger as a call to a stored procedure (thus writing some if sentence inside for writing your C_table). And it's a good way if you feel comfortable with it.
    But there are alternatives:

    create trigger .....
    for each row (
    when ( prv.field1 <> nxt.field1) ( insert/update/delete statements to control the condition -C_table-, separated by ','),
    (insert/update/delete statements to control general case -B_table-, also separated by ',')
    );

    regards

    iaguigon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •