PL/SQL programs using Triggers
AIM
To study and implement the concepts of triggers.
DEFINITION
A trigger is a statement that is executed automatically by the system as a side effect of a
modification to the database. The parts of a trigger are,
Trigger statement: Specifies the DML statements and fires the trigger body. It also specifies
the table to which the trigger is associated.
Trigger body or trigger action: It is a PL/SQL block that is executed when the triggering
statement is used.
Trigger restriction: Restrictions on the trigger can be achieved
The different uses of triggers are as follows,
To generate data automatically
To enforce complex integrity constraints
To customize complex securing authorizations
To maintain the replicate table
To audit data modifications
TYPES OF TRIGGERS
The various types of triggers are as follows,
Before: It fires the trigger before executing the trigger statement.
After: It fires the trigger after executing the trigger statement.
For each row: It specifies that the trigger fires once per row.
For each statement: This is the default trigger that is invoked. It specifies that the trigger fires
once per statement.
VARIABLES USED IN TRIGGERS
:new
:old
These two variables retain the new and old values of the column updated in the database. The
values in these variables can be used in the database triggers for data manipulation
TRIGGERS - 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)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing
trigger with the trigger_name.
{BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed.
The INSTEAD OF clause is used for creating trigger on a view.
{INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.
[OF col_name] − This specifies the column name that will be updated.
[ON table_name] − This specifies the name of the table associated with the trigger.
[REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values
for various DML statements, such as INSERT, UPDATE, and DELETE.
[FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed
for each row being affected. Otherwise the trigger will execute just once when the SQL
statement is executed, which is called a table level trigger.
WHEN (condition) − This provides a condition for rows for which the trigger would fire.
This clause is valid only for row-level triggers.
Example (consider a table employee)
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON employee
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
Output:
(OLD and NEW references are not available for table-level triggers, rather you can use them for
record-level triggers.)
When a record is updated in the employee table, the above create trigger,
display_salary_changes will be fired and it will display the following result −
Old salary: 1500
New salary: 2000
Salary difference: 500
Result:
Thus the PL/SQL program on Triggers was executed successfully
