TRIGGER: Triggers are procedures that are sotored in the database not in the complied form and implicitly fired when an event occurs.Traditionally,triggers guarantee that when a specific operation is performed related actions are performd.Triggers support the execution of a Pl/SQL block when an INSERT
Before/after insert or delete or update on for each row.
Begin
:
:
End;
Example 1: Create a database Trigger corresponding to emp table which is not going to allow the user to insert the employee’s salary more than 5000.
SQL>ed t1
Create or replace trigger eins
Before insert on emp for each row
Begin
If:new.sal>5000 then
Raise_application_erroe(-20001,’salary above 5000’);
End if;
End;
/
SQL>@t1
Example 2: Create a database Trigger on dept table which prevents the user from updating the deptno or deleting a row from the dept table if dependent rows are existing in the emp table( i.e. if there are any employee’s belonging to that department).
SQL>ed t2
Create or replace trigger updel
Before update or delete on dept for each row
Begin
Declare
No number(4);
If updating or deleting then
Select count(*) no from emp
Where deptno=:new.deptno;
If (no>1) then
Raise_application_erroe(-20001,’Records existing in emp table’);
End if;
End if;
End;
/
SQL>@ t2
