Create a trigger
-
I wanted to create a trigger on a table. Whenever an update statement gets executed on the table, the trigger will be fired and a column “UPDATED_DT” of the table will be updated with the current date. i have used the following but it has compilation errors cause with after update i cannot use the new keyword as it is technically incorrect. If works fine when i use BEFORE UPDATE. Could you guide me with this? CREATE OR REPLACE TRIGGER TRG_AU AFTER UPDATE ON X_TABLE FOR EACH ROW BEGIN :NEW.UPDATED_DT := SYSDATE; --error with the new keyboard any other approach END TRG_AU Can someone advice a solution?
-
I wanted to create a trigger on a table. Whenever an update statement gets executed on the table, the trigger will be fired and a column “UPDATED_DT” of the table will be updated with the current date. i have used the following but it has compilation errors cause with after update i cannot use the new keyword as it is technically incorrect. If works fine when i use BEFORE UPDATE. Could you guide me with this? CREATE OR REPLACE TRIGGER TRG_AU AFTER UPDATE ON X_TABLE FOR EACH ROW BEGIN :NEW.UPDATED_DT := SYSDATE; --error with the new keyboard any other approach END TRG_AU Can someone advice a solution?
-
What's the reason you cannot use BEFORE trigger? Before trigger is the only version where you can modify the values to be written to the row.
The need to optimize rises from a bad design.My articles[^]
I have been asked to do so.
-
I have been asked to do so.
Quite odd. If you're asked to modify the values of an updating row inside a trigger, this cannot be done in after trigger. You could possibly update the same row again in the after trigger BUT that would be a recursion and could run until you run out of memory... If the goal is to have the latest update timestamp on every row (implemented using a trigger), I would re-check why before trigger isn't an option.
The need to optimize rises from a bad design.My articles[^]
-
I wanted to create a trigger on a table. Whenever an update statement gets executed on the table, the trigger will be fired and a column “UPDATED_DT” of the table will be updated with the current date. i have used the following but it has compilation errors cause with after update i cannot use the new keyword as it is technically incorrect. If works fine when i use BEFORE UPDATE. Could you guide me with this? CREATE OR REPLACE TRIGGER TRG_AU AFTER UPDATE ON X_TABLE FOR EACH ROW BEGIN :NEW.UPDATED_DT := SYSDATE; --error with the new keyboard any other approach END TRG_AU Can someone advice a solution?
The only way you can change the values of a row being updated is in a
BEFORE UPDATEtrigger.AFTER UPDATEtrigger is triggered "after" the row is updated, and therefore, understandably, the:NEWtable can't be modified."Don't confuse experts with facts" - Eric_V
modified on Friday, August 19, 2011 10:34 AM