Saturday, March 12, 2016

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;

No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...