TRIGGERS:
*********
-->Trigger is also a stored procedure and also it will automatically invoked whenever DML operations pergformed by
against table or vew.
-->They are stored in "User_Triggers" system table.
-->They r used to impose Business rules / user defined restrictions on Table columns.
-->They provide high security on tables.
-->Triggers affects only those rows,which are added after its enabled.
-->Constraints affects all the rows, that have been existed before and the ones that were newly added the constrants are enabled.
-->Triggers are created to:
-->control DML
-->Security
-->For Auduiting
-->Implement business rules
-->There are two types of triggers suuported by oracle.
1)Statement Level Trigger
2)Row Level Trigger
-->In statement level trigger,Trigger body is executed only once for DML statements.
-->In Row Level Triggers,Trigger body is executed for each and every DML statements.
Syntax:
*******
Syntax:
Create or replace Trigger < Trigger name >
before/after insert or update or delete -- 1
[ of <columns> ] on < Table name >
[ for each row -- 2
when (< condition >) -- 3 * if condition is True
trigger is executed
declare
< variable declaration >; ]
begin
< exec stmts >;
[ exception
<exec stmts >; ]
end;
Trigger Parts:
**************
1. Trigger Event :
******************
-->Indicates when to activate the Trigger
Before STMT/ROW --Insert, Update, Delete = 6 Events
BEFORE STATEMENT : INSERT UPDATE DELETE
AFTER STATEMENT : INSERT UPDATE DELETE
After STMT/ROW --Insert, Update, Delete = 6 Events
BEFORE ROW: INSERT UPDATE DELETE
AFTER ROW: INSERT UPDATE DELETE
2.Trigger Type :
****************
-->Indicate the type of Trigger
a. Row Trigger : Trigger is activated for Every Row manipulated by DML statement.
b. Statement Trigger: Trigger is activated only once for 1 DML statement
3.Trigger Restriction:
**********************
-->it Supports to stop the Trigger Execution based on condition
4.Trigger Body:
***************
-->A set of PL/SQL statements.
1)Statement Level Trigger:
***************************
-->In statement level trigger.trigger body is executed only once for each dml statement.Thats why generally statement level triggers
user defined type based condition and also used to implement auditing reports.These triggers does not contain new and old qulaifiers.
EX:
***
create or replace trigger tr1
before update on emp1
begin
dbms_output.put_line('records are updated');
end;
/
select *
from emp1
where sal<2000;
/
update emp1 set sal=sal*0.1
where sal<2000;
/
update emp1 set sal=sal+1000 where ename='KING';
/
TRGGER FIRING SEQUENCE:
**********************
/
BEFORE STATEMENT LEVEL
BEFORE ROW LEVEL
AFTER ROW LEVEL
AFTER STATEMENT LEVEL
/
/
DROP TABLE STUDENT;
/
CREATE TABLE STUDENT
(
SNO NUMBER,
SNAME VARCHAR2(20),
DOJ DATE,
RESULT VARCHAR2(20)
);
/
DROP SEQUENCE LOG_SEQ;
/
CREATE SEQUENCE LOG_SEQ;
/
CREATE OR REPLACE TRIGGER ST_BI
BEFORE INSERT ON STUDENT
BEGIN
DBMS_OUTPUT.PUT_LINE('STATEMENT BEFORE INSERT :'||LOG_SEQ.NEXTVAL);
END;
/
CREATE OR REPLACE TRIGGER ST_AI
AFTER INSERT ON STUDENT
BEGIN
DBMS_OUTPUT.PUT_LINE('STATEMENT AFTER INSERT :'||LOG_SEQ.NEXTVAL);
END;
/
CREATE OR REPLACE TRIGGER ROW_BI
BEFORE INSERT ON STUDENT
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('ROW BEFORE INSERT :'||LOG_SEQ.NEXTVAL);
END;
/
CREATE OR REPLACE TRIGGER ROW_AI
AFTER INSERT ON STUDENT
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('ROW AFTER INSERT :'||LOG_SEQ.NEXTVAL);
END;
/
BEGIN
INSERT INTO STUDENT(SNO,SNAME,DOJ,RESULT) VALUES (1,'A',TO_dATE('07-JAN-2022'),'PASS');
END;
/
SELECT *
FROM STUDENT;
/
-->Write a PL/SL statement level trigger on emp table not to perform DML operations in saturday and sunday.
CREATE OR REPLACE TRIGGER TR2
BEFORE INSERT OR UPDATE OR DELETE
ON EMP1
BEGIN
IF TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN') THEN
RAISE_APPLICATION_ERROR(-20313,'WE CAN NOT PERFORM DML OPERATIONS ON SAT AND SUN');
END IF;
END;
/
EX:
****
INSERT INTO EMP1(EMPNO,ENAME) VALUES (100,'MOHAN');
/
UPDATE EMP1 SET SAL=SAL+6000;
/
DELETE FROM EMP1;
/
-->Write a pl/sql program statement level trigger on emp table not to perform dml operations on last day of the month.
CREATE OR REPLACE TRIGGER TR2
BEFORE INSERT OR UPDATE OR DELETE
ON EMP1
BEGIN
IF sysdate=last_day(sysdate) THEN
RAISE_APPLICATION_ERROR(-20210,'WE CAN NOT PERFORM DML OPERATIONS ON LASTDAY');
END IF;
END;
/
DROP TABLE XX_STUDENT;
/
CREATE TABLE XX_STUDENT
(
SNO NUMBER PRIMARY KEY,
SNAME VARCHAR2(50) NOT NULL,
M1 NUMBER CHECK (M1 BETWEEN 0 AND 100),
M2 NUMBER CHECK (M2 BETWEEN 0 AND 100),
M3 NUMBER CHECK (M3 BETWEEN 0 AND 100),
TOT NUMBER CHECK (TOT BETWEEN 0 AND 300),
AVG NUMBER,CHECK (AVG BETWEEN 0 AND 100),
RESULT VARCHAR2(4) CHECK (RESULT IN ('PASS','FAIL'))
);
/
CREATE OR REPLACE TRIGGER TR1
AFTER INSERT ON XX_STUDENT
--DECLARE
BEGIN
UPDATE XX_STUDENT
SET TOT=M1+M2+M3,
AVG=(M1+M2+M3)/3,
RESULT=CASE WHEN M1>=35 AND M2>=35 AND M3>=35 THEN 'PASS' ELSE 'FAIL' END;
DBMS_OUTPUT.PUT_LINE('RECORDS ARE UPDATED');
END;
/
SELECT * FROM XX_STUDENT;
/
INSERT INTO XX_STUDENT(SNO,SNAME,M1,M2,M3) VALUES (1,'A',90,80,90);
/
SELECT *
FROM XX_STUDENT;
/
INSERT INTO XX_STUDENT(SNO,SNAME,M1,M2,M3) VALUES (2,'B',90,80,100);
/
SELECT *
FROM XX_STUDENT;
/
ALTER TRIGGER TR1 disable;
/
INSERT INTO XX_STUDENT(SNO,SNAME,M1,M2,M3) VALUES (3,'C',90,60,80);
/
SELECT *
FROM XX_STUDENT;
/
ALTER TRIGGER TR1 ENABLE;
/
SELECT *
FROM XX_STUDENT;
/
INSERT INTO XX_STUDENT(SNO,SNAME,M1,M2,M3) VALUES (4,'D',90,85,95);
/
SELECT *
FROM XX_STUDENT;
/
CREATE TABLE XX_USER_AUDIT
(
LV_MSG VARCHAR2(20),
USER_NAME VARCHAR2(100),
UPDATED_DATE DATE
);
/
--Trigger to Track the users who did dml operations done on a particiluar table and date as well;
CREATE OR REPLACE TRIGGER USER_AUDIT
AFTER INSERT OR UPDATE OR DELETE ON EMP1
DECLARE
MSG VARCHAR2(100);
BEGIN
IF INSERTING THEN
MSG :='INSERTION IS DONE';
ELSIF UPDATING THEN
MSG :='UPDATION IS DONE';
ELSIF DELETING THEN
MSG :='DELETION IS DONE';
END IF;
INSERT INTO XX_USER_AUDIT VALUES (MSG,USER,SYSDATE);
END;
/
DROP TABLE XX_USER_AUDIT;
/
INSERT INTO EMP1(EMPNO,ENAME,SAL) VALUES(100,'A',2000);
/
UPDATE EMP1 SET SAL=3000 WHERE DEPTNO=20;
/
DELETE FROM EMP1 WHERE DEPTNO=10;
/
SELECT * FROM XX_USER_AUDIT;
Row Level Triggers:
*******************
-->In Row Level Triggers,Trigger body is executed for each row of DML statement.
That's why we are using for each row clause in trigger specification and also data internally stored in segment qualifiers like NEW and OLD.
-->These qualifiers are used in either trigger specification or in trigger body.When we are using these modifiers in trigger body
we must use colon prefix in the qualifiers.
syntax:
*******
:old.column_name or :new.column_name
-->wWhen we are using these qualifers in when clause we are not allow to use colon infront of the qualifiers.
Qulalifier: INSERT: UPDATE: DELETE:
*********** ******* ******* *******
:NEW YES YES NO
:OLD NO YES YES
-->In before triggers,Trigger body is executed before DML statements are effected into database.
-->In after triggers,Trigger body is executed after DML statements effected into database.
-->Generally if we want to restrict invalid data entry always we are using before triggers.
EX:
****
create or replace trigger tr1
after delete on emp1 for each row
begin
dbms_output.put_line('records are deleted');
end;
/
select *
from emp1
where sal<2000;
/
delete from emp1 where sal<2000;
/
delete from emp1 where sal=6000;
EX1:
****
-->Trigger checks for null value in dept name.
create or replace trigger chk_null
before insert or update of dname on dept
for each row
begin
if :new.dname is null then
raise_application_error(-20301,
' Department name cannot be blank ');
end if;
end;
/
insert into dept values(98,null,'Hyd');
/
update dept set dname = null where deptno = 30;
/
--TRIGGER VALIDATES THE REVISED SALARY WHETHER THE CURRENT SALARY IS LESS THAN THE PREVIOUS SALARY AMOUNT
CREATE OR REPLACE TRIGGER REV_SAL
BEFORE UPDATE OF SAL ON EMP1
FOR EACH ROW
WHEN(NEW.SAL<OLD.SAL)
BEGIN
RAISE_APPLICATION_ERROR(-20250,'REVISED UPDATED SALARY AMOUNT IS LESSTHAN THE CURRENT SALARY');
END;
/
UPDATE EMP1
SET SAL=2000
WHERE EMPNO=7902; -- trigger fires the error
/
UPDATE EMP1
SET SAL=5000
WHERE EMPNO=7902; --updated the salary in emp1 table
/
--TRIGGER TO VALIDATE EMAIL ADDRESS TO CHECK THE PRESENCE OF EMPTY SPACE AND SYMBOL @
/
create table XX_EMAIL
(
SNO NUMBER,
SNAME VARCHAR2(20),
EMAIL VARCHAR2(20)
);
/
DROP TRIGGER VALID_EMAIL;
/
CREATE OR REPLACE TRIGGER VALID_100
BEFORE INSERT OR UPDATE OF EMAIL ON XX_EMAIL
FOR EACH ROW
BEGIN
IF ((INSTR(:NEW.EMAIL,'@',1) =0) OR INSTR(:NEW.EMAIL,' ',1)>0) THEN
RAISE_APPLICATION_ERROR(-20800,'INVALID EMAIL ADDRESS, PLEASE CHECK');
END IF;
END;
/
INSERT INTO XX_EMAIL(SNO,SNAME,EMAIL) VALUES(1,'A','MOHAN@ GMAIL.COM');
/
INSERT INTO XX_EMAIL(SNO,SNAME,EMAIL) VALUES (2,'B','MOHAN.GMAIL.COM');
/
UPDATE XX_EMAIL
SET EMAIL='VASU.GMAIL.COM'
WHERE SNO=1;
EX:
***
/
DROP TABLE EMP1;
/
DROP TABLE EMP2;
/
CREATE TABLE EMP1 AS SELECT * FROM EMP;
/
CREATE TABLE EMP2 AS SELECT * FROM EMP WHERE 1=2;
/
CREATE OR REPLACE TRIGGER SAL_TRIGGER
BEFORE UPDATE OF SAL ON EMP1
FOR EACH ROW
BEGIN
INSERT INTO EMP2 VALUES(:OLD.EMPNO,:OLD.ENAME,:OLD.JOB,:OLD.MGR,:OLD.HIREDATE,:OLD.SAL,:OLD.COMM,:OLD.DEPTNO);
END;
/
UPDATE EMP1
SET SAL=20000
WHERE EMPNO=7369;
/
SELECT * FROM EMP1;
/
SELECT * FROM EMP2;
/
EX:
***
/
--tRIGGER DISPLAYS THE OLD VALUES AND NEW VALUES
CREATE OR REPLACE TRIGGER SAL_TR2
BEFORE UPDATE ON EMP1
FOR EACH ROW
WHEN (NEW.EMPNO>0)
DECLARE
LV_DIFFERENCE_SAL NUMBER;
BEGIN
LV_DIFFERENCE_SAL :=:NEW.SAL-:OLD.SAL;
DBMS_OUTPUT.PUT_LINE('OLD SALARY IS : '||:OLD.SAL);
DBMS_OUTPUT.PUT_LINE('NEW SALARY IS : '||:NEW.SAL);
DBMS_OUTPUT.PUT_LINE('DIFFERENCE SALARY IS : '||LV_DIFFERENCE_SAL);
END;
/
UPDATE EMP1
SET SAL=20000
WHERE EMPNO=7934;
/
SELECT * FROM EMP1;
/
EX:
***
-->Trigger adds the increment amount automatically to Employ salary.(Using IMPLICIT Cursor)
DROP TABLE EMP1;
/
CREATE TABLE EMP1 AS SELECT * FROM EMP;
/
DROP TABLE XX_ABC;
/
CREATE TABLE XX_ABC
(
EMPNO NUMBER,
SAL NUMBER
);
/
SELECT * FROM XX_ABC;
/
CREATE OR REPLACE TRIGGER SAL_TRG
BEFORE INSERT ON XX_ABC
FOR EACH ROW
BEGIN
UPDATE EMP1
SET SAL=SAL+:NEW.SAL
WHERE EMPNO=:NEW.EMPNO;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('UPDATED RECORDS SUCCESFULLY');
ELSE
--DBMS_OUTPUT.PUT_LINE('EMPNO DOES NOT EXIST');
RAISE_APPLICATION_ERROR(-20890,'EMPNO DOES NOT EXIST');
END IF;
END;
/
INSERT INTO XX_ABC VALUES(7839,2000);
/
SELECT * FROM EMP1 WHERE EMPNO=7839;
/
SELECT EMPNO,SAL FROM EMP1 WHERE EMPNO=7698; --2850
/
INSERT INTO XX_ABC VALUES(7698,1500);
/
SELECT EMPNO,SAL FROM EMP1 WHERE EMPNO=7698;
/
INSERT INTO XX_ABC VALUES (2000,4000); --EMPNO DOES NOT EXIST(OUTPUT)
/
EX:
***
-->Write a PL/SL Row level trigger on emp table whenever user inserting data into a emp table salary should be more than 5000.
CREATE OR REPLACE TRIGGER TR4
BEFORE INSERT ON EMP1
FOR EACH ROW
BEGIN
IF :NEW.SAL<5000 THEN
RAISE_APPLICATION_ERROR(-20313,'SALARY SHOULD BE MORE THAN 5000');
END IF;
END;
/
INSERT INTO EMP1(EMPNO,ENAME,SAL) VALUES (1000,'MOHAN',4999);
/
CREATE OR REPLACE TRIGGER TR5
BEFORE INSERT ON EMP1
FOR EACH ROW
BEGIN
IF :NEW.ENAME=LOWER(:NEW.ENAME) THEN
RAISE_APPLICATION_ERROR(-20313,'WE CAN NOT INSERT LOWER CASE VALUES');
END IF;
END;
/
INSERT INTO EMP1(EMPNO,ENAME) VALUES(100,'mohan');
/
-- WITHOUT ON DELETE CASCADE
/
CREATE TABLE XX_DEPT_PARENT
(
DEPTNO NUMBER PRIMARY KEY,
DNAME VARCHAR2(20),
LOC VARCHAR2(20)
);
/
CREATE TABLE XX_EMP_CHILD
(
EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(20),
JOB VARCHAR2(20),
SAL NUMBER,
DEPTNO NUMBER REFERENCES XX_DEPT_PARENT(DEPTNO)
);
/
INSERT INTO XX_DEPT_PARENT VALUES (10,'HR','HYD');
/
INSERT INTO XX_DEPT_PARENT VALUES (20,'SALES','CHN');
/
INSERT INTO XX_DEPT_PARENT VALUES (30,'RESEARCH','BNG');
/
INSERT INTO XX_DEPT_PARENT VALUES (40,'MARKETING','MUM');
/
COMMIT;
/
INSERT INTO XX_EMP_CHILD VALUES (1,'A','HR',1000,10);
/
INSERT INTO XX_EMP_CHILD VALUES (2,'B','HR1',2000,10);
/
INSERT INTO XX_EMP_CHILD VALUES (3,'C','HR2',3000,20);
/
INSERT INTO XX_EMP_CHILD VALUES (4,'D','HR3',4000,20);
/
INSERT INTO XX_EMP_CHILD VALUES (5,'E','HR4',5000,10);
/
INSERT INTO XX_EMP_CHILD VALUES (6,'F','HR5',1000,30);
/
INSERT INTO XX_EMP_CHILD VALUES (7,'G','HR6',2000,20);
/
INSERT INTO XX_EMP_CHILD VALUES (8,'H','HR7',4000,30);
/
COMMIT;
/
SELECT * FROM XX_DEPT_PARENT;
/
SELECT * FROM XX_EMP_CHILD;
/
DELETE FROM XX_DEPT_PARENT;
/
DROP TRIGGER TR6;
/
CREATE OR REPLACE TRIGGER TR6
AFTER DELETE ON XX_DEPT_PARENT
FOR EACH ROW
BEGIN
DELETE FROM XX_EMP_CHILD WHERE DEPTNO=:OLD.DEPTNO;
END;
/
SELECT * FROM XX_DEPT_PARENT WHERE DEPTNO=10;
/
SELECT * FROM XX_EMP_CHILD WHERE DEPTNO=10;
/
DELETE FROM XX_DEPT_PARENT WHERE DEPTNO=10;
/
ALTER TRIGGER TR6 DISABLE;
/
DELETE FROM XX_DEPT_PARENT WHERE DEPTNO=20;
/
ALTER TRIGGER TR6 ENABLE;
/
DELETE FROM XX_DEPT_PARENT WHERE DEPTNO=20;
/
SELECT * FROM XX_DEPT_PARENT WHERE DEPTNO=20;
/
SELECT * FROM XX_EMP_CHILD WHERE DEPTNO=20;
/
--WITH ON DELETE CASCADE
/
CREATE TABLE XX_DEPT_PARENT_1
(
DEPTNO NUMBER PRIMARY KEY,
DNAME VARCHAR2(20),
LOC VARCHAR2(20)
);
/
CREATE TABLE XX_EMP_CHILD_1
(
EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(20),
JOB VARCHAR2(20),
SAL NUMBER,
DEPTNO NUMBER REFERENCES XX_DEPT_PARENT_1(DEPTNO) ON DELETE CASCADE
);
/
INSERT INTO XX_DEPT_PARENT_1 VALUES (10,'HR','HYD');
/
INSERT INTO XX_DEPT_PARENT_1 VALUES (20,'SALES','CHN');
/
INSERT INTO XX_DEPT_PARENT_1 VALUES (30,'RESEARCH','BNG');
/
INSERT INTO XX_DEPT_PARENT_1 VALUES (40,'MARKETING','MUM');
/
COMMIT;
/
INSERT INTO XX_EMP_CHILD_1 VALUES (1,'A','HR',1000,10);
/
INSERT INTO XX_EMP_CHILD_1 VALUES (2,'B','HR1',2000,10);
/
INSERT INTO XX_EMP_CHILD_1 VALUES (3,'C','HR2',3000,20);
/
INSERT INTO XX_EMP_CHILD_1 VALUES (4,'D','HR3',4000,20);
/
INSERT INTO XX_EMP_CHILD_1 VALUES (5,'E','HR4',5000,10);
/
INSERT INTO XX_EMP_CHILD_1 VALUES (6,'F','HR5',1000,30);
/
INSERT INTO XX_EMP_CHILD_1 VALUES (7,'G','HR6',2000,20);
/
INSERT INTO XX_EMP_CHILD_1 VALUES (8,'H','HR7',4000,30);
/
COMMIT;
/
SELECT * FROM XX_DEPT_PARENT_1;
/
SELECT * FROM XX_EMP_CHILD_1;
/
-->Write a PL/SQL row level trigger on master,child tables while implement on delete cascade concept using on delete cascade.
/
CREATE OR REPLACE TRIGGER TR6_1
AFTER DELETE ON XX_DEPT_PARENT_1
FOR EACH ROW
BEGIN
DELETE FROM XX_EMP_CHILD_1 WHERE DEPTNO=:OLD.DEPTNO;
END;
/
DELETE FROM XX_DEPT_PARENT_1 WHERE DEPTNO=10;
/
--CHECK THE OUTPUT; --MUTATING ERROR
/
--Write a PL/SQL program row level trigger on dept table whenever updating deptno's in dept table automatically
those deptno's modified into emp table.
CREATE OR REPLACE TRIGGER TR8
AFTER UPDATE ON DEPT2
FOR EACH ROW
BEGIN
UPDATE EMP2 SET DEPTNO=:NEW.DEPTNO WHERE DEPTNO=:OLD.DEPTNO;
END;
/
UPDATE DEPT2
SET DEPTNO=50
WHERE DEPTNO=10;
/
Ex:
****
-->Write a Trigger program to take employee backup.
-->Create a backup table with same structure of EMP table.
-->Create table Backup as select * from emp where 1=2;
-->If where condition is false it copies Employee table structure only.
--CHECK THE PROGRAM
Trigger Name: BKP_TR
Table Name: EMP1
Trigger Event: After Delete
DROP TABLE EMP1;
/
CREATE TABLE EMP1 AS SELECT * FROM EMP;
/
SELECT * FROM EMP1;
CREATE TABLE XX_EMP_BKP AS SELECT * FROM EMP WHERE 1=2;
/
SELECT * FROM XX_EMP_BKP;
/
CREATE OR REPLACE TRIGGER BKP_TR
AFTER DELETE ON EMP1
FOR EACH ROW
BEGIN
INSERT INTO XX_EMP_BKP
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
)
VALUES
(
:OLD.EMPNO,
:OLD.ENAME,
:OLD.JOB,
:OLD.MGR,
:OLD.HIREDATE,
:OLD.SAL,
:OLD.COMM,
:OLD.DEPTNO
);
END;
/
SELECT * FROM EMP1;
/
DELETE FROM EMP1 WHERE EMPNO=7839;
/
SELECT * FROM XX_EMP_BKP;
/
DELETE FROM EMP1 WHERE EMPNO IN (7698,7782);
/
SELECT * FROM XX_EMP_BKP;
/
DELETE FROM EMP1;
/
SELECT * FROM XX_EMP_BKP;
/
DROP TABLE EMP1;
/
DROP TABLE EMP2;
/
CREATE TABLE EMP1 AS SELECT * FROM EMP;
/
CREATE TABLE EMP2 AS SELECT * FROM EMP WHERE 1=2;
/
SELECT * FROM EMP1;
/
SELECT * FROM EMP2;
/
create or replace trigger tr1
after insert on emp1
for each row
declare
pragma autonomous_transaction;
cursor c1 is select * from emp1;
lv_empno number;
lv_ename varchar2(20);
lv_job varchar2(10);
lv_mgr number;
lv_hiredate date;
lv_sal number;
lv_comm number;
lv_deptno number;
begin
for i in c1
loop
begin
select empno,ename,job,mgr,hiredate,sal,comm,deptno
into lv_empno,lv_ename,lv_job,lv_mgr,lv_hiredate,lv_sal,lv_comm,lv_deptno
from emp1
where empno=i.empno;
end;
if sql%found then
insert into emp2(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(lv_empno,lv_ename,lv_job,lv_mgr,lv_hiredate,lv_sal,lv_comm,lv_deptno);
end if;
end loop;
commit;
end;
/
insert into emp1(empno,ename,sal) values(2,'B',1000);
/
select * from emp1;
/
select * from emp2;
/
TRUNCATE TABLE EMP2;
Mutating trigger:
*****************
-->Mutating into a row level trigger based on a table,trigger body can not read data from same table and also we can not perform DML operations on same table.
-->If we are trying to this oracle server returns an error is table mutating.
-->This error is called miutatng error.
-->This trigger is called mutating trigger.
-->Mutating errors are not occured in statement level trigger because through these statement level trigger when we are performing DML operations automatically data
committed into database.
-->Where as in row level trigger when we are performing transactions data is not committed and also again we are reading this data from the same table then only mutating
error is occured.
-->To avoid this error we are using autonomous transactons in triggers.
EX:
***
TRUNCATE TABLE EMP1;
/
INSERT INTO EMP1 SELECT * FROM EMP;
/
COMMIT;
/
CREATE OR REPLACE TRIGGER TR11
AFTER DELETE ON EMP1
FOR EACH ROW
DECLARE
A NUMBER(10);
BEGIN
SELECT COUNT(*) INTO A FROM EMP1;
END;
/
DELETE FROM EMP1 WHERE DEPTNO=10; --MUTATING ERROR FIRED TO OVER COME THE ISSUE USING AUTONONOUS TRANSACTON
/
CREATE OR REPLACE TRIGGER TR11
AFTER DELETE ON EMP1
FOR EACH ROW
DECLARE
LV_COUNT NUMBER(10);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT COUNT(*) INTO LV_COUNT FROM EMP1;
--DBMS_OUTPUT.PUT_LINE(LV_COUNT);
COMMIT;
END;
/
DROP TABLE EMP_T;
/
CREATE TABLE EMP_T(EMPNO NUMBER PRIMARY KEY,ENAME VARCHAR2(20),JOB VARCHAR2(20),SAL NUMBER,DEPTNO NUMBER);
/
DROP TABLE EMP_SAL_LOG;
/
CREATE TABLE EMP_SAL_LOG(EMPNO NUMBER,UPDATE_LOG VARCHAR2(1000));
/
INSERT INTO EMP_T VALUES (1000,'KING','CEO',150000,10);
/
INSERT INTO EMP_T VALUES (1001,'SMITH','SALEMAN',30000,20);
/
INSERT INTO EMP_T VALUES (1002,'ADAMS','MANAGER',80000,20);
/
INSERT INTO EMP_T VALUES (1003,'JONES','CLERK',20000,30);
/
INSERT INTO EMP_T VALUES (1004,'CLARK','HR',90000,10);
/
SELECT * FROM EMP_T;
/
COMMIT;
/
CREATE OR REPLACE TRIGGER VALIDATE_SAL
BEFORE UPDATE OF SAL ON EMP_T
FOR EACH ROW
DECLARE
LV_CEO_SAL NUMBER :=100000;
BEGIN
IF :NEW.SAL< LV_CEO_SAL THEN
INSERT INTO EMP_SAL_LOG VALUES(:NEW.EMPNO,'UPDATED SALARY SUCCESSFULLY : '||'OLD SAL = '||:OLD.SAL||' , NEW SAL = '||:NEW.SAL);
ELSE
:NEW.SAL :=:OLD.SAL;
INSERT INTO EMP_SAL_LOG VALUES(:NEW.EMPNO,'SALARY NOT UPDATED : EMP SALARY CAN NOT BE MORETHAN '||LV_CEO_SAL);
END IF;
END;
/
SELECT * FROM EMP_T;
/
UPDATE EMP_T
SET SAL=40000
WHERE EMPNO=1003;
/
SELECT * FROM EMP_SAL_LOG;
/
UPDATE EMP_T
SET SAL=140000
WHERE EMPNO=1004;
/
SELECT * FROM EMP_SAL_LOG;
/
CREATE OR REPLACE TRIGGER VALIDATE_SAL
BEFORE UPDATE OF SAL ON EMP_T
FOR EACH ROW
DECLARE
LV_CEO_SAL NUMBER;
BEGIN
SELECT SAL
INTO LV_CEO_SAL
FROM EMP_T
WHERE JOB='CEO';
IF :NEW.SAL< LV_CEO_SAL THEN
INSERT INTO EMP_SAL_LOG VALUES(:NEW.EMPNO,'UPDATED SALARY SUCCESSFULLY : '||'OLD SAL = '||:OLD.SAL||' , NEW SAL = '||:NEW.SAL);
ELSE
:NEW.SAL :=:OLD.SAL;
INSERT INTO EMP_SAL_LOG VALUES(:NEW.EMPNO,'SALARY NOT UPDATED : EMP SALARY CAN NOT BE MORETHAN '||LV_CEO_SAL);
END IF;
END;
/
TRUNCATE TABLE EMP_SAL_LOG;
/
UPDATE EMP_T SET SAL=8000 WHERE EMPNO=1003;
/
SELECT * FROM EMP_T;
/
SELECT * FROM EMP_SAL_LOG;
/
CREATE OR REPLACE TRIGGER VALIDATE_SAL
BEFORE UPDATE OF SAL ON EMP_T
FOR EACH ROW
DECLARE
LV_CEO_SAL NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT SAL
INTO LV_CEO_SAL
FROM EMP_T
WHERE JOB='CEO';
IF :NEW.SAL< LV_CEO_SAL THEN
INSERT INTO EMP_SAL_LOG VALUES(:NEW.EMPNO,'UPDATED SALARY SUCCESSFULLY : '||'OLD SAL = '||:OLD.SAL||' , NEW SAL = '||:NEW.SAL);
COMMIT;
ELSE
:NEW.SAL :=:OLD.SAL;
INSERT INTO EMP_SAL_LOG VALUES(:NEW.EMPNO,'SALARY NOT UPDATED : EMP SALARY CAN NOT BE MORETHAN '||LV_CEO_SAL);
COMMIT;
END IF;
END;
/
TRUNCATE TABLE EMP_SAL_LOG;
/
UPDATE EMP_T SET SAL=80000 WHERE EMPNO=1003;
/
SELECT * FROM EMP_SAL_LOG;
/
CREATE OR REPLACE PACKAGE PKG1
IS
LV_CEO_SAL NUMBER;
END;
/
CREATE OR REPLACE TRIGGER VALIDATE_SAL
BEFORE UPDATE OF SAL ON EMP_T
BEGIN
SELECT SAL
INTO PKG1.LV_CEO_SAL
FROM EMP_T
WHERE JOB='CEO';
END;
/
CREATE OR REPLACE TRIGGER VALIDATE_SAL
BEFORE UPDATE OF SAL ON EMP_T
FOR EACH ROW
BEGIN
IF (:NEW.SAL< PKG1.LV_CEO_SAL AND :OLD.JOB<>'CEO' ) OR (:OLD.JOB='CEO') THEN
INSERT INTO EMP_SAL_LOG VALUES(:NEW.EMPNO,'UPDATED SALARY SUCCESSFULLY : '||'OLD SAL = '||:OLD.SAL||' , NEW SAL = '||:NEW.SAL);
ELSE
:NEW.SAL :=:OLD.SAL;
INSERT INTO EMP_SAL_LOG VALUES(:NEW.EMPNO,'SALARY NOT UPDATED : EMP SALARY CAN NOT BE MORETHAN '||PKG1.LV_CEO_SAL);
END IF;
END;
/
TRUNCATE TABLE EMP_SAL_LOG;
/
SELECT * FROM EMP_T;
/
SELECT * FROM EMP_SAL_LOG;
/
UPDATE EMP_T SET SAL=29000 WHERE EMPNO=1003;
/
UPDATE EMP_T SET SAL=180000 WHERE EMPNO=1004;
/
SELECT * FROM EMP_SAL_LOG;
/
INSTEAD OF TRIGGERS;
********************
-->An INSTEAD OF trigger is a trigger that allows you to update data in tables via their view which cannot be modified directly through DML statements.
-->In Oracle, you can create an INSTEAD OF trigger for a view only. You cannot create an INSTEAD OF trigger for a table.
CREATE TABLE XXEMP_V AS SELECT * FROM EMP;
/
CREATE TABLE XXDEPT_V AS SELECT * FROM DEPT;
/
SELECT * FROM XXEMP_V;
/
SELECT * FROM XXDEPT_V;
/
CREATE OR REPLACE VIEW XXEMPDEPT_VIEW
AS
SELECT A.EMPNO,A.ENAME,A.JOB,A.SAL,A.HIREDATE,B.DNAME,B.LOC,A.DEPTNO
FROM XXEMP_V A,
XXDEPT_V B
WHERE 1=1
AND A.DEPTNO=B.DEPTNO;
/
INSERT INTO XXEMPDEPT_VIEW
(
EMPNO,
ENAME,
JOB,
SAL,
HIREDATE,
DNAME,
LOC,
DEPTNO
)
VALUES
(
1000,
'MOHAN',
'SOFTWARE',
6000,
'28-APR-21',
'ORACLE',
'HYD',
50
);
/
CREATE OR REPLACE TRIGGER TR12
INSTEAD OF INSERT ON XXEMPDEPT_VIEW
FOR EACH ROW
BEGIN
INSERT INTO XXEMP_V(EMPNO,ENAME,JOB,SAL,HIREDATE,DEPTNO)
VALUES (:NEW.EMPNO,:NEW.ENAME,:NEW.JOB,:NEW.SAL,:NEW.HIREDATE,:NEW.DEPTNO);
INSERT INTO XXDEPT_V(DNAME,LOC,DEPTNO)
VALUES
(:NEW.DNAME,:NEW.LOC,:NEW.DEPTNO);
END;
/
drop table emp1;
/
drop table dept1;
/
CREATE TABLE EMP1 AS SELECT * FROM EMP;
CREATE TABLE DEPT1 AS SELECT * FROM DEPT;
CREATE OR REPLACE VIEW XX_VIEW
AS
SELECT A.EMPNO,A.ENAME,A.JOB,A.SAL,A.HIREDATE,B.DNAME,B.LOC,A.DEPTNO
FROM EMP1 A,
DEPT1 B
WHERE 1=1
AND A.DEPTNO=B.DEPTNO;
/
SELECT *
FROM XX_VIEW;
/
DELETE FROM XX_VIEW WHERE DEPTNO=10;
/
CREATE OR REPLACE TRIGGER TR13
INSTEAD OF DELETE ON XX_VIEW
FOR EACH ROW
BEGIN
DELETE FROM EMP1 WHERE DEPTNO=:OLD.DEPTNO;
DELETE FROM DEPT1 WHERE DEPTNO=:OLD.DEPTNO;
END;
/
DELETE FROM XX_VIEW WHERE DEPTNO=10;
/
SELECT * FROM XX_VIEW;
/
DROP TABLE EMP1;
/
DROP TABLE DEPT1;
/
create table emp1 as select * from emp;
create table dept1 as select * from dept;
CREATE OR REPLACE VIEW XX_VIEW
AS
SELECT A.EMPNO,A.ENAME,A.JOB,A.SAL,A.HIREDATE,B.DNAME,B.LOC,A.DEPTNO
FROM EMP1 A,
DEPT1 B
WHERE 1=1
AND A.DEPTNO=B.DEPTNO;
/
select *
from xx_view;
SELECT *
FROM XX_VIEW;
UPDATE XX_VIEW
SET SAL=SAL+5000,
DEPTNO=30
WHERE EMPNO=7839;
/
/
SELECT *
FROM XX_VIEW
WHERE DEPTNO=10;
/
CREATE OR REPLACE TRIGGER UPDATE_TRG
INSTEAD OF UPDATE ON XX_VIEW
FOR EACH ROW
BEGIN
UPDATE EMP1 SET SAL=:NEW.SAL WHERE DEPTNO=:OLD.DEPTNO;
UPDATE DEPT1 SET DEPTNO=:NEW.DEPTNO WHERE DEPTNO=:OLD.DEPTNO;
END;
/
SELECT * FROM EMP1 WHERE DEPTNO=10;
SELECT * FROM DEPT1
Disabling Triggers:
******************
-->Alter table transaction disable all triggers;
-->Alter table bankmaster disable all triggers;
Enabling Triggers:
******************
-->Alter table transaction enable all triggers;
-->Alter table bankmaster enable all triggers;
Dropping Triggers:
******************
SYNTAX:
*******
Drop trigger <triggername>;
EX:
***
Drop trigger Holi_Trig;
/
Advantage of Database Triggers:
*********************************
- Automatic execution of code based on event .
- Used to impose user defined restrictions on Tables .
- Provides Security on Tables while Manipulating data from any where .
No comments:
Post a Comment