Triggers

Triggers:

A trigger is a collection of sql and  plsql blocks of code which are precompiled and stored in the data base and is executed implicitly(cant be executed explicitly).

Row level trigger: it is fired for each and every row that is effected by dml operations

Statement level trigger: it is fired only one time for all the rows effected by dml operation.

Trigger timing : is mentioned by 2 keywords – before and after…

Before timing triggers ar e fired before the dml operation are performed.

After timing triggers are fired after the dml operation are performed.

Syntax:

Create or replace trigger trigger_name

 After/before insert/update/delete on table_name

 For each row(optional)

Is

 Begin

--any action to be taken place.

Exception

End;

How many data base triggers exists?

 Row level – after/before insert/update/delete –total 6

 Statement level – after/before insert/update/delete –total 6

Total is 12 triggers combinations can be fired.

:old and :new keywords

As their name indicates these words referrer to the values before or after dml operations….

Ex:

create table emp_test(empno number,ename varchar2(100),sal number,old_sal number);

create table emp_backup(empno number,ename varchar2(100),sal number);

create table xx_errors(error_dec varchar2(100));

write a trigger when ever there is an insert into emp table then insert           that new record in to emp_test table…

create or replace trigger xx_emp_insert

after insert on emp

for each row

begin

  --insert into xx_errors values('before insert');

  insert into emp_test values(:new.empno,:new.ename,:new.sal,:old.sal);

  --insert into xx_errors values('after insert');

exception

when others then

insert into xx_errors values('others exception rasied');

end xx_emp_insert;

create or replace trigger xx_emp_update

after update on emp

for each row

begin

  --insert into xx_errors values('before insert');

  update emp_test

  set old_sal = :old.sal,sal = :new.sal

  where empno = :new.empno;

  --insert into xx_errors values('after insert');

exception

when others then

insert into xx_errors values('others exception rasied');

end xx_emp_update;

create or replace trigger xx_emp_delete

after delete on emp

for each row

begin

  --insert into xx_errors values('before insert');

  insert into emp_backup values(:old.empno,:old.ename,:old.sal);

 

  delete from emp_test where empno = :old.empno;

  --insert into xx_errors values('after insert');

exception

when others then

insert into xx_errors values('others exception rasied');

end xx_emp_delete;

create or replace trigger xx_schema

after create on schema

begin

  insert into xx_objects values('object created');

exception

when others then

insert into xx_errors values('others exception rasied');

end xx_schema;

create or replace trigger xx_drop_schema

 after drop on schema

 begin

   insert into xx_objects values('object dropped');

 exception

 when others then

 insert into xx_errors values('others exception rasied');

 end xx_drop_schema;