Oracle Triggers

  • Triggers are also same as stored procedure. But trigger will be invoked whenever DML operations performed on a table.
  • It is database object.
  • Trigger is associated with table/view.
  • Trigger is not called explicitly.
  • It is executed implicitly against an event taken place for the table.
  • Trigger does not accept parameters.
  • It is used to impose business rules on data.

Triggers are two types:
Row Level trigger: Trigger will be fired for every row manipulated.
Statement Level Trigger: Trigger will be fired only once for DML statement.

Syntax:
CREATE OR REPLACE TRIGGER Trigger_Name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON Table_Name
FOR EACH ROW
WHEN Condition;
DECLARE
Varaible_declaration;
BEGIN
statements;
END;

To practice on triggers, create a sample table.
CREATE TAABLE pinnapa(logs date);

Example:Statment Level Trigger

CREATE OR REPLACE trigger tr1
AFTER UPDATE ON emp
BEGIN
INSERT INTO pinnapa values(sysdate);
END
/

Testing:
sql>UPDATE emp set sal=sal+100
WHERE deptno=10;

sql>select*from test;

Dropping a trigger:

DROP TRIGGER tr1;

Example for Row Level Trigger:

CREATE OR REPLACE trigger tr2
AFTER UPDATE ON emp
FOR EACH ROW
BEGIN
INSERT INTO pinnapa values(sysdate);
END
/

INSTEAD of Trigger:
We cant perform DML operations through complex view to base tables. To overcome this problem, Oracle introduced INSTEAD of triggers. By default instead of triggers are row level triggers.

Syntax:
CREATE OR REPLACE TRIGGER Trigger_Name
INSTEAD OF INSERT/UPDATE/DELETE
ON Table_Name
FOR EACH ROW
WHEN Condition;
DECLARE
Varaible_declaration;
BEGIN
statements;
END;

Example:

CREATE OR REPLACE trigger tr3
INSTEAD OF UPDATE on v5
FOR EACH ROW
BEGIN
UPDATE DEPT SET dname=:new.dname;
WHERE dname=:old.dname;
UPDATE DEPT SET loc=:new.loc;
WHERE loc=:old.loc;
END;
/

Powered by k2schools