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.

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

Yes, but how and where can I write the if sentence?

In a stored procedure?


Thanks,

John Lewis.

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