Trigger


Prev Tutorial Next Tutorial

Trigger in PL/SQL

Trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

Purpose of Triggers

Triggers can be written for the following purposes:

  • Generating some derived column values automatically
  • Enforcing referential integrity
  • Event logging and storing information on table access Auditing
  • Synchronous replication of tables
  • Imposing security authorizations
  • To avoid invalid transactions
  • To generate the resulting data automatically.

Part of Trigger

A database trigger has 5 parts.

  • Trigger timing
  • Trigger event or statement
  • Trigger level
  • Trigger restriction
  • Trigger body

Syntax

CREATE [OR REPLACE ] TRIGGER trigger_name 
 {BEFORE | AFTER | INSTEAD OF } 
 {INSERT [OR] | UPDATE [OR] | DELETE} 
 [OF col_name] 
 ON table_name 
 [REFERENCING OLD AS o NEW AS n] 
 [FOR EACH ROW] 
 WHEN (condition)  
 BEGIN 
   --- sql statements  
 END; 
 

Explanation

  • create or replace trigger trigger_name: This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
  • {BEFORE | AFTER | INSTEAD OF } - This clause indicates at what time should the trigger get fired. i.e for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. before and after cannot be used to create a trigger on a view.
  • {INSERT [OR] | UPDATE [OR] | DELETE} - This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword.
  • [REFERENCING OLD AS o NEW AS n] - This clause is used to reference the old and new values of the data being changed.
  • [FOR EACH ROW] - This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e.statement level Trigger).
  • WHEN (condition) - This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.

Write a trigger to stop delete operation on emp_table

Syntax

create or replace trigger mytrigger
before
delete
on emp
begin
raise_application_error(-20000, 'sorry we can not delete any record from this table');
end;
 

Explanation

  • In the above example raise_application_error is a predefined package which contains 2 parameters that is error number and error message.
  • Error number is the range between -40000 to -20999
  • Parameter represent error message which can give maximum 1048 character.

Write a trigger to stop all DML operation on emp_table

Syntax

create or replace trigger mytrig
before	// trigger time
delete or insert or update // statement on emp
on emp
begin
raise_application_error(-20000, 'sorry we can not perform any DML operation on this table');
end;
 

Database trigger

These trigger are written by DBA and it will be fired when the user log on to the database.


Prev Tutorial Next Tutorial

Advertisements




Facebook Likes

Advertisements