1、SQL: Structured Query LanguageChapter 5 (cont.)ConstraintsTriggersIntegrity ConstraintsConstraint 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 prevent

2、inconsistencies e.g., sname has to be a string, age must be = 1 AND rating = 1 AND VALUE = 10)More Powerful Table ConstraintsCREATE TABLE Reserves( sname CHAR(10),bid INTEGER,day DATE,PRIMARY KEY (bid,day),CONSTRAINT noInterlakeResCHECK (Interlake ( SELECT B.bnameFROM Boats BWHERE B.bid= bid) Constr

3、aint that Interlake boats cannot be reserved: If condition evaluates to FALSE, update is rejected.Table Constraints Associated with one table Only needs to hold TRUE when table is non-empty. Table Constraints with Complex CHECKCREATE TABLE Sailors( sid INTEGER,sname CHAR(10),rating INTEGER,age REAL,

4、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!Number of boats plus number of sailors is 100 Assertions: Constraints over Mult

5、iple RelationsCREATE TABLE Sailors( sid INTEGER,sname CHAR(10),rating INTEGER,age REAL,PRIMARY KEY (sid),) ASSERTION not associated with either table.CREATE ASSERTION smallClubCHECK ( (SELECT COUNT (S.sid) FROM Sailors S)+ (SELECT COUNT (B.bid) FROM Boats B) 100 )Number of boatsplus number of sailor

6、s is 100 Triggers (Active database)Trigger: A procedure that starts automatically if specified changes occur to the DBMSAnalog to a daemon that monitors a database for certain events to occurThree parts:Event (activates the trigger)Condition (tests whether the triggers should run) OptionalAction (wh

7、at happens if the trigger runs)Semantics:When an event occurs, And this condition is satisfied, Then the associated action is performed.Triggers Event,Condition,ActionEvents could be : BEFORE|AFTER INSERT|UPDATE|DELETE ON e.g.: BEFORE INSERT ON ProfessorCondition is SQL expression that or even an SQ

8、L query (A query with a 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”.Example TriggerAssume our DB has a relation schema : Professor (pNum, pName, salary)We want to write a trigger that

9、: Ensures that any new professor inserted has salary = 90000Example TriggerCREATE TRIGGER minSalary BEFORE INSERT ON Professor for what context ?BEGIN check for violation here ?END;Example TriggerCREATE TRIGGER minSalary BEFORE INSERT ON Professor FOR EACH ROWBEGINViolation of Minimum Professor Sala

10、ry?END;Example TriggerCREATE TRIGGER minSalary BEFORE INSERT ON Professor FOR EACH ROWBEGINIF (:new.salary 90000)THEN RAISE_APPLICATION_ERROR (-20004, Violation of Minimum Professor Salary);END IF;END;Example triggerCREATE TRIGGER minSalary BEFORE INSERT ON Professor FOR EACH ROWDECLARE temp int; -

11、dummy variable not neededBEGINIF (:new.salary 90000)THEN RAISE_APPLICATION_ERROR (-20004, Violation of Minimum Professor Salary);END IF;temp := 10;- to illustrate declared variablesEND;.Note: In SQLPLUS, dont forget the “dot” after the trigger.Details of Trigger ExampleBEFORE INSERT ON Professor Thi

12、s trigger is checked before the tuple is insertedFOR EACH ROW specifies that trigger is performed for each row inserted:new refers to the new tuple insertedIf (:new.salary 90000) then an application error is raised and hence the row is not inserted; otherwise the row is inserted.Use error code: -200

13、04; this is invalid rangeExample Trigger Using ConditionCREATE TRIGGER minSalary BEFORE INSERT ON ProfessorFOR EACH ROW WHEN (new.salary 90000)BEGINRAISE_APPLICATION_ERROR (-20004, Violation of Minimum Professor Salary);END;Conditions can refer to old/new values of tuples modified by the statement a

14、ctivating the trigger.Triggers: REFERENCINGCREATE TRIGGER minSalary BEFORE INSERT ON Professor REFERENCING NEW as newTuple FOR EACH ROW WHEN (newTuple.salary 90000)BEGINRAISE_APPLICATION_ERROR (-20004, Violation of Minimum Professor Salary);END;Example TriggerCREATE TRIGGER minSalary BEFORE UPDATE O

15、N Professor REFERENCING OLD AS oldTuple NEW as newTuple FOR EACH ROW WHEN (newTuple.salary oldTuple.salary)BEGINRAISE_APPLICATION_ERROR (-20004, Salary Decreasing !);END;Ensure that salary does not decreaseAnother Trigger Example (SQL:99)CREATE TRIGGER youngSailorUpdateAFTER INSERT ON SAILORSREFEREN

16、CING NEW TABLE AS NewSailorsFOR EACH STATEMENTINSERTINTO YoungSailors(sid, name, age, rating)SELECT sid, name, age, ratingFROM NewSailors NWHERE N.age = 18Row vs Statement Level TriggerRow level: activated once per modified tupleStatement level: activate once per SQL statementRow level triggers can

17、access new data, statement level triggers cannot generally do that (depends on DBMS).Statement level triggers will be more efficient if we do not need to make row-specific decisionsRow vs Statement Level TriggerExample: Consider a relation schema : Account (num, amount) where we will allow creation

18、of new accounts only during normal business hours.Example: Statement level triggerCREATE TRIGGER MYTRIG1 BEFORE INSERT ON AccountFOR EACH STATEMENT 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 n

19、ew account now !); END IF;END;Note: “FOR EACH STATEMENT” is default; so in some systems you omit this clause!Another Trigger Example (SQL:99)CREATE TRIGGER youngSailorUpdateAFTER INSERT ON SAILORSREFERENCING NEW TABLE AS NewSailorsFOR EACH STATEMENTINSERTINTO YoungSailors(sid, name, age, rating)SELE

20、CT sid, name, age, ratingFROM NewSailors NWHERE N.age = 18Note: Oracle uses “ FOR EACH STATEMENT” as default, hence you would simply not list this. They also do not support reference to such a delta-table as above.When to use BEFORE/AFTERBased on semantics and also on efficiency considerations.Suppo

21、se 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 ! Combining multiple events into one triggerCREATE TRIGGER salaryRestrictionsAFTER INSERT OR UPDATE ON ProfessorFOR EACH R

22、OWBEGINIF (INSERTING AND :new.salary 90000) THEN RAISE_APPLICATION_ERROR (-20004, below min salary); END IF;IF (UPDATING AND :new.salary :old.salary) THEN RAISE_APPLICATION_ERROR (-20004, Salary Decreasing !); END IF;END;Summary : Trigger Syntax CREATE TRIGGER BEFORE|AFTER INSERT|DELETE|UPDATE OF ON

23、 |REFERENCING OLD AS NEW AS FOR EACH ROW (default is “FOR EACH STATEMENT”)WHEN ();Some Points about TriggersCheck the system tables :user_triggersuser_trigger_colsORA-04091: mutating relation problemIn a row level trigger, you cannot have the body refer to the table specified in the eventAlso INSTEA

24、D OF triggers can be specified on viewsSome Points about TriggersCheck the system tables :user_triggersuser_trigger_colsORA-04091: mutating relation problemIn a row level trigger, you cannot have the body refer to the table specified in the eventAlso INSTEAD OF triggers can be specified on viewsSome

25、 Points about TriggersCheck the system tables :user_triggersuser_trigger_colsORA-04091: mutating relation problemIn a row level trigger, you cannot have the body refer to the table specified in the eventAlso INSTEAD OF triggers can be specified on viewsTriggers and OracleNote 1: Running Triggers. En

26、d CREATE TRIGGER statement with a dot and run:Type the body of the triggerPress . to terminate the sql statementType run to create the triggerExample: CREATE TRIGGER minSalary BEFORE . END;. run;Running the CREATE TRIGGER statement only creates the trigger; it does not execute it. Only a triggering event, such as an insertion, causes the trigger to execute. Triggers and OracleNote 2: Displaying Trigger Definition Errors If