CodesJava

Easy learning with example program codes

Oracle pl sql triggers tutorial


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;

 



Industrial Training

We offers Placement Oriented Training on Java, Spring, JSF, Hibernate, PHP, AngularJS, Angular 4, PLSQL, Oracle BI Publisher etc. We also provides Online training, please mail us at hr@codesjava.com.

Development

We also provides the Development services for Website Development , Java Development, PHP Development, Android App Development etc. You can contact us on hr@codesjava.com.

Copyright © 2019 CodesJava DMCA.com Protection Status SiteMap Reference: Java Wiki