next up previous
Next: Functional Dependencies Up: Integrity Constraints Previous: Assertions

Triggers

1. Another feature not present in the SQL standard is the trigger.

   Several existing systems have their own non-standard trigger features.

2. A trigger is a statement that is automatically executed by the system as a side effect of a modification to the database.
3. We need to
   • Specify the conditions under which the trigger is executed.
   • Specify the actions to be taken by the trigger.
4. For example, suppose that an overdraft is intended to result in the account balance being set to zero, and a loan being created for the overdraft amount.

   The trigger actions for tuple t with a negative balance are then

   • Insert a new tuple s in the borrow relation with

      s[bname] = t[bname]
     
     		 		 		 		 tex2html_wrap_inline1042 
     
     		 		 		 		 s[amount] = - t[balance]
     
     		 		 		 		 s[cname] = t[cname] 
     

   • We need to negate balance to get amount, as balance is negative.
   • Set t[balance] to 0.
   Note that this is not a good example. What would happen if the customer already had a loan?
5. SQL-92 does not include triggers. To write this trigger in terms of the original System R trigger:

    define trigger  overdraft 
   
   		 on update of  account T 
   
   		 		 (if new  T.balance  < 0
   
   		 		 then (insert into  loan  values 
   
   		 		 		 		 		 (T.bname, T.account#, - new T.balance)
   
   		 		 		 		 insert into borrower 
   
   		 		 		 		 		 (select cname, account#
   
   		 		 		 		 		 from depositor
   
   		 		 		 		 		 where T.coount# = depositor.account#)
   
   		 		 		 update account S 
   
   		 		 		 set  S.balance  = 0
   
   		 		 		 where S.account# = T.account# ))
   
   

---

Osmar Zaiane
Tue Jun 9 15:12:55 PDT 1998