# Triggers plsql

## Oracle pl sql triggers:

A database trigger is a stored program which 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. A database definition (DDL) statement 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 event.
[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 o 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 being 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 operations performed on the EMPLOYEES table.

## Drop a trigger:

DROP TRIGGER trigger_name;