Login

Username:

Password: 

Join us Now |  Forgot Password? | Forgot UserName?

PL/SQL

Learn step by step



What is a Trigger?

A 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.

PL/SQL Triggers Syntax

PL/SQL trigger define using CREATE TRIGGER statement.

CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE | AFTER
[INSERT, UPDATE, DELETE [COLUMN NAME..]
ON table_name

Referencing [ OLD AS OLD | NEW AS NEW ]
FOR EACH ROW | FOR EACH STATEMENT [ WHEN Condition ]

DECLARE
[declaration_section
variable declarations;
constant declarations;
]

BEGIN
[executable_section
PL/SQL execute/subprogram body
]

EXCEPTION
[exception_section
PL/SQL Exception block
]

END;

Syntax 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. The trigger gets fired at all the specified triggering event.
  • [OF col_name]:This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.
  • CREATE [OR REPLACE ] TRIGGER trigger_name:This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
  • [ON table_name]:This clause identifies the name of the table or view to which the trigger is associated.
  • [REFERENCING OLD AS o NEW AS n] This clause is used to reference the old and new values of the data being changed. By default, you reference the values as :old.column_name or :new.column_name. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.
  • [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.

Type of Triggers

Triggers are following types:

1.BEFORE Trigger :

BEFORE trigger execute before the triggering DML statement (INSERT, UPDATE, DELETE) execute. Triggering SQL statement is may or may not execute, depending on the BEFORE trigger conditions block.

2.AFTER Trigger :

AFTER trigger execute after the triggering DML statement (INSERT, UPDATE, DELETE) executed. Triggering SQL statement is execute as soon as followed by the code of trigger before performing Database operation.

3.ROW Trigger :

ROW trigger fire for each and every record which are performing INSERT, UPDATE, DELETE from the database table. If row deleting is define as trigger event, when trigger file, deletes the five rows each times from the table.

4.Statement Trigger :

Statement trigger fire only once for each statement. If row deleting is define as trigger event, when trigger file, deletes the five rows at once from the table.

5.Combination Trigger :

Combination trigger are combination of two trigger type:

  • Before Statement Trigger : Trigger fire only once for each statement before the triggering DML statement.
  • Before Row Trigger : Trigger fire for each and every record before the triggering DML statement.
  • After Statement Trigger : Trigger fire only once for each statement after the triggering DML statement executing.
  • After Row Trigger : Trigger fire for each and every record after the triggering DML statement executing.

PL/SQL Triggers Example

You can make your own trigger using trigger syntax referencing. Here are fewer trigger example.

Inserting Trigger
CREATE or REPLACE TRIGGER trg1
BEFORE
INSERT ON emp1
FOR EACH ROW
BEGIN
:new.ename := upper(:new.ename);
END;
Restriction to Deleting Trigger

This trigger is preventing to deleting row.

Delete Trigger Code:

CREATE or REPLACE TRIGGER trg1
AFTER
DELETE ON emp1
FOR EACH ROW
BEGIN
IF :old.eno = 1 THEN
raise_application_error(-20015, 'You can't delete this row');
END IF;
END;

Delete Trigger Result :

SQL>delete from emp1 where eno = 1;
Error Code: 20015
Error Name: You can't delete this row


Related Videos