[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

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

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
0
Mus_Bak
Asked:
Mus_Bak
3 Solutions
 
Jinesh KamdarCommented:
>> 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;
0
 
Mus_BakAuthor 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.
0
 
Mark GeerlingsDatabase AdministratorCommented:
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).
0
 
Jinesh KamdarCommented:
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.
0
 
sujith80Commented:
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

0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now