Trigger Mutation Problem [message #20161] |
Thu, 02 May 2002 13:34 |
sql gurus
Messages: 22 Registered: May 2002
|
Junior Member |
|
|
Hi,
I want to update a column A of table A when there is an update on coulumn B of table A.
How to write a trigger for this kind.
Any thoughts appreciated.
|
|
|
|
Re: Trigger Mutation Problem [message #20168 is a reply to message #20161] |
Fri, 03 May 2002 04:59 |
Christopher Beattie
Messages: 5 Registered: January 2001
|
Junior Member |
|
|
If you are updating the same row, the procedure is simple; you in effect update the column directly. (The :new.column value.) But this only works on BEFORE triggers, because the fields have already been changed by the time you get to the AFTER triggers.
Here is an simple example, this updates fields in the tables for who last updated the record and when, but the logic is easy to follow, the if could be based on another column, either the :new or the :old, depending on the situation.
BEGIN
IF inserting THEN
:new.create_date := SYSDATE;
:new.create_by := USER;
:new.modify_date := SYSDATE;
:new.modify_by := USER;
ELSIF updating THEN
:new.modify_date := SYSDATE;
:new.modify_by := USER;
END IF;
END;
If you need to update another row in the table, then you have a more complex problem on your hands.
|
|
|
|