What are triggers?
---- triggers are similar to procedures, in that they are the named pl/sql blocks with declarative, executable and exception-handling sections, how ever a procedure is executed explicitly from another block via a procedure call, which can also pass arguments.
---- A trigger is executed implicitly when ever a particular event task places. And is nothing but a event.
---- The triggering event is a DML (insert, update, delete) operations on a data base table
----- fires whenever a data event(such as DML) or system event(such as login or shutdown) occurs on a schema or database
Trigger timing : 1) before
2) after
3) instead of ( this is used for views)
Triggering events : 1) insert
2)update
3) delete
Trigger type : 1) statement level
2) row level.
Firing sequence of database triggers
1) before statement trigger
2) before row trigger
3) after row trigger
4) after statement trigger
Examples:
1) Create or replace trigger secure_emp
Before
Insert on emp
Begin
If (to_char(sysdate,’dy’) in(‘sat’,’sun’)) or
To_char(sysdate,’hh24:mi’)
Not between ’08:00’ and ’18:00’)
Then raise_application_error(-20500,’u can insert in the office timings’)
End if;
End;
Ex :- 2) write a program to all transitions with name smith?
Create or replace
Trigger trigger_name
Before insert or update or delete
On emp
For each row
When (old.ename =’smith’ or
New.ename =’smith’)
Begin
Raise_application_error(-20003,’smith’);
End;
COMMENTS