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)
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);
The size of the Trigger should not exceed 32K.
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.
OFtriggers on views.
- System triggers on
DATABASE, triggers fire for each event for all users; with
SCHEMA, triggers fire for each event for that specific user.