Oracle triggers if inserting updating
First, their NAME attribute should have been a column in a view (or, in Oracle Database 11, a virtual column).This name information is derived data based on the values in two other columns.We have a BEFORE UPDATE trigger on the above table for each row (using autonomous transaction to avoid the mutating error) to check whether the country has any primary currency. I knew they had a serious bug on their hands when I read—paraphrasing: create or replace 2 trigger currencies_trigger 3 before update on currencies 4 for each row 5 declare 6 PRAGMA AUTONOMOUS_TRANSACTION; 7 l_cnt number; 8 begin 9 select count(*) 10 into l_cnt 11 from currencies 12 where primary_currency=' Y' 13 and country = :new.country; 14 if ( l_cnt Now, there are many things wrong with this trigger.
Incorrect Implementations The first reason I have for disliking triggers (the long-term maintenance headache) is merely an “I find them a pain in the neck” reason.Triggers make understanding an existing system hard. During a code review, triggers are typically not even looked at in the context of the code being reviewed. The trigger is like a subroutine that is called over and over again.A trigger is just like a subroutine , but most people consider triggers part of the DDL.For example, if you use a trigger to invoke UTL_FILE to write text to a file, that is probably wrong. PUT_LINE calls in a trigger will not roll back; you’ll have written to a file about an event that never happened.If you use a trigger to invoke UTL_HTTP to invoke a service on a Web server, you’ll have invoked that service for an event that never happened.
September/October 2008 Those of you who frequent the asktom.Web site know that I have an aversion to triggers.