Trigger in Pl-sql/oracle/Oracle 10g forms and reports
1. it is a db object which is a collection of pl/sql codes and it automatically get executed whenever any dml event happens provided trigger has been written for that DML event.
2. Trigger has three section i.e. events, restrictions and when condition (optional.
3. Pl sql trigger is divided into two parts. 1. Row level 2. Statement level.
Row Level Statement Trigger.
1. Row level trigger is executed for each and every row affected by DML stmt.
2. we need to use for each row for row level trigger.
3. used in those cases where we need to depend on the values of every record of any column of any table.
4. eg. Any name begin inserted must be in letter capital.
Statement Level Trigger.
1. Statement level trigger is executed only once irrespective of No. of records affected by the dml statement.
2. Every trigger is by default STMT level trigger.
3. Used for restrictions like No Deletion allowed on Sunday on emp table.
Syntax.
CREATE [ OR REPLACE]
TRIGGER
BEFORE | AFTER
INSERT [ OR DELETE]
OF (OPTIONAL)
ON FOR EACH ROW (OPTIONAL)
REFRENCING NEW AS
OLD AS
WHEN () (OPTIONAL)
Note.
1. to follow any value of any col. Of any record of any table within the execution
section of a trigger always used :old. and :new.
2. Theses two can be followed only with in a row level trigger.
:new.
Refers to new values or that col for that row (Applicable for updated and insert)
:old.
Refers to old values for that col for that record (Applicable for both delete and update)
Example:-
Create or replace
Trigger tr_ex
Before delete
On insert or update
On emp
For each row
Declare
V_msg varchar2(200)
Begin
If inserting then
V_msg := ‘inserting….’
Elsif deleting then
V_msg := ‘deleting………….’;
Elsif updating then
V_msg := ‘deleting…’;
End if;
Dbms_output.put_line(V_msg);
End;
Q. Create a trigger which will automatic make the first letter of
every name inserted | updated into capital letter irrespective of any case enter by user.
Create or replace trigger
Tr_name
Before update or insert
Of ename
On emp
For each row
Begin
Dbms_output.put_line(‘before change name = ‘ ||.new.ename);
End;
/
Example of Statement level trigger.
CREATE OR REPLACE TRIGGER TR_DELETE
BEFORE DELTE
ON EMP
BEGIN
IF TRIM(TO_CHAR(SYSDATE,’DAY’)) = ‘SUNDAY’ THEN RAISE _APPLICATION_ERROR
( -20001,’NO DELTETION ALLOW ON SUNDAY);
END IF;
END;
INSTEAD OF TRIGGER.
This is used to perform DML operation on base table through join view.
Create or replace trigger tr_instead
Instead of
Insert or update or delete
On v_join
Referencing new as N old as O
For each row
Declare
V_count number;
Begin
If inserting then
Select count(*) into v_count from dept
Where deptno = :n.deptno;
If v_count = 0 then
Insert into dept values(:n.deptno,:n.dname,:n.loc);
End if;
Insert into emp
(empno,ename,sal) values (:n.empno,:n.ename,:n.sal);
Elsif updating then
Update emp
Set sal = :n.sal where empno = :o.empno;
Update dept
Set dname = :n.dname where deptno = :o.deptno;
Elsif deleting then
Delete from emp where empno = :o.empno;
Delete from dept where deptno = :o.deptno;
End if;
End;
/
No comments:
Post a Comment