Not found in Oracle. 5. Oracle T riggers. A substitute - PDF document

Constrain ts Commercial relational systems allo w m uc h more \�ne-tuning" of constrain ts than do the mo deling languages w e learned earlier. In essence: SQL programming is used to � describ e constrain ts. Outline 1. Primary k ey declarations (already co v ered). 2. F oreign-k eys = referen tial in tegrit y constrain ts. 3. A ttribute- and tuple-based c hec ks = constrain ts within relations. 4. SQL Assertions = global constrain ts. ✦ Not found in Oracle. 5. Oracle T riggers. ✦ A substitute for assertions. 1

F oreign Keys In relation a clause that \attribute references R A ( B )" sa ys that whatev er non-n ull v alues app ear S in the column of m ust also app ear in the A R B column of relation . S m ust b e declared the primary k ey for . B S � Example CREATE TABLE Beers ( name CHAR(20) PRIMARY KEY, manf CHAR(20) ); CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20) REFERENCES Beers(name), price REAL ); 2

Alternativ e: add another elemen t declaring � the foreign k ey , as: CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20), price REAL, FOREIGN KEY beer REFERENCES Beers(name) ); Extra elemen t essen tial if the foreign k ey is � more than one attribute. 3

What Happ ens When a F oreign Key Constrain t is Violated? Tw o w a ys: � 1. Insert or up date a tuple so it refers to a Sells nonexisten t b eer. ✦ Alw a ys rejected. 2. Delete or up date a tuple that has a Beers v alue some tuples refer to. beer Sells a) Default: reject. b) Casc ade : Ripple c hanges to referring tuple. Sells Example Delete \Bud." Cascade deletes all Sells � tuples that men tion Bud. Up date \Bud" \Budw eiser." Change all � ! tuples with \Bud" in column to Sells beer b e \Budw eiser." 4

c) : Change referring tuples to ha v e Set Nul l in referring comp onen ts. NULL Example Delete \Bud." Set-n ull mak es all tuples Sells � with \Bud" in the comp onen t ha v e beer NULL there. Up date \Bud" \Budw eiser." Same c hange. � ! 5

Selecting a P olicy Add ON [DELETE, UPDATE] [CASCADE, SET NULL] to declaration of foreign k ey . Example CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20), price REAL, FOREIGN KEY beer REFERENCES Beers(name) ON DELETE SET NULL ON UPDATE CASCADE ); \Correct" p olicy is a design decision. � ✦ E.g., what do es it mean if a b eer go es a w a y? What if a b eer c hanges its name? 6

A ttribute-Based Chec ks F ollo w an attribute b y a condition that m ust hold for that attribute in eac h tuple of its relation. F orm: ( condition ) . CHECK � ✦ Condition ma y in v olv e the c hec k ed attribute. ✦ Other attributes and relations ma y b e in v olv ed, but in sub queries. only ✦ Oracle: No sub queries al lowe d in ondition . c Condition is c hec k ed only when the asso ciated � attribute c hanges (i.e., an insert or up date o ccurs). 7

Example CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20) CHECK( beer IN (SELECT name FROM Beers) ), price REAL CHECK( price <= 5.00 ) ); Chec k on is lik e a foreign-k ey constrain t, beer � except: ✦ The c hec k o ccurs only when w e add a tuple or c hange the b eer in an existing tuple, not when w e delete a tuple from Beers . 8

T uple-Based Chec ks Separate elemen t of table declaration. F orm: lik e attribute-based c hec k. � But condition can refer to an y attribute of the � relation. ✦ Or to other relations/attri butes in sub queries. ✦ Again: Oracle forbids the use of sub queries. Chec k ed whenev er a tuple is inserted or � up dated. 9

Example Only Jo e's Bar can sell b eer for more than $5. CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20), price REAL, CHECK(bar = 'Joe''s Bar' OR price <= 5.00) ); 10

SQL Assertions Database-sc hema constrain t. � Not presen t in Oracle. � Chec k ed whenev er a men tioned relation � c hanges. Syn tax: � < name > CREATE ASSERTION CHECK( < conditi on > ); 11

Example No bar ma y c harge an a v erage of more than $5 for b eer. Sells(bar , beer , price) CREATE ASSERTION NoRipoffBars CHECK(NOT EXISTS( SELECT bar FROM Sells GROUP BY bar HAVING 5.0 < AVG(price) ) ); Chec k ed whenev er c hanges. Sells � 12

Example There cannot b e more bars than drink ers. Bars(name , addr, license) Drinkers(name , addr, phone) CREATE ASSERTION FewBar CHECK( (SELECT COUNT(*) FROM Bars) <= (SELECT COUNT(*) FROM Drinkers) ); Chec k ed whenev er or c hanges. Bars Drinkers � 13

T riggers (Oracle V ersion) Often called ev en t-condition-acti on rules. = a class of c hanges in the DB, e.g., Event � \insertions in to Beers ." = a test as in a where-clause for Condition � whether or not the trigger applies. = one or more SQL statemen ts. A ction � Di�er from c hec ks or SQL assertions in that � triggers are in v ok ed b y the ev en t; the system do esn't ha v e to �gure out when a trigger could b e violated. 14

Example Whenev er w e insert a new tuple in to Sells , mak e sure the b eer men tioned is also men tioned in Beers , and insert it (with a n ull man ufacturer) if not. Sells(bar , beer , price) CREATE OR REPLACE TRIGGER BeerTrig AFTER INSERT ON Sells FOR EACH ROW WHEN(new.beer NOT IN (SELECT name FROM Beers)) BEGIN INSERT INTO Beers(name) VALUES(:new.beer); END; . run 15

Options 1. Can omit REPLACE . But if y ou do, it is an OR error if a trigger of this name exists. 2. can b e BEFORE . AFTER 3. If the relation is a view, can b e AFTER INSTEAD OF . ✦ Useful for allo wing \mo di�cations" to a view; y ou mo dify the underlying relations instead. 4. can b e or INSERT DELETE UPDATE OF < attribute > . ✦ Also, sev eral conditions lik e INSERT ON can b e connected b y OR . Sells 5. can b e omitted, with an FOR EACH ROW imp ortan t e�ect: the action is done once for the relation(s) consisting of all c hanges. 16

Notes More information in on-line do cumen t or- � plsql.html There are t w o sp ecial v ariables and old , new � represen ting the new and old tuple in the c hange. ✦ mak es no sense in an insert, and old new mak es no sense in a delete. Notice: in w e use and without WHEN new old � a colon, but in actions, a preceding colon is needed. The action is a PL/SQL statemen t. � ✦ Simplest form: surround one or more SQL statemen ts with and END . BEGIN ✦ Ho w ev er, select-from-where has a limited form. 17

Dot and cause the de�nition of the trigger run � to b e stored in the database. ✦ Oracle triggers are part of the database sc hema, lik e tables or views. Imp ortan t Oracle constrain t: the action � cannot c hange the relation that triggers the action. ✦ W orse, the action cannot ev en c hange a relation connected to the triggering relation b y a constrain t, e.g., a foreign-k ey constrain t. 18

Example Main tain a list of all the bars that raise their price for some b eer b y more than $1. Sells(bar , beer , price) RipoffBars(bar ) CREATE TRIGGER PriceTrig AFTER UPDATE OF price ON Sells FOR EACH ROW WHEN(new.price > old.price + 1.00) BEGIN INSERT INTO RipoffBars VALUES(:new.bar); END; . run 19