Trigger Solutions

Problems (For DML Triggers):

Q.1 Keep the backup of department data for DELETE and UPDATE.
Ans: CREATE OR REPLACE TRIGGER dept_backup_trg

AFTER DELETE OR UPDATE ON dept

FOR EACH ROW

BEGIN

INSERT INTO dept_backup (deptno, dname, loc)

VALUES (:OLD.deptno, :OLD.dname, :OLD.loc);

END;

Q.2 Secure emp table from SCOTT user for DELETE or UPDATE of manager and salesman records.
Ans: CREATE OR REPLACE TRIGGER emp_dml_check

BEFORE DELETE OR UPDATE ON emp

FOR EACH ROW

WHEN (USER = 'SCOTT')

BEGIN

IF UPPER(RTRIM(LTRIM(:OLD.JOB))) IN ('MANAGER', 'SALESMAN')

THEN

RAISE_APPLICATION_ERROR

(-20001, 'You can not update or delete MANAGER or SALESMAN records');

END IF;

END;

Q.3 Delete all related employees as soon as dept is deleted from dept table.
Ans: CREATE OR REPLACE TRIGGER Delete_Emp_Trg

AFTER DELETE ON dept

FOR EACH ROW

BEGIN

DELETE FROM emp WHERE deptno = :OLD.deptno;

END;

Problems (For DDL Triggers):

Q.1 Create a DDL trigger to prevent removal of any table under scott schema.
ANS. CREATE OR REPLACE TRIGGER Drop_Check_For_Scott

BEFORE DROP ON scott.SCHEMA

BEGIN

RAISE_APPLICATION_ERROR (-20001, 'No table can not be droped from SCOTT schema');

END;

2. Create a DDL trigger to prevent removal of emp table under scott schema.
ANS. CREATE OR REPLACE TRIGGER Drop_Check_For_Scott_on_Emp

BEFORE DROP ON scott.SCHEMA

BEGIN

IF UPPER(RTRIM(LTRIM(sys.dictionary_obj_name))) = 'EMP'

THEN

RAISE_APPLICATION_ERROR (-20001, 'EMP table can not be droped from SCOTT schema'); END IF;

END;

Q.3 create a DDL trigger to prevent removal of any table under any schema. (User must have ADMINISTER DATABASE TRIGGER privilege).
ANS. CREATE OR REPLACE TRIGGER Drop_Check_For_Any_Table

BEFORE DROP ON DATABASE

BEGIN

IF UPPER(RTRIM(LTRIM(sys.sysevent))) = 'DROP' AND UPPER(RTRIM(LTRIM(sys.dictionary_obj_Type))) = 'TABLE

THEN

RAISE_APPLICATION_ERROR (-20001, 'Drop Table is not allowed under this Database');

END IF;

END;

Problems (For Instead Of Triggers)

1. create a trigger to allow Data Manipulation on EMP and DEPT tables via the View.

1.1 Create a view on emp and dept tables combination.

1.2 Create Instead Of Trigger on the View.
Ans: CREATE OR REPLACE TRIGGER emp_dept_vw_trg

INSTEAD OF INSERT OR DELETE OR UPDATE ON emp_dept_vw

FOR EACH ROW

BEGIN

IF INSERTING = True THEN

INSERT INTO dept (deptno, dname, loc)

VALUES (:NEW.deptno, :NEW.dname, :NEW.loc);

--

INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)

VALUES (:NEW.empno, :NEW.ename, :NEW.job, :NEW.mgr, :NEW.hiredate, :NEW.sal, :NEW.comm, :NEW.deptno);

--

ELSIF UPDATING = True THEN

UPDATE dept SET dname = :NEW.dname, loc = :NEW.loc WHERE deptno = :NEW.deptno;

--

UPDATE emp SET ename = :NEW.ename, job = :NEW.job, mgr = :NEW.mgr, hiredate = :NEW.hiredate, sal = :NEW.sal, comm = :NEW.comm, deptno = :NEW.deptno WHERE empno = :NEW..empno; ELSE DELETE FROM emp WHERE empno = :OLD.empno; DELETE FROM dept

WHERE deptno = :OLD.deptno;

END IF;

END;

Problems (For Database Events Triggers)
Q.1 Create a trigger that denies login for any user except SYSTEM or INTERNAL users.
Ans. CREATE OR REPLACE TRIGGER check_user_login

AFTER LOGON ON DATABASE

BEGIN ]

IF :sys.login_user NOT IN ('SYS', 'SYSTEM')

THEN

RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to Login');

END IF;

END;

Q.2 Create a trigger to load a package into KEEP buffer as soon as Database is started.
Ans. CREATE OR REPLACE TRIGGER pin_package

AFTER STARTUP ON DATABASE

BEGIN

DBMS_SHARED_POOL.KEEP ('SCOTT.EMP_PG', 'P');

END;