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



Download Projects

Advertisements

FREE Online Google Play APK Downloader

Buy APP Installs, Buy 100% Genuine Installs