Oracle triggers if updating performance
This is non intuitive and you might get surprised by this behaviour.Things will get worse when doing DML inside a trigger, because you could get a chain of reactions: table t1 gets mutated, which fires a trigger that does an insert into t2, which has a trigger that does ... This can be a nightmare to understand and debug and you can imagine some nasty unexpected side effects happening.Way back in 1995/1996, when I started working, I remember my colleagues being very enthusiastic about the upgrade from Oracle6 to Oracle7.Many new features became available, and the one that stood out the most, according to my coworkers, was the birth of database triggers.This problem can of course be slightly alleviated by proper coding and documentation, but it will never be straightforward.
Lucas Jellema wrote a very clear article on the AMIS blog about this exact topic: On the false sense of security with PL/SQL based implementation of business rules - and what to do about it, where he explains why you have to add serialization code by locking.We were using Headstart Utilities, which made the implementation of business rules with database triggers even easier, by offering numerous utilities that generated database trigger code for:- populating the ID column from a sequence- populating auditing columns- standard code for circumventing the well-known mutating table problem- and much more The result was a system that used database triggers heavily which I thought was very robust. This way of dealing with business rules became the de facto standard in Oracle consultancy in the Netherlands for many years to come.Then, somewhere in 2002/2003, I discovered the Ask Tom website, and I was intrigued by the quality of the answers, by the abundance of test cases clearly showing all Tom's points, and by the use of clear and simple language that makes reading for foreigners almost a piece of cake.ORA-20000: Cannot end a customer's last open contract. CONTRACTS_ASU'[email protected] select * from contracts_trigger_approach where customer_id in (617,618) 2 / ID CUSTOMER_ID STARTDATE ENDDATE---------- ----------- ------------------- ------------------- 1233 617 1234 617 1235 618 1236 618 4 rijen zijn [email protected] we can see the business rule is enforced.Note that there is some code as well to lock the customer, so in case another session simultaneously tries to end another contracts from the same customer, then this session gets blocked until the first session is either committed or rollbacked. Please note that I used the phrase "an api approach", because I could think of several valid ones: create package contracts_api 2 as 3 procedure end_contract 4 ( p_contract_id in contracts_api_approach.id%type 5 , p_customer_id in contracts_api_approach.customer_id%type 6 , p_enddate in contracts_api_approach.enddate%type 7 ); 8 end contracts_api; 9 /Package is [email protected] create package body contracts_api 2 as 3 procedure lock_customer (p_customer_id in contracts_trigger_approach.customer_id%type) 4 is 5 l_lock_dummy integer; 6 begin 7 l_lock_dummy := dbms_lock.request 8 ( id = power(2,30) 12 ) 13 ); 14 end lock_customer 15 ; 16 function contract_exists (p_contract_id in contracts_api_approach.id%type) return boolean 17 is 18 l_dummy varchar2(6); 19 begin 20 select 'exists' 21 into l_dummy 22 from contracts_api_approach 23 where id = p_contract_id 24 ; 25 return true; 26 exception 27 when no_data_found then 28 return false; 29 end contract_exists 30 ; 31 procedure end_contract 32 ( p_contract_id in contracts_api_approach.id%type 33 , p_customer_id in contracts_api_approach.customer_id%type 34 , p_enddate in contracts_api_approach.enddate%type 35 ) 36 is 37 begin 38 lock_customer(p_customer_id) 39 ; 40 update contracts_api_approach c1 41 set c1.enddate = p_enddate 42 where c1= p_contract_id 43 and exists 44 ( select 'another open contract from same customer' 45 from contracts_api_approach c2 46 where c2!