Aim – Implement Triggers

Theory

A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database. Triggers are fired implicitly and not called by user like procedure and function. To design a trigger mechanism, we must specify two things:

  • Specify the conditions under which the trigger is to be executed
  • Specify the actions to be taken when the trigger executes

Use of Database Triggers

  • To access table during regular business hours or on predetermined weekdays
  • To keep track of modification of data along with the user name, the operation performed and the time when the operation was performed
  • To prevent invalid transaction
  • Enforces complex security authorization

Database Triggers Vs Procedures

  • Triggers do not accept parameters whereas procedures can have parameters
  • Triggers are executed (fired) automatically upon modification of the table or it’s data whereas to execute a procedure it has to be explicitly called by the user

How To apply Database Triggers

A trigger has three parts

  • A triggering event or statement – An SQL statement that causes a trigger to be fired. It can be insert, update or delete statement for a specific table
  • A trigger restriction – It specifies a Boolean expression that must be TRUE for the trigger to fire. It conditionally controls the execution of trigger. Specified using WHEN clause
  • Trigger Action – PL/SQL block to be executed when triggering statement is encountered and trigger restriction evaluates to TRUE

Types of Triggers

  • Row Triggers – A row trigger is fired each time a row in the table is affected by triggering statement. If the triggering statement affects no rows, the trigger is not executed at all
  • Statement Triggers – A statement trigger is fired once on behalf of the triggering statement, independent of number of rows affected by the triggering statement

Trigger Timing (Before Vs After Triggers)

When defining a trigger it is necessary to specify the trigger timing i. e. when trigger action is to be executed in relation to the triggering Statement. Before and After apply to both row and statement trigger

  • Before Triggers – Trigger action is executed before triggering statement
  • After Triggers – Trigger action is executed after triggering statement

Creating a Trigger

Syntax :

CREATE OR REPLACE TRIGGER [ schema. ]

trigger_name

{ BEFORE, AFTER }

{ DELETE, INSERT, UPDATE [ OF column1, . . . ]

ON [schema.]table_name

[ REFERENCING { OLD AS old, NEW AS new} ]

[ FOR EACH ROW [ WHEN condition ] ]

DECLARE

variable declarations>;

constant declarations>;

BEGIN

< PL/SQL sub-program body >;

Exception

exception PL/SQL block >;

End;

Example :

CREATE OR REPLACE TRIGGER t_Audit_trail

BEFORE DELETE OR UPDATE ON

FOR EACH ROW

DECLARE

oper varchar2(8);

BEGIN

If updating then

oper :=‘Update’

end if;

If deleting then

oper :=‘Delete’

end if;

insert into audit_cust values (:OLD.custno, :OLD.fname, :OLD.lname, :OLD.address, oper, user, sysdate);

End;

Conclusion – Thus we have implemented trigger to protect data of database.