28-02-2013, 11:06 AM
SQL: Structured Query Language(‘Sequel’)
SQL.ppt (Size: 124 KB / Downloads: 296)
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 IC’s:
Fundamental: Domain constraints, primary key constraints, foreign key constraints
General constraints : Check Constraints, Table Constraints and Assertions.
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.
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”.
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