TRIGGERS IN DATABASE MANAGEMENT SYSTEM.ppt

A trigger is a stored procedure in a database that automatically invokes whenever a special event in the database occurs.

1. TRIGGER
1

2. TRIGGERS
A trigger is a statement that the system executes
automatically as a side effect of a modification to
the database.
Triggers are stored in database as a simple
database objects.
Adatabasethathasasetofassociatedtriggers
iscalledanactivedatabase.
2

3. BENEFITSOFATRIGGER
Generating some derived column values
automatically
Enforcing referential integrity
Event logging and storing information on table
access
Synchronous replication of tables
Imposing security authorizations
3

4. COMPONENTS OFTRIGGER( E-C-A MODEL)
Event–SQLstatementthatcausesthetriggerto
fire(oractivate).Thiseventmaybeinsert,update
ordeleteoperationdatabasetable.
Condition-Aconditionthatmustbesatisfiedfor
executionoftrigger.
Action-Thisiscodeorstatementthatistobe
executedwhentriggeringconditionissatisfiedand
triggerisactivatedondatabasetable.
4

5. TRIGGERSYNTAX
CREATE [OR REPLACE] TRIGGER
<trigger_name>
<BEFORE | AFTER>
<INSERT | UPDATE | DELETE>
[OF<column_name_list>]
ON<table_name>
[REFERENCING NEW AS <synonym> OLD AS
<synonym>]
[FOR EACH ROW] [WHEN(<trigger_condition>)]
BEGIN
<trigger_code>
Triggering
stmt
Trigger Constraint
Trigger Body
5

6. 6
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE
ON customers
FOR EACH ROW
WHEN (NEW.ID >0)
DECLARE sal_diff number;
BEGIN
sal_diff :=:NEW.salary -:OLD.salary;
dbms_output.put_line('Old salary: '||:OLD.salary);
dbms_output.put_line('New salary: '||:NEW.salary);
dbms_output.put_line('Salary difference: '||sal_diff);
END;
/

7. 7

8. 8
Old salary:
New salary: 7500
Salary difference:
UPDATE customers SET salary =salary +500WHERE id =2;
Old salary: 1500
New salary: 2000
Salary difference: 500

9. TRIGGERTYPES
Row Level Triggers
Arowleveltriggerisfiredeachtimethetableis
affectedbythetriggeringstatement.
Forexample,ifanUPDATEstatementupdates
multiplerowsofatable,arowtriggerisfiredoncefor
eachrowaffectedbytheUPDATEstatement.
Ifatriggeringstatementaffectsnorows,arow
triggerisnotrun.
IfFOREACHROWclauseiswrittenthatmeans
triggerisrowleveltrigger
9

10. Statement Level Triggers
Astatementleveltriggerisfiredonceonbehalf
ofthetriggeringstatement,regardlessofthe
numberofrowsinthetablethatthetriggering
statementaffects,evenifnorowsareaffected.
Forexample,ifaDELETEstatementdeletes
severalrowsfromatable,astatement-level
DELETEtriggerisfiredonlyonce.
ByDefaultwhenFOREACHROWclauseisnot
writtenintriggerthatmeanstriggerisstatement
leveltrigger
10

11. TRIGGEROPERATIONS
Data Dictionary for Triggers
Once triggers are created their definitions can be viewed
by selecting it from system tables as shown below
Select *
From user_triggers
Where trigger_name = '<trigger_name>';
This statement will give you all properties of trigger
including trigger code as well.
11

12. DROPPINGTRIGGERS:
To remove trigger from database we use command
DROP
Drop trigger <trigger_name>;
12

13. DISABLINGTRIGGERS
To activate or deactivate trigger temporarily we use
the ALTER trigger command
Alter trigger <trigger_name> {disable | enable};
13