Trigger

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

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

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

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.