Table of Contents
Working With Non DML Triggers In Oracle
There are triggers available in database other than DML triggers. They are:
- DDL TRIGGERS: Triggering event:
- CREATE
- ALTER
- DROP
- LOGGING ON or OFF : A specific user logging on or off.
- A database shut down or startup
- A specific or any other error occurs
DDL TRIGGERS:
CREATE TABLE DDL_LOG
(
LOGID NUMBER GENERATED ALWAYS AS IDENTITY,
OS_USER VARCHAR2(100),
SCHEMA_USER VARCHAR2(100),
LOG_DATE DATE,
OBJECT_TYPE VARCHAR2(100),
OBJECT_NAME VARCHAR2(100),
OBJECT_OWNER VARCHAR2(100),
EVENT VARCHAR2(100),
REMARKS VARCHAR2(4000)
);
CREATE OR REPLACE TRIGGER TRIG_DDL_SCHEMA_LOG
AFTER DDL ON SCHEMA
BEGIN
IF (ORA_DICT_OBJ_NAME <> 'DDL_LOG' AND ORA_DICT_OBJ_TYPE = 'TABLE') THEN
NULL;
RETURN;
END IF;
IF (ORA_SYSEVENT = 'CREATE' AND (ORA_DICT_OBJ_NAME <> 'DDL_LOG' AND ORA_DICT_OBJ_TYPE = 'TABLE')) THEN
INSERT INTO DDL_LOG(OS_USER,
SCHEMA_USER,
LOG_DATE,
OBJECT_TYPE,
OBJECT_NAME,
OBJECT_OWNER,
EVENT,
REMARKS
)
VALUES(SYS_CONTEXT('USERENV','OS_USER'),
SYS_CONTEXT('USERENV','CURRENT_USER'),
SYSDATE,
ORA_DICT_OBJ_TYPE,
ORA_DICT_OBJ_NAME,
ORA_DICT_OBJ_OWNER,
ORA_SYSEVENT,
ORA_DICT_OBJ_NAME||' '||ORA_DICT_OBJ_TYPE||' Created Successfully.'
);
ELSIF (ORA_SYSEVENT = 'DROP' AND (ORA_DICT_OBJ_NAME <> 'DDL_LOG' AND ORA_DICT_OBJ_TYPE = 'TABLE')) THEN
INSERT INTO DDL_LOG(OS_USER,
SCHEMA_USER,
LOG_DATE,
OBJECT_TYPE,
OBJECT_NAME,
OBJECT_OWNER,
EVENT,
REMARKS
)
VALUES(SYS_CONTEXT('USERENV','OS_USER'),
SYS_CONTEXT('USERENV','CURRENT_USER'),
SYSDATE,
ORA_DICT_OBJ_TYPE,
ORA_DICT_OBJ_NAME,
ORA_DICT_OBJ_OWNER,
ORA_SYSEVENT,
ORA_DICT_OBJ_NAME||' '||ORA_DICT_OBJ_TYPE||' Dropped Successfully.'
);
ELSIF (ORA_SYSEVENT = 'ALTER' AND (ORA_DICT_OBJ_NAME <> 'DDL_LOG' AND ORA_DICT_OBJ_TYPE = 'TABLE')) THEN
INSERT INTO DDL_LOG(OS_USER,
SCHEMA_USER,
LOG_DATE,
OBJECT_TYPE,
OBJECT_NAME,
OBJECT_OWNER,
EVENT,
REMARKS
)
VALUES(SYS_CONTEXT('USERENV','OS_USER'),
SYS_CONTEXT('USERENV','CURRENT_USER'),
SYSDATE,
ORA_DICT_OBJ_TYPE,
ORA_DICT_OBJ_NAME,
ORA_DICT_OBJ_OWNER,
ORA_SYSEVENT,
ORA_DICT_OBJ_NAME||' '||ORA_DICT_OBJ_TYPE||' Altered Successfully.'
);
END IF;
END;
/
CREATE TABLE AA_TEST(A NUMBER);
SELECT * FROM DDL_LOG;
ALTER TABLE AA_TEST ADD (B NUMBER);
SELECT * FROM DDL_LOG;
DROP TABLE AA_TEST;
SELECT * FROM DDL_LOG
LOGON & LOGOFF TRIGGER:
CREATE TABLE SCHEMA_LOG
(
LOGID NUMBER GENERATED ALWAYS AS IDENTITY,
OS_USER VARCHAR2(100),
SCHEMA_USER VARCHAR2(100),
LOG_DATE DATE,
EVENT VARCHAR2(100)
);
CREATE OR REPLACE TRIGGER TRIG_LOGON_SCHEMA_LOG
AFTER LOGON ON SCHEMA
BEGIN
INSERT INTO SCHEMA_LOG(OS_USER,
SCHEMA_USER,
LOG_DATE,
EVENT
)
VALUES(SYS_CONTEXT('USERENV','OS_USER'),
SYS_CONTEXT('USERENV','CURRENT_USER'),
SYSDATE,
'LOGON'
);
END;
/
conn hr/hr
SELECT * FROM SCHEMA_LOG;
CREATE OR REPLACE TRIGGER TRIG_LOOFF_SCHEMA_LOG
BEFORE LOGOFF ON SCHEMA
BEGIN
INSERT INTO SCHEMA_LOG(OS_USER,
SCHEMA_USER,
LOG_DATE,
EVENT
)
VALUES(SYS_CONTEXT('USERENV','OS_USER'),
SYS_CONTEXT('USERENV','CURRENT_USER'),
SYSDATE,
'LOGOFF'
);
END;
/
DISCONNECT HR
conn hr/hr
SELECT * FROM SCHEMA_LOG;
MUTATING TABLE:
A Mutating table is
- that is currently modified by an INSERT, UPDATE or DELETE statement
- or a table that might need to be updated by a declarative DELETE CASCADE referential integrity action.
- A table is not considered mutating for a STATEMENT trigger
- The trigger table itself is a mutating table or a table referencing it with FOREIGN KEY constraint.
- Mutating table restriction prevent a row trigger from seeing an inconsistent data.
Example:
CREATE OR REPLACE TRIGGER TRIG_MUTATING_TEST
BEFORE INSERT OR UPDATE OF JOB_ID, SALARY ON EMPLOYEES
FOR EACH ROW
WHEN (NEW.JOB_ID <> 'AD_PRES')
DECLARE
V_MIN_SAL EMPLOYEES.SALARY%TYPE;
V_MAX_SAL EMPLOYEES.SALARY%TYPE;
BEGIN
SELECT MIN(SALARY),
MAX(SALARY)
INTO V_MIN_SAL,
V_MAX_SAL
FROM EMPLOYEES
WHERE JOB_ID = :NEW.JOB_ID;
IF :NEW.SALARY < V_MIN_SAL OR :NEW.SALARY > V_MAX_SAL THEN
RAISE_APPLICATION_ERROR(-20202, 'SALARY OUT OF RANGE');
END IF;
END;
/
SELECT * FROM EMPLOYEES
WHERE JOB_ID = 'AD_VP';
UPDATE EMPLOYEES
SET SALARY = SALARY * 1.1
WHERE JOB_ID = 'AD_VP';
Error report -
SQL Error: ORA-04091: table HR.EMPLOYEES is mutating, trigger/function may not see it
ORA-06512: at "HR.TRIG_MUTATING_TEST", line 5
ORA-04088: error during execution of trigger 'HR.TRIG_MUTATING_TEST'
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
To rectify this we will use a PRAGMA AUTONOMOUS_TRANSACTION.
CREATE OR REPLACE TRIGGER TRIG_MUTATING_TEST
BEFORE INSERT OR UPDATE OF JOB_ID, SALARY ON EMPLOYEES
FOR EACH ROW
WHEN (NEW.JOB_ID <> 'AD_PRES')
DECLARE
V_MIN_SAL EMPLOYEES.SALARY%TYPE;
V_MAX_SAL EMPLOYEES.SALARY%TYPE;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT MIN(SALARY),
MAX(SALARY)
INTO V_MIN_SAL,
V_MAX_SAL
FROM EMPLOYEES
WHERE JOB_ID = :NEW.JOB_ID;
IF :NEW.SALARY < V_MIN_SAL OR :NEW.SALARY > V_MAX_SAL THEN
RAISE_APPLICATION_ERROR(-20202, 'SALARY OUT OF RANGE');
END IF;
END;
/
UPDATE EMPLOYEES
SET SALARY = SALARY * 1.1
WHERE JOB_ID = 'AD_VP';
Error report -
SQL Error: ORA-20202: SALARY OUT OF RANGE
ORA-06512: at "HR.TRIG_MUTATING_TEST", line 14
ORA-04088: error during execution of trigger 'HR.TRIG_MUTATING_TEST'
Now correct error message is coming.
Implementation of Triggers:
Use Triggers for following:
- SECURITY: Use trigger for accessing table according to data values.
- AUDITING: Track audit trail records on a table
- INTEGRITY: Trigger can enforce complex business integrity rule.
- REFERENTIAL INTEGRITY RULES: Trigger can enforce referential integrity rules.
- TABLE REPLICATION: Trigger can populate tables on a specific event
- DERIVED DATA: Triggers can compute derived data values automatically.
SECURITY:
- Triggers are used to enforce complex security on table data.
- Only use triggers to enforce security that cannot be defined by the security features provided with a oracle database.
E.g. Restrict users from doing DML operation on employees table during weekend or non-business hours.
CREATE TABLE PUBLIC_HOLIDAYS(HOLIDAY DATE);
INSERT INTO PUBLIC_HOLIDAYS VALUES(TO_DATE('01-JAN-2020', 'DD-MON-RRRR'));
INSERT INTO PUBLIC_HOLIDAYS VALUES(TO_DATE('26-JAN-2020', 'DD-MON-RRRR'));
INSERT INTO PUBLIC_HOLIDAYS VALUES(TO_DATE('13-MAR-2020', 'DD-MON-RRRR'));
INSERT INTO PUBLIC_HOLIDAYS VALUES(TO_DATE('20-APR-2020', 'DD-MON-RRRR'));
INSERT INTO PUBLIC_HOLIDAYS VALUES(TO_DATE('01-MAY-2020', 'DD-MON-RRRR'));
INSERT INTO PUBLIC_HOLIDAYS VALUES(TO_DATE('06-JUN-2020', 'DD-MON-RRRR'));
INSERT INTO PUBLIC_HOLIDAYS VALUES(TO_DATE('15-AUG-2020', 'DD-MON-RRRR'));
INSERT INTO PUBLIC_HOLIDAYS VALUES(TO_DATE('02-OCT-2020', 'DD-MON-RRRR'));
INSERT INTO PUBLIC_HOLIDAYS VALUES(TO_DATE('28-OCT-2020', 'DD-MON-RRRR'));
INSERT INTO PUBLIC_HOLIDAYS VALUES(TO_DATE('13-NOV-2020', 'DD-MON-RRRR'));
INSERT INTO PUBLIC_HOLIDAYS VALUES(TO_DATE('25-DEC-2020', 'DD-MON-RRRR'));
SELECT * FROM PUBLIC_HOLIDAYS;
CREATE OR REPLACE TRIGGER TRIG_SECURITY_TEST
BEFORE INSERT OR UPDATE OR DELETE ON EMPLOYEES
FOR EACH ROW
DECLARE
V_COUNT NUMBER := 0;
EXECPTION_WEEKENDS EXCEPTION;
EXCEPTION_HOLIDAY EXCEPTION;
EXCEPTION_NON_BUSINESS EXCEPTION;
BEGIN
IF TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
RAISE EXECPTION_WEEKENDS;
END IF;
SELECT COUNT(1)
INTO V_COUNT
FROM PUBLIC_HOLIDAYS
WHERE TRUNC(HOLIDAY) = TRUNC(SYSDATE);
IF V_COUNT > 0 THEN
RAISE EXCEPTION_HOLIDAY;
END IF;
IF (EXTRACT (HOUR FROM CAST(SYSDATE AS TIMESTAMP)) < 8
OR EXTRACT (HOUR FROM CAST(SYSDATE AS TIMESTAMP)) > 18) THEN
RAISE EXCEPTION_NON_BUSINESS;
END IF;
EXCEPTION
WHEN EXECPTION_WEEKENDS THEN
RAISE_APPLICATION_ERROR(-20202, 'DML OPERATION CANNOT BE DONE ON WEEKENDS');
WHEN EXCEPTION_HOLIDAY THEN
RAISE_APPLICATION_ERROR(-20203, 'DML OPERATION CANNOT BE DONE ON HOLIDAYS');
WHEN EXCEPTION_NON_BUSINESS THEN
RAISE_APPLICATION_ERROR(-20204, 'DML OPERATION CANNOT BE DONE DURING NON BUSINESS HOURS');
END;
/
SELECT SYSDATE, EXTRACT (HOUR FROM CAST(SYSDATE AS TIMESTAMP)) CURRENT_TIME FROM DUAL;
UPDATE EMPLOYEES
SET SALARY = SALARY
WHERE EMPLOYEE_ID = 100;
Error report -
SQL Error: ORA-20204: DML OPERATION CANNOT BE DONE DURING NON BUSINESS HOURS
ORA-06512: at "HR.TRIG_SECURITY_TEST", line 30
ORA-04088: error during execution of trigger 'HR.TRIG_SECURITY_TEST'
INSERT INTO PUBLIC_HOLIDAYS VALUES(TO_DATE('29-JUN-2020', 'DD-MON-RRRR'));
UPDATE EMPLOYEES
SET SALARY = SALARY
WHERE EMPLOYEE_ID = 100;
Error report -
SQL Error: ORA-20203: DML OPERATION CANNOT BE DONE ON HOLIDAYS
ORA-06512: at "HR.TRIG_SECURITY_TEST", line 28
ORA-04088: error during execution of trigger 'HR.TRIG_SECURITY_TEST'
DROP TRIGGER TRIG_SECURITY_TEST;
AUDITING:
TRIGGER TO KEEP AUDIT TRAIL RECORDS:
CREATE TABLE EMP_AUDIT_LOG
(AUDIT_ID NUMBER GENERATED ALWAYS AS IDENTITY,
EMPLOYEE_ID NUMBER,
AUDIT_OPS VARCHAR2(100),
USERID VARCHAR2(100),
AUDIT_DATE DATE,
REMARKS VARCHAR2(4000)
);
CREATE OR REPLACE TRIGGER TRIG_EMP_INTEGRITY_AUDIT_TEST
BEFORE INSERT OR UPDATE OR DELETE ON EMP_INTEGRITY_TEST
FOR EACH ROW
DECLARE
V_COUNT NUMBER := 0;
PROCEDURE SP_INSERT_EMP_AUDIT_LOG
(
P_EMPLOYEE_ID NUMBER,
P_AUDIT_OPS VARCHAR2,
P_USERID VARCHAR2,
P_AUDIT_DATE DATE,
P_REMARKS VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO EMP_AUDIT_LOG(EMPLOYEE_ID,
AUDIT_OPS,
USERID,
AUDIT_DATE,
REMARKS
)
VALUES(P_EMPLOYEE_ID,
P_AUDIT_OPS,
P_USERID,
P_AUDIT_DATE,
P_REMARKS
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
FUNCTION FUN_CHECK_DUPLICATE(P_EMPLOYEE_ID EMP_INTEGRITY_TEST.EMPLOYEE_ID%TYPE)
RETURN BOOLEAN
IS
V_COUNT NUMBER := 0;
BEGIN
SELECT COUNT(1)
INTO V_COUNT
FROM EMP_INTEGRITY_TEST
WHERE EMPLOYEE_ID = :NEW.EMPLOYEE_ID;
IF V_COUNT > 0 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END;
PROCEDURE SP_TRIGGER_ACTION(P_EMPLOYEE_ID EMP_INTEGRITY_TEST.EMPLOYEE_ID%TYPE,
P_ACTION VARCHAR2,
P_REMARKS VARCHAR2,
P_SUCCESS VARCHAR2
)
IS
BEGIN
SP_INSERT_EMP_AUDIT_LOG(P_EMPLOYEE_ID,
P_ACTION,
'SYSTEM',
SYSDATE,
P_REMARKS
);
IF P_SUCCESS <> 'Y' THEN
RAISE_APPLICATION_ERROR(-20202, P_REMARKS);
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
BEGIN
IF INSERTING THEN
IF NOT FUN_CHECK_DUPLICATE(:NEW.EMPLOYEE_ID) THEN
SP_TRIGGER_ACTION(:NEW.EMPLOYEE_ID,
'INSERT',
'EMPLOYEE ALREADY EXISTS',
'N'
);
ELSE
IF :NEW.HIRE_DATE <> SYSDATE THEN
SP_TRIGGER_ACTION(:NEW.EMPLOYEE_ID,
'INSERT',
'HIRE DATE SHOULD BE CURRENT DATE',
'N'
);
ELSIF :NEW.JOB_ID = 'SA_REP' AND :NEW.SALARY > 1000 THEN
SP_TRIGGER_ACTION(:NEW.EMPLOYEE_ID,
'INSERT',
'SA_REP CANNOT EARN MORE THAN 1000',
'N'
);
ELSE
SP_TRIGGER_ACTION(:NEW.EMPLOYEE_ID,
'INSERT',
'RECORD INSERTED',
'Y'
);
END IF;
END IF;
ELSIF UPDATING THEN
IF :OLD.JOB_ID = 'SA_REP' AND :NEW.SALARY > 1000 THEN
SP_TRIGGER_ACTION(:NEW.EMPLOYEE_ID,
'UPDATE',
'SA_REP CANNOT EARN MORE THAN 1000',
'N'
);
ELSE
SP_TRIGGER_ACTION(:NEW.EMPLOYEE_ID,
'UPDATE',
'RECORD UPDATED',
'Y'
);
END IF;
ELSIF DELETING THEN
SP_TRIGGER_ACTION(:NEW.EMPLOYEE_ID,
'DELETE',
'RECORD DELETED',
'Y'
);
END IF;
END;
/
INSERT INTO EMP_INTEGRITY_TEST(EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
SALARY,
HIRE_DATE,
DEPARTMENT_ID
)
VALUES(100,
'STEVEN',
'SMITH',
'SA_REP',
1200,
SYSDATE-1,
80);
Error report -
SQL Error: ORA-20202: HIRE DATE SHOULD BE CURRENT DATE
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 63
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 60
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 75
ORA-04088: error during execution of trigger 'HR.TRIG_EMP_INTEGRITY_AUDIT_TEST'
SELECT * FROM EMP_AUDIT_LOG;
INSERT INTO EMP_INTEGRITY_TEST(EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
SALARY,
HIRE_DATE,
DEPARTMENT_ID
)
VALUES(100,
'STEVEN',
'SMITH',
'SA_REP',
1200,
SYSDATE,
80);
Error report -
SQL Error: ORA-20202: SA_REP CANNOT EARN MORE THAN 1000
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 63
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 60
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 80
ORA-04088: error during execution of trigger 'HR.TRIG_EMP_INTEGRITY_AUDIT_TEST'
SELECT * FROM EMP_AUDIT_LOG;
INSERT INTO EMP_INTEGRITY_TEST(EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
SALARY,
HIRE_DATE,
DEPARTMENT_ID
)
VALUES(100,
'STEVEN',
'SMITH',
'SA_REP',
950,
SYSDATE,
80);
1 row inserted.
SELECT * FROM EMP_AUDIT_LOG;
UPDATE EMP_INTEGRITY_TEST
SET SALARY = 1500
WHERE EMPLOYEE_ID = 100;
Error report -
SQL Error: ORA-20202: SA_REP CANNOT EARN MORE THAN 1000
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 66
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 62
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 100
ORA-04088: error during execution of trigger 'HR.TRIG_EMP_INTEGRITY_AUDIT_TEST'
SELECT * FROM EMP_AUDIT_LOG;
UPDATE EMP_INTEGRITY_TEST
SET SALARY = 1000
WHERE EMPLOYEE_ID = 100;
1 row updated.
SELECT * FROM EMP_AUDIT_LOG;
INTEGRITY:
Trigger can enforce complex integrity rules.
CREATE OR REPLACE TRIGGER TRIG_EMP_INTEGRITY
BEFORE INSERT OR UPDATE OF DEPARTMENT_ID, EMPLOYEE_ID ON EMPLOYEES
FOR EACH ROW
WHEN (NEW.DEPARTMENT_ID IS NOT NULL)
DECLARE
CURSOR DEPT_CUR(P_DEPTNO DEPARTMENTS.DEPARTMENT_ID%TYPE)
IS
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_ID = P_DEPTNO;
CURSOR EMP_CUR(P_EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE)
IS
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMPLOYEE_ID;
V_DEPARTMENT_ID DEPARTMENTS.DEPARTMENT_ID%TYPE;
V_EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
INVALID_DEPARTMENT EXCEPTION;
DUPLICATE_EMPLOYEE EXCEPTION;
BEGIN
OPEN DEPT_CUR(:NEW.DEPARTMENT_ID);
FETCH DEPT_CUR INTO V_DEPARTMENT_ID;
IF DEPT_CUR%NOTFOUND THEN
RAISE INVALID_DEPARTMENT;
END IF;
CLOSE DEPT_CUR;
OPEN EMP_CUR(:NEW.EMPLOYEE_ID);
FETCH EMP_CUR INTO V_EMPLOYEE_ID;
IF EMP_CUR%FOUND THEN
RAISE DUPLICATE_EMPLOYEE;
END IF;
CLOSE EMP_CUR;
EXCEPTION
WHEN INVALID_DEPARTMENT THEN
RAISE_APPLICATION_ERROR(-20202, 'DEPARTMENT DOES NOT EXISTS');
CLOSE DEPT_CUR;
WHEN DUPLICATE_EMPLOYEE THEN
RAISE_APPLICATION_ERROR(-20203, 'EMPLOYEE ALREADY EXISTS');
CLOSE EMP_CUR;
END;
/
SELECT * FROM EMPLOYEES
WHERE EMPLOYEE_ID = 207;
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES (207,'Steve','Smith','SSMITH','515.123.9999',TO_DATE('17-12-03','DD-MM-RR'),'SA_REP',1550,NULL,NULL,80);
Error report -
SQL Error: ORA-20203: EMPLOYEE ALREADY EXISTS
ORA-06512: at "HR.TRIG_EMP_INTEGRITY", line 36
ORA-04088: error during execution of trigger 'HR.TRIG_EMP_INTEGRITY'
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES (208,'Steven','George','SGEORGE','515.123.9999',TO_DATE('17-12-03','DD-MM-RR'),'SA_REP',1550,NULL,NULL,300);
Error report -
SQL Error: ORA-20202: DEPARTMENT DOES NOT EXISTS
ORA-06512: at "HR.TRIG_EMP_INTEGRITY", line 34
ORA-04088: error during execution of trigger 'HR.TRIG_EMP_INTEGRITY'
DROP TRIGGER TRIG_EMP_INTEGRITY;
REFERENTIAL INTEGRITY RULES:
Trigger can enforce Referential Integrity rules.
CREATE OR REPLACE TRIGGER TRIG_DEPT_INTEGRITY
BEFORE UPDATE OR DELETE OF DEPARTMENT_ID ON DEPARTMENTS
FOR EACH ROW
DECLARE
CURSOR EMP_CUR(P_DEPTNO EMPLOYEES.DEPARTMENT_ID%TYPE)
IS
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = P_DEPTNO
FETCH FIRST ROWS ONLY;
V_EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
EMPLOYEE_EXISTS EXCEPTION;
BEGIN
OPEN EMP_CUR(:OLD.DEPARTMENT_ID);
FETCH EMP_CUR INTO V_EMPLOYEE_ID;
IF EMP_CUR%FOUND THEN
RAISE EMPLOYEE_EXISTS;
END IF;
CLOSE EMP_CUR;
EXCEPTION
WHEN EMPLOYEE_EXISTS THEN
RAISE_APPLICATION_ERROR(-20202, 'CANNOT DELETE/UPDATE AS EMPLOYEE EXISTS AGAINST THIS DEPARTMENT');
CLOSE EMP_CUR;
END;
/
DELETE DEPARTMENTS
WHERE DEPARTMENT_ID = 80;
Error report -
SQL Error: ORA-20202: CANNOT DELETE/UPDATE AS EMPLOYEE EXISTS AGAINST THIS DEPARTMENT
ORA-06512: at "HR.TRIG_DEPT_INTEGRITY", line 21
ORA-04088: error during execution of trigger 'HR.TRIG_DEPT_INTEGRITY'
UPDATE DEPARTMENTS
SET DEPARTMENT_ID = 300
WHERE DEPARTMENT_ID = 80;
Error report -
SQL Error: ORA-20202: CANNOT DELETE/UPDATE AS EMPLOYEE EXISTS AGAINST THIS DEPARTMENT
ORA-06512: at "HR.TRIG_DEPT_INTEGRITY", line 21
ORA-04088: error during execution of trigger 'HR.TRIG_DEPT_INTEGRITY'
ON DELETE SET NULL:
CREATE OR REPLACE TRIGGER TRIG_DEPT_SET_NULL
BEFORE UPDATE OR DELETE OF DEPARTMENT_ID ON DEPARTMENTS
FOR EACH ROW
BEGIN
IF (UPDATING AND :OLD.DEPARTMENT_ID <> :NEW.DEPARTMENT_ID) OR DELETING THEN
UPDATE EMPLOYEES
SET DEPARTMENT_ID = NULL
WHERE DEPARTMENT_ID = :OLD.DEPARTMENT_ID;
END IF;
END;
/
INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES(280, 'Retail Sales', NULL, 1700);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES (208,'Steven','George','SGEORGE','515.123.9999',TO_DATE('17-12-03','DD-MM-RR'),'SA_REP',1550,NULL,NULL,280);
SELECT * FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 280;
SELECT * FROM EMPLOYEES
WHERE EMPLOYEE_ID = 208;
UPDATE DEPARTMENTS
SET DEPARTMENT_ID = 290
WHERE DEPARTMENT_ID = 280;
SELECT * FROM EMPLOYEES
WHERE EMPLOYEE_ID = 208;
ROLLBACK;
CASCADING DELETE:
CREATE OR REPLACE TRIGGER TRIG_DEPT_CASCADE_DELETE
BEFORE DELETE ON DEPARTMENTS
FOR EACH ROW
BEGIN
DELETE EMPLOYEES
WHERE DEPARTMENT_ID = :OLD.DEPARTMENT_ID;
END;
/
SELECT * FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 280;
SELECT * FROM EMPLOYEES
WHERE EMPLOYEE_ID = 208;
DELETE DEPARTMENTS
WHERE DEPARTMENT_ID = 280;
SELECT * FROM EMPLOYEES
WHERE EMPLOYEE_ID = 208;
TABLE REPLICATION:
CREATE TABLE EMPLOYEES_REPLICATE_TEST
AS
SELECT * FROM EMPLOYEES
WHERE 1 = 2;
CREATE TABLE EMPLOYEES_REPLICATE_TEST_HST
AS
SELECT * FROM EMPLOYEES
WHERE 1 = 2;
CREATE OR REPLACE TRIGGER TRIG_EMP_TABLE_REPLICATE
AFTER INSERT OR UPDATE OR DELETE OF JOB_ID, SALARY ON EMPLOYEES_REPLICATE_TEST
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO EMPLOYEES_REPLICATE_TEST_HST(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
VALUES(:NEW.EMPLOYEE_ID, :NEW.FIRST_NAME, :NEW.LAST_NAME, :NEW.EMAIL, :NEW.PHONE_NUMBER, :NEW.HIRE_DATE, :NEW.JOB_ID, :NEW.SALARY, :NEW.COMMISSION_PCT, :NEW.MANAGER_ID, :NEW.DEPARTMENT_ID);
ELSIF (UPDATING AND :OLD.JOB_ID <> :NEW.JOB_ID) THEN
UPDATE EMPLOYEES_REPLICATE_TEST_HST
SET JOB_ID = :NEW.JOB_ID
WHERE EMPLOYEE_ID = :OLD.EMPLOYEE_ID;
ELSIF (UPDATING AND :OLD.SALARY <> :NEW.SALARY) THEN
UPDATE EMPLOYEES_REPLICATE_TEST_HST
SET SALARY = :NEW.SALARY
WHERE EMPLOYEE_ID = :OLD.EMPLOYEE_ID;
ELSIF DELETING THEN
DELETE EMPLOYEES_REPLICATE_TEST_HST
WHERE EMPLOYEE_ID = :OLD.EMPLOYEE_ID;
END IF;
END;
/
INSERT INTO EMPLOYEES_REPLICATE_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES (208,'Steven','George','SGEORGE','515.123.9999',TO_DATE('17-12-03','DD-MM-RR'),'SA_REP',1550,NULL,NULL,280);
SELECT * FROM EMPLOYEES_REPLICATE_TEST
WHERE EMPLOYEE_ID = 208;
SELECT * FROM EMPLOYEES_REPLICATE_TEST_HST
WHERE EMPLOYEE_ID = 208;
UPDATE EMPLOYEES_REPLICATE_TEST
SET SALARY = 1600
WHERE EMPLOYEE_ID = 208;
SELECT * FROM EMPLOYEES_REPLICATE_TEST
WHERE EMPLOYEE_ID = 208;
SELECT * FROM EMPLOYEES_REPLICATE_TEST_HST
WHERE EMPLOYEE_ID = 208;
DELETE EMPLOYEES_REPLICATE_TEST
WHERE EMPLOYEE_ID = 208;
SELECT * FROM EMPLOYEES_REPLICATE_TEST
WHERE EMPLOYEE_ID = 208;
SELECT * FROM EMPLOYEES_REPLICATE_TEST_HST
WHERE EMPLOYEE_ID = 208;
DERIVED DATA:
CREATE TABLE EMPLOYEES_CP
AS
SELECT * FROM EMPLOYEES;
CREATE TABLE DEPARTMENTS_CP
AS
SELECT * FROM DEPARTMENTS;
ALTER TABLE EMPLOYEES_CP
ADD (FULL_NAME VARCHAR2(60),
MANAGER_NAME VARCHAR2(60));
CREATE OR REPLACE TRIGGER TRIG_DERIVED_EMP
BEFORE INSERT OR UPDATE OF FIRST_NAME, LAST_NAME, MANAGER_ID ON EMPLOYEES_CP
FOR EACH ROW
DECLARE
V_MGR_NAME VARCHAR2(60);
BEGIN
IF INSERTING THEN
:NEW.FULL_NAME := UPPER(:NEW.FIRST_NAME||' '||:NEW.LAST_NAME);
BEGIN
SELECT FIRST_NAME||' '||LAST_NAME
INTO V_MGR_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = :NEW.MANAGER_ID;
EXCEPTION
WHEN OTHERS THEN
V_MGR_NAME := NULL;
END;
:NEW.MANAGER_NAME := UPPER(V_MGR_NAME);
ELSIF UPDATING THEN
IF (:OLD.FIRST_NAME <> :NEW.FIRST_NAME AND :OLD.LAST_NAME <> :NEW.LAST_NAME) THEN
:NEW.FULL_NAME := UPPER(:NEW.FIRST_NAME||' '||:NEW.LAST_NAME);
ELSIF (:OLD.FIRST_NAME <> :NEW.FIRST_NAME AND :OLD.LAST_NAME = :NEW.LAST_NAME) THEN
:NEW.FULL_NAME := UPPER(:NEW.FIRST_NAME||' '||:OLD.LAST_NAME);
ELSIF (:OLD.FIRST_NAME = :NEW.FIRST_NAME AND :OLD.LAST_NAME <> :NEW.LAST_NAME) THEN
:NEW.FULL_NAME := UPPER(:OLD.FIRST_NAME||' '||:NEW.LAST_NAME);
END IF;
IF :OLD.MANAGER_ID <> :NEW.MANAGER_ID THEN
BEGIN
SELECT FIRST_NAME||' '||LAST_NAME
INTO V_MGR_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = :NEW.MANAGER_ID;
EXCEPTION
WHEN OTHERS THEN
V_MGR_NAME := NULL;
END;
:NEW.MANAGER_NAME := UPPER(V_MGR_NAME);
END IF;
END IF;
END;
/
INSERT INTO EMPLOYEES_CP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES (208,'Steven','George','SGEORGE','515.123.9999',TO_DATE('17-12-03','DD-MM-RR'),'SA_REP',1550,NULL,100,80);
RELATED TOPICS:
