In contrast to passive databases, in active databases, execution of actions can be triggered in response to some monitored events
Usually there are some rules in such databases that react on the external events
Example
These rules are usually expressed via triggers in databases
rollback - abort the transaction that caused the triggered event
Rules usually express various aspects of application semantics and are typically used for maintaining Consistency (databases)
An important application of triggers in Active Databases
This includes:
Derived Data:
There are two strategies for derived data
Triggers are usually a part of transaction
Levels of granularity
Triggering types:
Execution mode:
It supports
Rule processing algorithm
Partial rollback
The execution of triggers is immediate in MS SQL
Types:
CREATE TABLE <name> ON <table> {AFTER | INSTEAD OF} <list of events> as <transact-SQL code>
Special tables that can be used inside triggers:
Inserted
- new or updated rows of the triggered transactionDeleted
- deleted rows (or rows with old state for updates) of the triggered transaction
consider the following schema:
Constraint:
Events that may violate this constraint:
PhDStudent
Laboratory
or Supervisor
in PhDStudent
Laboratory
in Professor
Professor
Events (a) and (b):
create trigger StudSameLabAsSuperv_PhDStud_InsUpd_Abort -- EVENT on PhDStudent after insert, update as -- CONDITION if exists ( select * from Inserted I, Professor P where P.ProfNo = I.Supervisor -- and not the same laboratory and P.Laboratory <> I.Laboratory) begin -- ACTION raiserror 13000 'Constraint Violation: A PhD student must work in the same laboratory as his/her supervisor' rollback end
Event (c)
create trigger StudSameLabAsSuperv_Prof_Upd_Abort on Professor after update as if exists ( -- if there exists a student who is supervised -- by professor who works in different lab select * from Inserted I, PhDStudent S where I.ProfNo = S.Supervisor and I.Laboratory <> S.Laboratory) begin raiserror 13000 'Constraint Violation: A PhD student must work in the same laboratory as his/her supervisor' rollback end
Event (d)
CHECK
constraintalter table Employee add constraint employee_Age18 check (dateadd(year, 18, BDate) <= getdate())
Department.NbrEmployees
is derived from Employee.DNo
create trigger DeptNbrEmp_Employee_InsUpdDel_Derive on Employee after insert, update, delete as begin update Department D set NbrEmployees = (select Count(*) from Employee E where E.DNo = D.DNumber) where D.DNumber in (select distinct I.DNo from Inserted I) or D.DNumber in (select distinct D.DNo from Deleted D) end
Incremental version
create trigger derived_Department_NbrEmployees_Employee on Employee after insert, update, delete as begin update Department set NbrEmployees = NbrEmployees + (select count(*) from Inserted I where DNumber = I.DNo) - (select count(*) from Deleted D where DNumber = D.DNo) where DNumber in (select DNo from Inserted) or DNumber in (select DNo from Deleted) end
Now also need to ensure that no one can modify this attribute
create trigger derived_Department_NbrEmployees_Department on Department after insert, update as if exists (select * from Inserted where NbrEmployees <> (select count(*) from Employee E where E.DNo = DNumber)) begin raiserror 13008 'Constraint Violation: The attribute Department.NbrEmployees is a derived attribute from Employee.DNo' rollback end