We help IT Professionals succeed at work.

Merge and firing a  trigger on an audit column in the target table

Mus_Bak
Mus_Bak asked
on
Medium Priority
533 Views
Last Modified: 2013-12-07
Hey,

I'm using the merge statement to merge data from a source table into a target table. The target table has an auditing column LAST_UPDATED which is updated by firing the following trigger:  

  IF INSERTING OR UPDATING THEN
    :NEW.LAST_UPDATED    := SYSTIMESTAMP;
  END IF;

My question is whether it is possible to modify this trigger such that it only applies to those rows that are actually updated - that is the value in the LAST_UPDATED column needs to remain the same after the execution of the merge statement for rows that already exist in the target table.

Any alternative solutions are welcome!

/Best regards
Comment
Watch Question

>> the value in the LAST_UPDATED column needs to remain the same after the execution of the merge statement for rows that already exist in the target table.

Do u mean to say for rows that were already there in the target table and were not INSERTed with the MERGE statement, the last_updated field should not be touched? If ye, then just remove that check from the IF conditions.

IF INSERTING THEN
    :NEW.LAST_UPDATED    := SYSTIMESTAMP;
END IF;

Author

Commented:
I'd like to update the LAST_UPDATED column for the rows that are updated, e.g. the updated of one field value.
The LAST_UPDATED column shall remain intact if the rows in the source table are already present in the target table.
Database Administrator
CERTIFIED EXPERT
Commented:
What do you mean by "rows that are actually updated"?  Do you mean just the rows where at least one column value is changed?  If that is what you want, then you may have to add an "if..." statement for every column in the table and compare the :new.column_value to the :old.column_value and only do this line:
  :NEW.LAST_UPDATED    := SYSTIMESTAMP
if at least one of the "new" values is different from one of the "old" values.  Also, you will likely have to use "nvl" with each of the "old" values (to handle inserts correctly) and on the "new" values for any columns where nulls are allowed (to handle an update to a null value correctly).
Mark is right. I don't see of any short-cut way to check if any of the columns of the target table have been affected by the UPDATE or not, unless you decide to put in an explicit column filter.
E.g. If ur source table also has a LAST_UPDATED column, then you can compare the :OLD value with the :NEW value and then determine if the :NEW value needs to be set to SYSTIMESTAMP.
SujithData Architect
CERTIFIED EXPERT
Commented:
Include the columns you are trying to update in the trigger specification.
Say you are updating N columns in the merge statement, you may try something like:

create or replace trigger <trigger name>
before insert or update of <col1>,<col2>,...<colN>
on <your table>
for each row
begin
 .
 .
  IF INSERTING OR UPDATING THEN
    :NEW.LAST_UPDATED    := SYSTIMESTAMP;
  END IF;
 .
 .
end;
/

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.