Trigger:
A trigger is a block of statement which is fired when any SQL statements (INSERT,UPDATE,DELETE) done against on the table.
or
Trigger is a procedure which is fired when INSERT,UPDATE,DELETE done against on the table.
Trigger consists of 3 parts
1) Trigger event
2) Trigger restriction
3) Trigger action.
Trigger event:
It is an SQL statement , which causers trigger to fired. It can be INSERT,UPDATE,DELETE.
Trigger restriction:
It specifies Boolean value, that must be true trigger to fired. It specified using WHEN clause.
Trigger action:
It is a PL/SQL code to be executed, when a trigger statement is encounted and any trigger action evaluate true.
Syntax:
The general structure of triggers is:
CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE (or AFTER)
INSERT OR UPDATE [OF COLUMNS] OR DELETE
ON tablename
[FOR EACH ROW [WHEN (condition)]]
BEGIN
...
END;
Types of PL/SQL Triggers
1) Row level trigger :
An event is triggered for each row upated, inserted or deleted.
2) Statement level trigger:
An event is triggered for each sql statement executed.
PL/SQL Trigger Execution Hierarchy
1) BEFORE statement trigger fires first.
2) Next BEFORE row level trigger fires, once for each row affected.
3) Then AFTER row level trigger fires once for each affected row. This events will alternates between BEFORE and AFTER row level triggers.
4) Finally the AFTER statement level trigger fires.
CREATE TABLE product
(Message varchar2(50),
Current_Date number(32));
CREATE or REPLACE TRIGGER Before_Update_Stat_product
BEFORE
UPDATE ON product
Begin
INSERT INTO product_check
Values('Before update, statement level',sysdate);
END;
/
CREATE or REPLACE TRIGGER Before_Upddate_Row_product
BEFORE
UPDATE ON product
FOR EACH ROW
BEGIN
INSERT INTO product_check
Values('Before update row level',sysdate);
END;
/
CREATE or REPLACE TRIGGER After_Update_Stat_product
AFTER
UPDATE ON product
BEGIN
INSERT INTO product_check
Values('After update, statement level', sysdate);
End;
/
CREATE or REPLACE TRIGGER After_Update_Row_product
AFTER
insert On product
FOR EACH ROW
BEGIN
INSERT INTO product_check
Values('After update, Row level',sysdate);
END;
/
UPDATE PRODUCT SET unit_price = 800
WHERE product_id in (100,101);
SELECT * FROM product_check;
Before update, row level 26-Nov-2008
After update, Row level 26-Nov-2008
Before update, row level 26-Nov-2008
After update, Row level 26-Nov-2008
After update, statement level 26-Nov-2008
There are two types of triggers based on the which level it is triggered.
The following hierarchy is followed when a trigger is fired.
For Example: Let's create a table 'product_check' which we can use to store messages when triggers are fired.
Let's create a BEFORE and AFTER statement and row level triggers for the product table.
1) BEFORE UPDATE, Statement Level:
This trigger will insert a record into the table 'product_check' before a sql update statement is executed, at the statement level.
2) BEFORE UPDATE, Row Level:
This trigger will insert a record into the table 'product_check' before each row is updated.
3) AFTER UPDATE, Statement Level:
This trigger will insert a record into the table 'product_check' after a sql update statement is executed, at the statement level.
4) AFTER UPDATE, Row Level:
This trigger will insert a record into the table 'product_check' after each row is updated.
Now lets execute a update statement on table product.
Lets check the data in 'product_check' table to see the order in which the trigger is fired.
Output:
Mesage Current_Date
----------------------------------------------------------------------------
Before update, statement level 26-Nov-2008
The above result shows 'before update' and 'after update' row level events have occured twice, since two records were updated. But 'before update' and 'after update' statement level events are fired only once per sql statement.
The above rules apply similarly for INSERT and DELETE statements.
**********************
Before insert
**********************
create or replace trigger before_emp
before insert
on emp
for each row
begin
if :new.sal>800 then
insert into emp1 values(:new.empno,
:new.ename,
:new.job,
:new.mgr,
:new.hiredate,
:new.sal,
:new.comm,
:new.deptno);
else
raise_application_error(-20199,'salary not grater than 800');
end if;
end;
/
trigger created.
*******************************
After update trigger
*******************************
create table a(sno number(4),name varchar(20));
table created;
create table b(
before_sno number(4),
after_sno number(4),
before_name number(4),
after_name number(4),
user_name varchar(20),
date_of_changing);
table created;
create or replace trigger after_update_a
after update
on a
for each row
declare
v_username varchar(20);
v_date date;
begin
select user into v_username from dual;
select sysdate into v_date from dual;
insert into b values(:new.sno,
:new.name,
:old.sno,
:old.name,
v_username,v_date);
end;
trigger created;
SQL> insert into a values(&sno,'&NAME');
Enter value for sno: 1000
Enter value for name: Chinnu
old 1: insert into a values(&sno,'&NAME')
new 1: insert into a values(1000,'CHINNU')
1 row created.
SQL> SELECT * FROM A;
SNO NAME
---------- --------------------
1000 CHINNU
SQL> insert into a values(&sno,'&NAME');
Enter value for sno: 1001
Enter value for name: don
old 1: insert into a values(&sno,'&NAME')
new 1: insert into a values(1001,'don')
1 row created.
SQL> /
Enter value for sno: 1002
Enter value for name: sunny
old 1: insert into a values(&sno,'&NAME')
new 1: insert into a values(1002,'sunny')
1 row created.
SQL> /
Enter value for sno: 1003
Enter value for name: kumara
old 1: insert into a values(&sno,'&NAME')
new 1: insert into a values(1003,'kumar')
1 row created.
SQL> commit;
Commit complete.
SQL> select * from a;
SNO NAME
---------- --------------------
1000 CHINNU
1001 don
1002 sunny
1003 kumara
SQL> update a set name='kumar' where sno=1003;
1 row updated.
SQL> select * from b;
BEFORE_SNO AFTER_SNO BEFORE_NAME AFTER_NAME USER_NAME DATE_OF_C
---------- ---------- -------------------- -------------------- -------------------- ---------
1003 1003 kumar kumara CHINNU 14-APR-12
SQL> update a set name='kumar_jabu' where sno=1003;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from b;
BEFORE_SNO AFTER_SNO BEFORE_NAME AFTER_NAME USER_NAME DATE_OF_C
---------- ---------- -------------------- -------------------- -------------------- ---------
1003 1003 kumar kumara CHINNU 14-APR-12
1003 1003 kumar_jabu kumar CHINNU 14-APR-12
************************
Deleted trigger
************************
create or replace trigger trigger_del
after delete
on emp1
for each row
declare
usr varchar(10);
begin
select user into usr from dual;
insert into emp_delete values(:old.empno,
:old.ENAME,
:old.JOB,
:old.MGR,
:old.HIREDATE,
:old.SAL,
:old.COMM,
:old.DEPTNO,
usr,
sysdate);
end;
/
trigger created.
SQL> select * from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7935 chinnu software 7983 12-APR-12 8000 800 30
15 rows selected.
SQL> select * from emp_delete;
no rows selected
SQL> delete from emp1 where sal=8000;
1 row deleted.
SQL> select * from emp_delete;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
USER_NAME DELETED_D
-------------------- ---------
7935 chinnu software 7983 12-APR-12 8000 800 30
CHINNU 14-APR-12
******************************************
For finding triggers on a particular table
******************************************
select trigger_name,trigger_type,status from user_triggers where table_name='EMP';
********************************************************************************
For knowing how many triggers,function,procedure,index,sequence etc.. are created
********************************************************************************
SQL> select object_name from user_objects where object_type='TRIGGER';
OBJECT_NAME
-----------------------------------------------------------------------
T1
TRIGGER_DEL
INST_T
BEFORE_T
BEFORE_T1
BEFORE_EMP
6 rows selected.
********************************
For finding trigger
********************************
select text from user_source where name='TRIGGER_SECURE';
**************************************************
To find how many triggers/procedures/cursors is ther in the database
**************************************************
SQL> select count(*) from user_source where type='TRIGGER';
COUNT(*)
----------
79
SQL> select count(*) from user_source where type='PROCEDURE';
COUNT(*)
----------
15
*****************************
To find Contraints on a table
*****************************
select constraint_name,table_name from user_constraints where constraint_name='constaintname'
No comments:
Post a Comment