PLSQL

PL/SQL Triggers

PL/SQL Triggers

Triggers are known to be the stored programs, that are executed automatically, or even fired when some events occur. In response to any of the following events, triggers are said to be executed. Triggers are said to be defined on the table, view, schema or database where the events are associated.

  1. A database manipulation (DML) statement (DELETE, INSERT OR UPDATE)
  2. A database definition (DDL) statement (CREATE, ALTER, OR DROP)
  3. A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)
Creating 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;

Where,

  1. CREATE [OR REPLACE] TRIGGER trigger_name – this creates or replaces an existing trigger with the given trigger_name.
  2. {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger is executed. The INSTEAD OF clause can be used for creating trigger on a view.
  3. {INSERT [OR] | UPDATE [OR] | DELETE} – it specifies the DML operation.
  4. [OF col_name] – it specifies the column name that should be updated.
  5. [ON table_name] − it specifies the name of the table associated with that of the trigger.
  6. [REFERENCING OLD AS o NEW AS n] − This allows us for referring new and old values for various DML statements, like INSERT, UPDATE, and DELETE.
  7. [FOR EACH ROW] – It specifies a row-level trigger.
Example:
Select * from customers;
Refer to the table we created in the functions section:
CREATE OR REPLACE TRIGGER display_salary_changes 
BEFORE DELETE OR INSERT OR UPDATE ON teachers 
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:
Trigger created.
Triggering the Trigger:
INSERT INTO TEACHERS (ID, NAME, AGE, ADDRESS, SALARY) 
VALUES (1, 'Ranjitha', 34, 'bangalore', 3500.00);
display salary_changes;
Old salary: 
New salary: 3500 
Salary difference: