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
Mus_BakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
SujithData ArchitectCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.