marți, 26 mai 2015

Oracle trigger: capturing one less column of a table

If you want to fire a trigger when any of columns but one is updated, the solution ca be to put all the wanted columns in the script, but there is an easier way:

CREATE OR REPLACE TRIGGER ....... before UPDATE or delete ON
 .......... REFERENCING old AS old new as new FOR EACH ROW
declare
...............................
BEGIN
...........................
if updating ('unwanted_column') then  

         null;
             else
    ........the action of trigger..........;

end if;
exception
WHEN exc THEN
............

    rollback;
END;
/




Be aware that if you use 

if not updating ('unwanted_column') then
   ........the action of trigger..........;
end if;

you'll notice, when you update two or more columns, and the unwanted column is not included, anyway, the condition will not be satisfied and the action will not be done. Only in the case of updating one column will succeed, which is not a solid solution.
So, use the condition somehow that the action will be on the ELSE   branch.
Enjoy!

Niciun comentariu:

Trimiteți un comentariu