PL SQL

Hi Everyone, Today's topic is Triggers.

In Oracle, you can define procedures that are implicitly executed when an INSERT, UPDATE or DELETE statement is issued against the associated table. These procedures are called database triggers.

A trigger is a named PL/SQL block stored in the Oracle Database and executed automatically when a triggering event takes place.

Uses Of Triggers:

1. Enforcing complex business rules that cannot be established using integrity constraint such as UNIQUE, NOT NULL, and CHECK.

2. Preventing invalid transactions.

3. Auditing sensitive data.

4. Generating value automatically for derived columns.

Creating a Trigger :

Syntax:

CREATE [ORREPLACE] TRIGGER trigger_name
 {BEFORE | AFTER } triggering_event 
ON table_name [FOREACHROW] [FOLLOWS | PRECEDES another_trigger]
[ENABLE / DISABLE ] 
[WHEN condition] 
DECLARE     
     declaration statements 
BEGIN     
    executable statements
 EXCEPTION     
     exception_handling statements
 END;

BEFORE TRIGGER:

This statement specifies that Oracle will fire this trigger BEFORE the INSERT/UPDATE or DELETE operation is executed.

AFTER TRIGGER:

This statement specifies that Oracle will fire this trigger AFTER the INSERT/UPDATE or DELETE operation is executed.

Example:

CREATE OR REPLACE TRIGGER emp_t 
BEFORE DELETE OR INSERT OR UPDATE ON employee 
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;
   / 

Insert statement:

insert into employee(id,name,salary) values (1,'Rajesh',20000);

Output:

1 row(s) inserted.
Old salary: 
New salary: 20000
Salary difference: 

Update statement:

UPDATE employee 
SET salary = salary + 500 
WHERE id = 1; 

Output:

1 row(s) updated.
Old salary: 20000
New salary: 20500
Salary difference: 500

DROP TRRIGGER:

In Oracle, DROP TRIGGER statement is used to drop the trigger if you find that you need to remove it from the database.

Syntax:

DROP TRIGGER trigger_name

Example:

DROP TRIGGER emp_t

DISABLE TRIGGER:

The ALTER TRIGGER statement is used to disable a trigger.

Syntax:

ALTER TRIGGER trigger_name DISABLE;

Example:

ALTER TRIGGER emp_t DISABLE; 

ENABLE TRIGGER:

The ALTER TRIGGER statement is used to enable a trigger.

Syntax:

ALTER TRIGGER trigger_name ENABLE;   

Example:

ALTER TRIGGER emp_t ENABLE;   

ENABLE ALL TRIGGERS in a table:

The ALTER TRIGGER statement is used to enable a trigger.

Syntax

ALTER TABLE table_name ENABLE ALL TRIGGERS;  

Example:

ALTER TABLE employee ENABLE ALL TRIGGERS;

DISABLE ALL TRIGGERS in a table:

The ALTER TRIGGER statement is used to enable a trigger.

Syntax:

ALTER TABLE table_name DISABLE ALL TRIGGERS;

Example:

ALTER TABLE emp_t DISABLE ALL TRIGGERS;  

Thank you for reading.

Ask your Doubt or Query in Comment Sections.