Oracle PL/SQL triggers:
A database trigger is a stored program that is automatically fired or executed when some events occur. A trigger can execute in response to any of the following events:
1. A database manipulation (DML) statement like DELETE, INSERT, or UPDATE.
2. Database definition (DDL) statements like CREATE, ALTER, or DROP.
3. A database operation like SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN.
Note: A trigger can be defined on the table, view, schema, or database with which the event is associated.
Types of PL/SQL triggers:
1. Row level trigger – An event is triggered at row level i.e. for each row updated, inserted, or deleted.
2. Statement level trigger – An event is triggered at table level i.e. for each SQL statement executed.
Syntax for creating a trigger:
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;
/
Where:
CREATE [OR REPLACE ] TRIGGER trigger_name – It creates a trigger with the given name or overwrites an existing trigger with the same name.
{BEFORE | AFTER | INSTEAD OF } – It specifies the trigger get fired. i.e. before or after updating a table. INSTEAD OF is used to create a trigger on a view.
{INSERT [OR] | UPDATE [OR] | DELETE} – It specifies the triggering event. The trigger gets fired at all the specified triggering events.
[OF col_name] – It is used with update triggers. It is used when we want to trigger an event only when a specific column is updated.
[ON table_name] – It specifies the name of the table or view to which the trigger is associated.
[REFERENCING OLD AS or NEW AS n] – It 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 old values cannot be referenced when inserting a record and new values cannot be referenced when deleting a record, because they do not exist.
[FOR EACH ROW] – It is used to specify whether a trigger must fire when each row is affected (Row Level Trigger) or just once when the SQL statement is executed (Table level Trigger).
WHEN (condition) – It is valid only for row-level triggers. The trigger is fired only for rows that satisfy the condition specified.
Example:
Existing data:
Select * from employees;
EMP_ID NAME AGE ADDRESS SALARY 1 Shveta 23 Delhi 50000 2 Bharti 22 Karnal 52000 3 Deepika 24 UP 54000 4 Richi 25 US 56000 5 Bharat 21 Paris 58000 6 Sahdev 26 Delhi 60000
Trigger:
CREATE OR REPLACE TRIGGER show_salary_difference
BEFORE DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
WHEN (NEW.EMP_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;
/
Note: The above trigger will execute for every INSERT, UPDATE, or DELETE operation performed on the EMPLOYEES table.
Drop a trigger:
DROP TRIGGER trigger_name;
