How to Create Trigger in PLSQL Procedure Best Example

Triggers are database procedures. These will be fired implicitly, when certain thing happens. 

Create Triger Example

CREATE OR REPLACE TRIGGER my_sal_changes
BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab
FOR EACH ROW
WHEN (new.Empno > 0)
DECLARE
    sal_diff number;
BEGIN
    sal_diff  := :new.sal  – :old.sal;
    dbms_output.put(‘Old salary: ‘ || :old.sal);
    dbms_output.put(‘  New salary: ‘ || :new.sal);
    dbms_output.put_line(‘  Difference ‘ || sal_diff);
END;
/

The size of the Trigger should not exceed 32K.

Important Points

Trigger will work on DML operations, INSERT,DELETE,UPDATE

BEFORE/AFTER key words. When trigger has to execute, we need to given in definition

FOR EACH ROW => Trigger validates for each row

INSTEAD OF key word we need to use for views

Types of Triggers

• DML triggers on tables.
• INSTEAD OF triggers on views.
• System triggers on DATABASE or SCHEMA: With DATABASE, triggers fire for each event for all users; with SCHEMA, triggers fire for each event for that specific user.

Related articles

• PL/SQL – Complex Queries (Job Interviews) (srinimf.com)
• PL/SQL – Difficult Questions (srinimf.com)