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;

Please follow and like us: