Oracle trigger if updating column Chat with sluts no login
The following is sample data: We need to enforce the rule that at most one currency can be primary for a given country.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.When we retrieve that column, the query does not return the updated column values, but other columns are being updated and we are able to see those values. It happens more often than you might think, in real life.) I looked at their trigger and decided immediately that I did not like it.First, their NAME attribute should have been a column in a view (or, in Oracle Database 11, a virtual column).A trigger is just like a subroutine , but most people consider triggers part of the DDL.And just as you would not review a CREATE TABLE statement while reviewing a package implementation, most people do not review the hidden code in a trigger.For example, can you immediately see the huge bug in this trigger?
However, that single feature, when not fully understood by a developer, turns into a liability when the developer turns to triggers, especially triggers that enforce some “rule.” For example, just recently on asktom.oracle.com, I was presented with the following scenario: Suppose we have a table that includes country currency combinations with a primary currency.There was no reason to ever store the result of the function in the table.Second, they blindly overwrite the NAME attribute in the trigger.This name information is derived data based on the values in two other columns.The NAME attribute is the result of a trivial function, the function is not computationally expensive, and they could have indexed the function if necessary.