TRIGGER

                           TRIGGER

OBJECTIVES

 After reading this  you will able to - 

   how to Create Trigger 

   how to Modify Trigger

  State Enable / Disable trigger 

  State Delete Trigger

 INTRODUCTION 

 A trigger is PL/SQL code block which is executed by an event 

which occurs to a database table. Triggers are implicitly invoked when 

INSERT, UPDATE or DELETE command is executed. A trigger is 

associated to a table or a view. When a view is used, the base table 

triggers are normally enabled. 

 Triggers are stored as text and compiled at execute time, because 

of this it is wise not to include much code in them. You may not use 

COMMIT, ROLLBACK and SAVEPOINT statements within trigger 

blocks. 

 The advantages of using trigger are : 

 1. It creates consistency and access restrictions to the 

database. 

 2. It implements the security. 

CREATING A TRIGGER 

 A trigger is created with CREATE TRIGGER command. 

Syntax : 

 CREATE [OR REPLACE] TRIGGER trigger_name 

 {BEFORE / AFTER / INSTEAD OF} 

 {DELETE /INSERT/UPDATE [OF column [,column….]} 

 [OR {DELETE /INSERT/UPDATE [OF column 

 [,column….]}] 

 ON {TABLE/VIEW} 

 FOR EACH {ROW / STATEMENT} 

 [WHEN (condition)] 

 PL/SQL block. 

 Triggers may be called BEFORE or AFTER the following events. 

 INSERT, UPDATE and DELETE. 

 The BEFORE trigger is used when some processing is

needed before execution of the command. 

 The AFTER trigger is triggered only after the execution of the 

associated triggering commands. 

 INSTEAD OF trigger is applied to view only. 

 Triggers may be ROW or STATEMENT types. 

 ROW type trigger which is also called as ROW level trigger is 

executed on all the rows that are affected by the command. 

 STATEMENT type trigger (STATEMENT level trigger) is triggered 

only once. For example if an DELETE command deletes 15 rows, the 

commands contained in the trigger are executed only once and not 

with every processed row. 

 The trigger can be activated by a SQL command or by system 

event or a user event which are called triggering events. 

 

According to these events, trigger types are : 

1. TABLE triggers : Applied to DML commands (INSERT / 

DELETE / UPDATE). 

 2. SYSTEM EVENT triggers : Such as startup, shutdown of the 

database and server error message event. 

 3. USER EVENT triggers : Such as User logon and logoff, DDL 

commands (CREATE, ALTER, DROP), DML commands 

(INSERT, DELETE, UPDATE). 

WHEN clause is used to specify triggering restriction i.e. it specifies what condition must be true for the trigger to be activated. PL/SQL block is a trigger action. Thus every trigger is divided into three components as : 

 1. Triggering event 

 2. Triggering restriction 

 3. Triggering action. 

ACCESS THE VALUE OF COLUMN INSIDE A TRIGGER 

 A value of a column of a ROW-LEVEL trigger can be accessed 

using NEW and OLD variable. 

Syntax : Column_name : NEW 

 Column_name : OLD 

 Depending on the commands INSERT, UPDATE and DELETE, 

the values NEW and OLD will be used as follows : 

1. INSERT command : The value of the fields that will be 

inserted must be preceded by : NEW 

2. UPDATE command : The original value is accessed with : 

OLD and the new values will be preceded by : NEW. 

3. DELETE command : The values in this case must be 

preceded by : OLD.

For example : 

 SQL> create trigger tr_sal 

 2 before insert on emp 

 3 for each row 

 4 begin 

 5 if :new.sal = 0 then 

 6 Raise_application_error('-20010','Salary should be greater 

than 0'); 

 7 end if; 

 8 end; 

 SQL> / 

 Trigger created. 

 When you insert data into an emp table with salary 0 at that time 

this trigger will get executed. 

ENABLING/ DISABLING A TRIGGER 

 To enable or disable a specific trigger, ALTER TRIGGER 

command is used. 

Syntax : 

 ALTER TRIGGER trigger_name ENABLE / DISABLE ; 

 When a trigger is created, it is automatically enabled and it gets 

executed according to the triggering command. To disable the trigger, 

use DISABLE option as : 

 ALTER TRIGGER tr_sal DISABLE; 

To enable or disable all the triggers of a table, ALTER TABLE command is used. Syntax : ALTER TABLE table_name ENABLE / DISABLE ALL TRIGGERS; For example : ALTER TABLE emp DISABLE ALL TRIGGERS; 

MODIFYING A TRIGGER 

 A trigger can be modified using OR REPLACE clause of CREATE 

TRIGGER command. 

example : 

 SQL> create or replace trigger tr_sal 

 2 before insert on emp 

 3 for each row 

 4 begin 

 5 if :new.sal <=0 then 

 6 Raise_application_error('-20010','Salary should be greater 

than 0'); 

 7 end if; 

 8 end; 

 SQL> / 

 Trigger created. 

 When you insert data into an emp table with salary 0 or less 

than 0 at that time this trigger will get executed. 

DELETING A TRIGGER 

 To delete a trigger, use DROP TRIGGER command. 

 Syntax : DROP TRIGGER trigger_name;

 For example : DROP TRIGGER tr_sal;

A trigger is Pl/SQL code block, which is executed by an event, which occurs to a database table. Triggers are implicitly invoked when Insert, update or delete command is executed. You may not use commit, Roleback and save point commands within trigger blocks.

 According to events triggers are of three types table triggers, system event triggers and user event triggers. To enables or disable a trigger, Alter trigger command is used. By using drop trigger comment we can delete a trigger. 

QUESTIONS-

1) What is trigger ? What are it’s advantages ? 

2) What are the types of trigger ? 

3) Explain with example how will you create trigger. 

4) How will you modify trigger ? 

5) Explain 1. ALTER TRIGGER 

 2. DELETE TRIGGER

1) A trigger is executed by an__________ . 

 2) Startup , shutdown are the_________ level triggers. 

 3) Log on log.off are __________event triggers.