[PDF] - Not found in Oracle. 5. Oracle T riggers. A substitute PDF Document

SLIDE 1 Constrain ts Commercial relational systems allo w m uc h more \ne-tuning"

f

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

reign-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

SLIDE 2 F

reign

Keys In relation R a clause that \attribute A references S (B )" sa ys that whatev er non-n ull v alues app ear in the A column

f

R m ust also app ear in the B column

f

relation S .

B

m ust b e declared the primary k ey for 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

SLIDE 3

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

ne

attribute. 3

SLIDE 4 What Happ ens When a F

reign

Key Constrain t is Violated?

Tw
w

a ys: 1. Insert

r

up date a Sells tuple so it refers to a nonexisten t b eer.

✦

Alw a ys rejected. 2. Delete

r

up date a Beers tuple that has a beer v alue some Sells tuples refer to. a) Default: reject. b) Casc ade : Ripple c hanges to referring Sells tuple. Example

Delete

\Bud." Cascade deletes all Sells tuples that men tion Bud.

Up

date \Bud" ! \Budw eiser." Change all Sells tuples with \Bud" in beer column to b e \Budw eiser." 4

SLIDE 5 c) Set Nul l : Change referring tuples to ha v e NULL in referring comp

nen

ts. Example

Delete

\Bud." Set-n ull mak es all Sells tuples with \Bud" in the beer comp

nen

t ha v e NULL there.

Up

date \Bud" ! \Budw eiser." Same c hange. 5

SLIDE 6 Selecting a P

licy

Add ON [DELETE, UPDATE] [CASCADE, SET NULL] to declaration

f

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

licy

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

SLIDE 7 A ttribute-Based Chec ks F

llo

w an attribute b y a condition that m ust hold for that attribute in eac h tuple

f

its relation.

F
rm:

CHECK (condition).

✦

Condition ma y in v

lv

e the c hec k ed attribute.

✦

Other attributes and relations ma y b e in v

lv

ed, but

nly

in sub queries.

✦

Oracle: No sub queries al lowe d in c

ndition.
Condition

is c hec k ed

nly

when the asso ciated attribute c hanges (i.e., an insert

r

up date

ccurs).

7

SLIDE 8 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

n

beer is lik e a foreign-k ey constrain t, except:

✦

The c hec k

ccurs
nly

when w e add a tuple

r

c hange the b eer in an existing tuple, not when w e delete a tuple from Beers. 8

SLIDE 9 T uple-Based Chec ks Separate elemen t

f

table declaration.

F
rm:

lik e attribute-based c hec k.

But

condition can refer to an y attribute

f

the relation.

✦

Or to

ther

relations/attri butes in sub queries.

✦

Again: Oracle forbids the use

f

sub queries.

Chec

k ed whenev er a tuple is inserted

r

up dated. 9

SLIDE 10 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

SLIDE 11 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: CREATE ASSERTION <name> CHECK(<conditi

n>);

11

SLIDE 12 Example No bar ma y c harge an a v erage

f

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 Sells c hanges. 12

SLIDE 13 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 Bars

r

Drinkers c hanges. 13

SLIDE 14 T riggers (Oracle V ersion) Often called ev en t-condition-acti

n

rules.

Event

= a class

f

c hanges in the DB, e.g., \insertions in to Beers."

Condition

= a test as in a where-clause for whether

r

not the trigger applies.

A

ction =

ne
r

more SQL statemen ts.

Dier

from c hec ks

r

SQL assertions in that triggers are in v

k

ed b y the ev en t; the system do esn't ha v e to gure

ut

when a trigger could b e violated. 14

SLIDE 15 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

SLIDE 16 Options 1. Can

mit

OR REPLACE. But if y

u

do, it is an error if a trigger

f

this name exists. 2. AFTER can b e BEFORE. 3. If the relation is a view, AFTER can b e INSTEAD OF.

✦

Useful for allo wing \mo dications" to a view; y

u

mo dify the underlying relations instead. 4. INSERT can b e DELETE

r

UPDATE OF <attribute>.

✦

Also, sev eral conditions lik e INSERT ON Sells can b e connected b y OR. 5. FOR EACH ROW can b e

mitted,

with an imp

rtan

t eect: the action is done

nce

for the relation(s) consisting

f

all c hanges. 16

SLIDE 17 Notes

More

information in

n-line

do cumen t

r-

plsql.html

There

are t w

sp

ecial v ariables new and

ld,

represen ting the new and

ld

tuple in the c hange.

✦

ld

mak es no sense in an insert, and new mak es no sense in a delete.

Notice:

in WHEN w e use new and

ld

without a colon, but in actions, a preceding colon is needed.

The

action is a PL/SQL statemen t.

✦

Simplest form: surround

ne
r

more SQL statemen ts with BEGIN and END.

✦

Ho w ev er, select-from-where has a limited form. 17

SLIDE 18

Dot

and run cause the denition

f

the trigger to b e stored in the database.

✦

Oracle triggers are part

f

the database sc hema, lik e tables

r

views.

Imp
rtan

t Oracle constrain t: the action cannot c hange the relation that triggers the action.

✦

W

rse,

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

SLIDE 19 Example Main tain a list

f

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 >

ld.price

+ 1.00) BEGIN INSERT INTO RipoffBars VALUES(:new.bar); END; . run 19