SQL: Constraints and Triggers - PowerPoint PPT Presentation

About This Presentation
Title:

SQL: Constraints and Triggers

Description:

A CHECK constraint may be expressed over several tables; however, it is often ... Trigger: procedure that starts automatically if specified changes occur to the DBMS ... – PowerPoint PPT presentation

Number of Views:714
Avg rating:3.0/5.0
Slides: 18
Provided by: RaghuRamak246
Category:

less

Transcript and Presenter's Notes

Title: SQL: Constraints and Triggers


1
SQL Constraints and Triggers
  • Chapter 5, 5.7-5.8

2
Integrity Constraints (Review)
  • An IC 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 lt
    200)
  • Types of ICs Domain constraints, primary key
    constraints, foreign key constraints, general
    constraints.
  • Domain constraints Field values must be of
    right type. Always enforced.

3
General Constraints CHECK
  • Syntax CHECK conditional-expression.
  • The conditional expression captures more general
    ICs than keys.
  • The conditional expressions can use queries.
  • The conditional expressions required to hold only
    if the associated table is nonempty.
  • A CHECK constraint may be expressed over several
    tables however, it is often expressed over one
    single table.
  • Constraints can be named
  • CONSTRAINT MyConstraint
  • CHECK conditional-expression

4
CHECK Constraints Examples
Constraint Rating must be in the range 1 to
10 CREATE TABLE Sailors ( sid
INTEGER, sname CHAR(10), rating INTEGER, age
REAL, PRIMARY KEY (sid), CHECK ( rating gt 1
AND rating lt 10 ))
5
CHECK Constraints Examples
Constraint Interlake boats cannot be reserved
6
General Constraints ASSERTION
Constraint Number of boats plus number of
sailors is lt 100
CREATE TABLE Sailors ( 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) lt 100 )
  • This solution is awkward and wrong
  • It is wrongfully associated only with Sailors,
    though it involves both Sailors and Boats.
  • If Sailors is empty, the number of Boats tuples
    can be anything, since the conditional expression
    is not required to hold in such case!

7
General Constraints ASSERTION
Constraint Number of boats plus number of
sailors is lt 100
CREATE ASSERTION smallClub CHECK ( (SELECT
COUNT (S.sid) FROM Sailors S) (SELECT COUNT
(B.bid) FROM Boats B) lt 100 )
  • The assertion is not associated with any one of
    the tables involved.
  • ASSERTION is the right solution not associated
    with either table.

8
General Constraints Further Examples
Schema Students(sid int, sname string, age
int, cgpa real) Works(sid int, pid
int, pct_time int) Projects(did
int, budget real, ta int)
  • Write SQL integrity constraints (domain, key,
    foreign key, or CHECK constraints or assertions)
    for given requirements
  • Students must have a minimum cgpa of 5.
  • Every TA must also be a student.
  • The total percentage of all assignments to
    projects for a given student must be at most
    100.
  • A TA must have a cgpa higher than any student
    that she coaches.

9
General Constraints Further Examples (1)
Constraint Students must have a minimum cgpa of
5. CREATE TABLE Students ( sid
INTEGER, sname CHAR(10), age REAL,
cgpa REAL, PRIMARY KEY
(sid), CHECK ( cgpa gt 5))
Constraint Every TA must also be a
student. CREATE ASSERTION TAisStudent CHECK
( (SELECT COUNT () FROM Projects P
WHERE P.ta_id NOT IN (SELECT sid
FROM Students)) 0 )
10
General Constraints Further Examples (2)
Constraint The total percentage of all
assignments to projects for a student must be
at most 100. CREATE TABLE Works ( sid
INTEGER, pid INTEGER, pct_time
INTEGER, PRIMARY KEY (sid,pid),
FOREIGN KEY (sid) REFERENCES Students,
FOREIGN KEY (pid) REFERENCES
Projects, CHECK ((SELECT COUNT (W.stid)
FROM Works W
GROUP BY W.stid
HAVING SUM(pct_time) gt 100) 0)
11
General Constraints Further Examples (3)
Constraint A TA must have a cgpa higher than any
student that she coaches.
CREATE ASSERTION TAHigherCGPA CHECK
((SELECT COUNT(S.stid) FROM Students S,
Students TA, Works W, Projects P WHERE
S.sidW.sid AND W.pidP.pid AND
P.taTA.sid AND S.cgpa gt TA.cgpa) 0)
12
Triggers
  • Trigger procedure that starts automatically if
    specified changes occur to the DBMS
  • Three parts
  • Event (activates the trigger)
  • Condition (tests whether the triggers should run)
  • Action (what happens if the trigger runs)

13
Triggers
  • BEFORE trigger action executed before the
    activating event occurs.
  • AFTER trigger action executed after the
    activating event occurs.
  • INSTEAD trigger action executed instead of the
    activating event.
  • Row-level trigger executed once per modified row
    (that satisfies the trigger condition).
  • Statement-level trigger executed once per
    modifying statement.
  • Transition variables NEW, OLD, NEW TABLE, OLD
    TABLE.

14
Triggers Example (SQL92)
Increment a count for each newly inserted sailor
whose age lt 18.
  • CREATE TRIGGER incr_count
  • AFTER INSERT ON Sailors
  • WHEN (new.age lt 18)
  • FOR EACH ROW
  • BEGIN
  • countcount1
  • END
  • Illustrates use of NEW to refer to newly
    inserted tuples
  • Exists since SQL92

15
Triggers Example (SQL1999)
Save newly inserted sailors aged lt 18 in a
special table.
  • CREATE TRIGGER youngSailorsUpdate
  • AFTER INSERT ON Sailors
  • REFERENCING NEW TABLE AS NewSailors
  • FOR EACH STATEMENT / This is the default /
  • INSERT
  • INTO YoungSailors(sid, name, age, rating)
  • SELECT sid, name, age, rating
  • FROM NewSailors N
  • WHERE N.age lt 18
  • Illustrates use of NEW TABLE to refer to a set
    of
  • newly inserted tuples
  • Exists since SQL1999

16
Triggers More Elaborated Example
Whenever a student is given a (bonus) raise on
his cgpa, the TAs cgpa must be increased to be
at least as high.
  • CREATE TRIGGER bumpTAcgpa
  • AFTER UPDATE ON Students
  • WHEN OLD.cgpa lt NEW.cgpa
  • FOR EACH ROW
  • BEGIN
  • UPDATE Students S
  • SET S.cgpa NEW.cgpa
  • WHERE S.cgpa lt NEW.cgpa
  • AND S.sid IN (SELECT P.ta
  • FROM STudents S1, Works W,
    Projects P
  • WHERE S1.sid NEW.sid

  • AND S1.sid W.sid

  • AND W.sid P.sid)
  • END
  • Illustrates use of Oracle PL/SQL syntax in the
    action part.

17
Summary
  • SQL allows specification of rich integrity
    constraints
  • Triggers respond to changes in the database
Write a Comment
User Comments (0)
About PowerShow.com