Transcript Triggers - KES Shroff College

In Oracle

A PL/SQL block stored in the database and
fired in response to a specified event
◦
◦
◦
◦
◦
DML statements : insert , update, delete
DDL statements : create, alter, drop
Startup or shutdown
An error
A user logon or logoff
Create or replace trigger <trigger_name>
{before|after|Instead of}
{Insert | Update | Delete}
[of <col_name>]
On <table_name>
[referencing old as o new as n]
[for each row]
When (<condition>)
Begin
Sql statements
End;



Triggering SQL statement
Trigger action
Trigger restriction

DML triggers
◦ Defined on a table and fired in response to an
insert, delete or update

DDL triggers
◦ Defined in response to events such as logon or
logoff, create alter drop etc.

Instead of triggers
◦ Used for non updateable views

DML trigger definition consists of:
◦ Event that fires the trigger
◦ Database table on which the event occurs
◦ Optional condition controlling when trigger
executes

Types:
◦ Row level
 Trigger fired or each row updated , inserted or deleted
◦ Statement level
 Trigger fired for each sql statement executed
create or replace trigger before_update_emp
before update on emp
begin
insert into empcheck values('Before update','Statement',
sysdate);
dbms_output.put_line('Some updation done on Emp');
end;
/
Update emp set sal=sal+100;
Some updation done on emp
14 rows updated
Select * from empcheck;
EVENT
LEVEL_CHECK
DATE_CHEC
-------------------- -------------------------------- -----------------------------Before update Statement
13-SEP-13
create or replace trigger before_update_row_emp_sal
before update of sal on emp
For each row
begin
insert into empcheck values('Before update',’Row', sysdate);
dbms_output.put_line('Some updation done on Emp Sal column');
end;
/
SQL>
Some
Some
Some
Some
update emp set sal=sal+10 where deptno=10;
updation done on Emp
updation done on Emp Sal column
updation done on Emp Sal column
updation done on Emp Sal column
3 rows updated.
SQL> select * from empcheck;
EVENT
LEVEL_CHECK
DATE_CHEC
-------------------- ------------------------------ --------Before update
Statement
13-SEP-13
Before update
Row
13-SEP-13
Before update
Row
13-SEP-13
Before update
Row
13-SEP-13
create or replace trigger display_salary_changes
before delete or update or insert on emp
for each row
declare
sal_diff number;
begin
sal_diff:=:new.sal-:old.sal;
dbms_output.put_line('Old salary: '||:old.sal);
dbms_output.put_line('New salary: '||:new.sal);
dbms_output.put_line('Salary Difference: '||sal_diff);
end;
/
create trigger trig_alter_scott
before alter on scott.schema
begin
raise_application_error(-20000,'Cannot alter
any table in scott');
end;
/
alter table emp drop primary key;
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Cannot alter any table in scott



Help to make non updateable views
updateable
Called so because unlike other types of
triggers, oracle fires the trigger instead of
executing the triggering statement
Exist only on views
Create a view
create or replace view v as select empno,ename ,sal from emp
where sal>2000;
Create a trigger(Instead of)
create or replace trigger emp_trig
instead of insert on v
begin
if :new.sal >2000 then
insert into emp (empno,ename,sal)
values(:new.empno,:new.ename,:new.sal);
else
raise_application_error(-20001,'Insertion from this view invalid');
end if;
end;
/
Creation of a non updateable view
Create view vtest as select deptno,sum(sal) as
total from emp group by deptno;
 Updation through the view not possible
update vtest set deptno=20 where deptno=10;
ORA-01732: data manipulation operation not
legal on this view

Creation of Instead of trigger
create or replace trigger emp_trig2
instead of update on vtest
begin
if updating
then
update emp set deptno=:new.deptno;
end if;
end;
/
Updation using trigger
update vtest set deptno=20 where deptno=10;
1 row updated.


Mutating table exceptions occur triggering
table is referenced from within the row level
trigger code
This error is raised when a trigger attempts to
execute a DML statement on the same table
that the trigger is defined on
create or replace trigger trig_up_emp
after update on emp
for each row
declare
i number;
begin
select sum(sal) into i from emp;
dbms_output.put_line('The sum of salary of employees is '||i);
end;
/
ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not
see it
ORA-06512: at "SCOTT.TRIG_UP_EMP", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRIG_UP_EMP'
create or replace trigger trig_up_emp
after update on emp
for each row
declare
i number;
pragma autonomous_transaction;
begin
select sum(sal) into i from emp;
dbms_output.put_line('The sum of salary of employees
is '||i);
commit;
end;
/
View ALL_TRIGGERS, USER_TRIGGERS
Alter trigger tigger_name enable;
Alter trigger tigger_name disable;
Drop trigger trigger_name;
Alter table table_name disable all triggers;
Alter table table_name enable all triggers;



Refers to SQL statements that are constructed
and executed at run time
Execute DDL statements
Manage Ad hoc query and update
requirements of web based applications
create or replace procedure dynsql_eg(ddl_string varchar2)
is
begin
execute immediate ddl_string;
end;