Oracle triggers if updating performance Reallifecam line and mark today
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!
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.
Using a database trigger compared to the alternative - which I'll discuss in a minute - is a bit slower.
But the real pain comes when doing set operations, typically at night in batches.
Another nice example of how to code a business rule correctly can be found as a blog comment on Tom Kyte's blog, written by Toon Koppelaars.
I have spent a lot of time tuning queries and batches lately on a system that was built on Oracle 7.3 with A LOT of triggers.
First of all let me elaborate on the disadvantages of database triggers.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.The first point was one I could immediately agree with, and my initial reaction was: that's the price to pay.It's possible you deliver value 'A' and some trigger changed the value into 'B'.
Because most applications, including Oracle*Forms, almost always do their DML statements immediately followed by a commit, the chances of this happening are reduced a lot, but it remains a fundamental mistake.