PL/SQL – Create Trigger

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

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.

One thought on “PL/SQL – Create Trigger

  1. Pingback: PL/SQL- Expected Errors in Executing and Compiling | Srinimf

Have Something to Say? Post Your Comment

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s