Assertions and Triggers

SQL: Structured Query
Language
(Sequel)
Chapter 5
(cont.)
More on SQL
Constraints
Triggers
Integrity Constraints
(Review)
Constraint describes conditions that every legal

instance of a relation must satisfy.
Inserts/deletes/updates that violate ICs are disallowed.

Can be used to :
ensure application semantics (e.g., sid is a key), or

prevent inconsistencies (e.g., sname has to be a string, age
must be < 200)
Types of ICs:
Fundamental:
Domain constraints, primary key
constraints, foreign key constraints
General constraints : Check Constraints, Table Constraints
and Assertions.
3
Check or Table Constraints

CREATE TABLE Sailors
( sid INTEGER,
sname CHAR(10),
rating INTEGER,
age REAL,
PRIMARY KEY (sid),
CHECK ( rating >= 1
AND rating <=
10 ))
Can use queries to express constraint.

4
Explicit Domain Constraints

CREATE DOMAIN values-of-ratings INTEGER
DEFAULT 1
CHECK ( VALUE >= 1 AND VALUE <= 10)

( sid INTEGER,
sname CHAR(10),
rating values-of-ratings,
age REAL,
PRIMARY KEY (sid))
5
More Powerful Table

Constraints
Constraint that Interlake boats cannot be reserved:

Reserves
( sname CHAR(10),
bid INTEGER,
day DATE,
PRIMARY KEY (bid,day),
CONSTRAINT noInterlakeRes
CHECK (`Interlake <>
( SELECT B.bname
FROM Boats B
WHERE B.bid= bid)))
CREATE TABLE
If condition evaluates to FALSE, update is rejected.

6
Table Constraints
Associated with one table

Only needs to hold TRUE when table is non-empty.
Table Constraints with Complex

CHECK
Number of boats plus number of sailors is < 100
( sid INTEGER,
sname CHAR(10),
rating INTEGER,
age REAL,
PRIMARY KEY (sid),
CHECK
( (SELECT COUNT (S.sid) FROM Sailors S)
+ (SELECT COUNT (B.bid) FROM Boats B)
< 100 )
Symmetric constraint, yet associated with Sailors.

If Sailors is empty, the number of Boats tuples can be
anything!
8
Assertions
( Constraints
over
Number of boats
CREATE TABLE
SailorsMultiple
plus number of
( sid INTEGER,
sailors is < 100
CHAR(10),
Relations) sname
rating INTEGER,
ASSERTION
not
associated
with either
table.
age REAL,
PRIMARY KEY (sid),
CHECK
+ (SELECT COUNT (B.bid) FROM Boats B) < 100 )
CREATE ASSERTION smallClub

CHECK
+ (SELECT COUNT (B.bid) FROM Boats B) < 100 )
9
Triggers (Active database)
Trigger: A procedure that starts automatically if

specified changes occur to the DBMS
Analog to a "daemon" that monitors a

database for certain events to occur
Three parts:
Event (activates the trigger)

Condition (tests whether the triggers should run)
[Optional]
Action (what happens if the trigger runs)
Semantics:
When event occurs, and condition is satisfied, the
action is performed.
10
Triggers
Event,Condition,Action
Events could be :
BEFORE|AFTER INSERT|UPDATE|DELETE ON <tableName>

e.g.:
BEFORE INSERT ON Professor
Condition is SQL expression or even an SQL

query
(query with non-empty result
means TRUE)
Action can be many different choices :

SQL statements , body of PSM, and even DDL and
transaction-oriented statements like commit.
11
Example Trigger
Assume our DB has a relation schema :
Professor (pNum, pName, salary)
We want to write a trigger that :
Ensures that any new professor
inserted
has salary >= 60000
12
Example Trigger
CREATE TRIGGER minSalary BEFORE INSERT ON Professor
for what context
BEGIN
check for violation here ?
END;
13
Example Trigger
FOR EACH ROW
BEGIN
Violation of Minimum Professor Salary?
END;
14
Example Trigger
FOR EACH ROW
BEGIN
IF (:new.salary < 60000)
THEN RAISE_APPLICATION_ERROR (-20004,
Violation of Minimum Professor Salary);
END IF;
END;
15
Example trigger
FOR EACH ROW
DECLARE temp int;
-- dummy variable not needed
BEGIN
IF (:new.salary < 60000)
END IF;
temp := 10;
-- to illustrate declared variables
END;
.
run;
16
Details of Trigger Example
BEFORE INSERT ON Professor

This trigger is checked before the tuple is inserted
FOR EACH ROW
specifies that trigger is performed for each row

inserted
:new
refers to the new tuple inserted
If (:new.salary < 60000)

then an application error is raised and hence the row
is not inserted; otherwise the row is inserted.
Use error code: -20004;

this is in the valid range
17
Example Trigger Using

Condition
FOR EACH ROW
WHEN (new.salary < 60000)
BEGIN
RAISE_APPLICATION_ERROR (-20004,
END;
.
run;
Conditions can refer to old/new values of tuples

modified by the statement activating the trigger.
18
Triggers: REFERENCING
REFERENCING NEW as newTuple
FOR EACH ROW
WHEN (newTuple.salary < 60000)
BEGIN
RAISE_APPLICATION_ERROR (-20004,
END;
.
run;
19
Example Trigger
CREATE TRIGGER minSalary
BEFORE UPDATE ON Professor
REFERENCING OLD AS oldTuple NEW as newTuple
FOR EACH ROW
WHEN (newTuple.salary < oldTuple.salary)
BEGIN
RAISE_APPLICATION_ERROR (-20004, Salary
Decreasing !!);
END;
.
run;
Ensure that salary does not decrease

20
Another Trigger Example

(SQL:99)
CREATE TRIGGER youngSailorUpdate
AFTER INSERT ON SAILORS
REFERENCING NEW TABLE AS NewSailors
FOR EACH STATEMENT
INSERT
INTO YoungSailors(sid, name, age, rating)
SELECT sid, name, age, rating
FROM NewSailors N
WHERE N.age <= 18
21
Row vs Statement Level

Trigger
Row level: activated once per modified tuple

Statement level: activate once per SQL
statement
Row level triggers can access new data,

statement level triggers cannot always do that
(depends on DBMS).
Statement level triggers will be more efficient if

we do not need to make row-specific decisions
22
Row vs Statement Level

Trigger
Example: Consider a relation schema

Account (num, amount)
where we will allow creation of new accounts
only during normal business hours.
23
Example: Statement level

trigger
CREATE TRIGGER MYTRIG1
BEFORE INSERT ON Account
FOR EACH STATEMENT
--- is default
BEGIN
IF (TO_CHAR(SYSDATE,dy) IN (sat,sun))
OR
(TO_CHAR(SYSDATE,hh24:mi) NOT BETWEEN 08:00
AND 17:00)
THEN
RAISE_APPLICATION_ERROR(-20500,Cannot
create new account now !!);
END IF;
END;
24
When to use BEFORE/AFTER
Based on efficiency considerations or

semantics.
Suppose we perform statement-level after

insert, then all the rows are inserted first,
then if the condition fails,
and all the inserted rows
must be rolled back
Not very efficient !!

25
Combining multiple events

into one trigger
CREATE TRIGGER salaryRestrictions
AFTER INSERT OR UPDATE ON Professor
FOR EACH ROW
BEGIN
IF (INSERTING AND :new.salary < 60000) THEN
RAISE_APPLICATION_ERROR (-20004, 'below min
salary'); END IF;
IF (UPDATING AND :new.salary < :old.salary)
Salary Decreasing !!'); END IF;
END;
26
Summary : Trigger Syntax

CREATE TRIGGER <triggerName>
BEFORE|AFTER
INSERT|DELETE|UPDATE
[OF <columnList>] ON <tableName>|<viewName>
[REFERENCING [OLD AS <oldName>] [NEW AS
<newName>]]
[FOR EACH ROW] (default is FOR EACH STATEMENT)
[WHEN (<condition>)]
<PSM body>;
27
Some Points about Triggers
Check the system tables :

user_triggers
user_trigger_cols
user_errors
ORA-04091: mutating relation problem

In a row level trigger, you cannot have the body
refer to the table specified in the event
Also INSTEAD OF triggers can be specified on

views
28
To Show Compilation Errors

SELECT line, position, text
FROM user_errors
WHERE name = 'MY_TRIGGER'
AND TYPE = 'TRIGGER
In SQL*Plus, you can also use the following

shortcut:
SQL> SHOW ERRORS TRIGGER MY_TRIGGER

29
Constraints versus Triggers
Constraints are useful for database consistency

Use IC when sufficient
More opportunity for optimization
Not restricted into insert/delete/update
Triggers are flexible and powerful
Alerters
Event logging for auditing
Security enforcement
Analysis of table accesses (statistics)
Workflow and business intelligence
But can be hard to understand

Several triggers
(Arbitrary order unpredictable !?)
Chain triggers
(When to stop ?)
Recursive triggers (Termination?)
30
Summary
SQL allows specification of rich integrity

constraints and their efficient
maintenance
Triggers respond to changes in the

database: powerful for enforcing
application semantics
31

Assertions and Triggers

Uploaded by

Copyright:

Available Formats

Assertions and Triggers

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Assertions and Triggers

Uploaded by

Copyright:

Available Formats

SQL: Structured Query

Constraint describes conditions that every legal

Inserts/deletes/updates that violate ICs are disallowed.

ensure application semantics (e.g., sid is a key), or

Check or Table Constraints

Can use queries to express constraint.

Explicit Domain Constraints

CREATE TABLE Sailors

More Powerful Table

Constraint that Interlake boats cannot be reserved:

If condition evaluates to FALSE, update is rejected.

Associated with one table

Table Constraints with Complex

Symmetric constraint, yet associated with Sailors.

CREATE ASSERTION smallClub

Triggers (Active database)

Trigger: A procedure that starts automatically if

Analog to a "daemon" that monitors a

Event (activates the trigger)

BEFORE|AFTER INSERT|UPDATE|DELETE ON <tableName>

BEFORE INSERT ON Professor

Condition is SQL expression or even an SQL

Action can be many different choices :

-- dummy variable not needed

-- to illustrate declared variables

Details of Trigger Example

BEFORE INSERT ON Professor

FOR EACH ROW

specifies that trigger is performed for each row

If (:new.salary < 60000)

Use error code: -20004;

Example Trigger Using

Conditions can refer to old/new values of tuples

Ensure that salary does not decrease

Another Trigger Example

Row vs Statement Level

Row level: activated once per modified tuple

Row level triggers can access new data,

Statement level triggers will be more efficient if

Row vs Statement Level

Example: Consider a relation schema

Example: Statement level

When to use BEFORE/AFTER

Based on efficiency considerations or

Suppose we perform statement-level after

Not very efficient !!

Combining multiple events

Summary : Trigger Syntax

Some Points about Triggers

Check the system tables :

ORA-04091: mutating relation problem

Also INSTEAD OF triggers can be specified on

To Show Compilation Errors

In SQL*Plus, you can also use the following

SQL> SHOW ERRORS TRIGGER MY_TRIGGER

Constraints versus Triggers

Constraints are useful for database consistency

Triggers are flexible and powerful

But can be hard to understand