Triggers

                                            TRIGGERS

TRIGGERS

IT IS A PL/SQL BLOCK OR PL/SQL PROCEDURE ASOCITED WITH A TABLE,VIEW,SCHEMA OR DATABASE EXECUTED AUTOMATICALLY RATHER IN TECHNICAL TERMS IMPLICITLY FIRED AUTOMATICALLY WHENEVER A SPECIFIC EVENT OCCURS UPON THE OBJECT ASSOCIAYED WITH.

TYPES OF TRIGGERS

THERE ARE TWO TYPES OF TRIGGERS.

APPLICATION TRIGGER:-FIRED WHENEVER AN EVENT //CAN BE CONSIDERED AS A DML OPERATION// OCCURS UPON A PARTICULAR APPLICATION.

DATABASE TRIGGER:-FIRED WHENVER AN EVENT (IN THE SENSE ANY DML OPERATION OR ANY SYSTEM EVENT I.E LOGON OR SHUTDOWN) OCCURS ON A SCHEMA OR DATABASE.THESE ARE FIRED IMPLICITLY

INSTEAD OF TRIGGERS

THESE ARE ONLY LIMITED TO THE VIEWS .THE NAME SIGNIFIES THE OPERATION OF THE TRIGGER.TO BE MORE ELABORATIVE WHENEVER A DML IS OPERATED UPON A VIEW THE ACTION IS TAKEN CARE OF BY THE INSTEAD OF TRIGGER.RATHER IF ANY OTHER TRIGGERS ARE ASSOCIATED WITH THAT TABLE THEN THOSE TRIGGER WILL BE FIRED.

WHY THE TRIGGERS ARE DESIGNED?

FOR RELATED TASKS TO BE PERFORMED AND TO CENTRALIZE THE GLOBAL OPERATIONS WHICH TAKES CARE OF THE ACTIONS RATHER CONSIDERING WHATEVER APPLIACTION OR WHOEVER THE USER MAY BE.

CREATING DML TRIGGERS

A TRIGGER CONTAINS

A) TRIGGER TIMING DESCRIBES ABOUT THE FIRING OF THE TRIGGER WRT TRIGGERING EVENT.
B) TRIGGERING EVENT DESCRIBES ABOUT THE DML WHICH IS TO BE TAKEN CARE FOR THE BY ACTIONS TO BE RAISED.
C) TRIGGER TYPE DESCRIBES ABOUT THE NUMBER OF TIMES OF EXECUTION OF TRIGGER.
D) TRIGGER BODY DESCRIBES ABOUT THE ACTIONS TAKEN CARE OF BY THE TRIBGGER.
DML TRGGER COMPONENTS

TRIGGER TIMING

A) BEFORE TRIGGERS EXECUTION OF TRIGGER BOBY OCCURS BEFORE THE DML EVENT IS OPERATED UPON AN OBJECT.
B) AFTER TRIGGERS EXECUTION OF TRIGGER BOBY OCCURS AFTER THE DML EVENT IS OPERATED UPON AN OBJECT.
C) INSTEAD OF SPECIFIES A SEPARATE EXECUTION PROCESS APART FROM THE TRIGGERING STATEMENT.THESE ACT UPON THE VIEWS AND ARE NOT MODIFIABLE.

DESCRIPTIONS

BEFORE TRIGGERS ARE USED TO DETERMINE THE STATUS OF TRIGGER STATEMENT WHETHER TO BE COMPLETED.ALSO BETTER EXPLANATION CAN BE ROLLBACK.ALSO TO FETCH THE COLUMN VALUES PRIOR TO EXECUTION AND TO VALIDATE RULES OF BUSINESS.

AFTER TRIGGERS ARE USED TO COMPLETE THE ACTON BEFORE TRIGGERING ACTION.IF THERE IS A PRESENCE OF A BEFORE TRIGGER THEN TO INITIATE A DIFFERENT ACTION.

INSTEAD OF TRIGGERS ARE USED TO MODIFY THE VIEWS WHICH CANNOT BE MODIFIED BY A SQL DML STATEMENT DUE TO LACK OF MODIFICATION INHERITANCE.THESE WORK IN THE BACKGROUND ACCORDING TO THE DML ACTIONS SPECIFIED IN THE TRIGGERING BODY.

TRIGGERING EVENTS CAN BE SPECIFICALLY LINKED WITH THE DMLS SUCH AS INSERT, UPDATE OR A DELETE.IN CASE OF UPDATE DML THE COLUMN LIST ARE TO SPECIFIED FOR WHICH THE TRIGGERING ACTIONS ARE TO BE TAKEN CARE OF.

TRIGGER TYPE SPECIFIES WHETHER THE TRIGGER IS TO BE FIRED FOR EACH ROW OR FOR MULTIPLE ROWS (STATEMENT TRIGGERS).THERE ARE TWO TYPES OF TRIGGERS.

1) ROW TRIGGERS ARE EXECUTED ONCE FOR EACH ROW RETRIEVED BY THE DML SPECIFIED IN THE STATEMENT.IT IS NOT EXECUTED IF THE STATEMENT DOES NOT RETURN ANY VALUE.IT IS NOT EXECUTED WHEN NO ROWS ARE SELECTED.

2) STATEMENT TRIGGER IS FIRED ONCE ON BEHALF OF THE TRIGGERING EVENT EVEN IF NO ROWS ARE AFFECTED AT ALL.

TRIGGER BODY EXPLAINS ABOUT THE ACTIONS TO BE TAKEN CARE OF BY THE TRIGGER.IT CAN BE PL/SQL BLOCK OR A CALL PROCEDURE.

NOTE
1) WHEN THE TRIGGERING DATA MANIPULATION STATEMENT EFFECTS THE A SINGLE ROW, BOTH THE ROW TRIGGER AND STATEMENT TRIGGER FIRE EXACTLY ONCE PROVIDED THE TYPE OF TRIGGER THAT HAS BEEN MENTIONED IN THE TRIGGER BODY.

2) WHEN A TRIGGERING DATA MANIPULATION STATEMENT AFFECTS MULTIPLE ROWS THEN THE STATEMENT TRIGGER FIRES EXACTLY ONCE AND THE ROW TRIGGER FIRES ONCE FOR EVERY ROWEFFECTED BY THE STATEMENT.

SYNTAX
CREATE TRIGGER 
TIMING
EVENT1 OR OR 
ON 


TRIGGER NAME SHOULD BE UNIQUE COMPARED TO OTHER TRIGGERS.

SPECIFIES THE TIME WHEN THE TRIGGER WILL FIRE
EITHER OR 

IDENTIFIES THE DML THAT CAUSES THE TRIGGER TO FIRE.
EITHER ,, OR ALL OF THE THREE.
NAME OF THE TABLE ASSOCIATED WITH THE TRIGGER.

EXPLAINS ABOUT THE ACTIONS PERFORMED.
IT BEGINS WITH A DECALERE AND END OR A CALL OF PROCEDURE.

USING COLUMNS NAMES WITH UPDATE TRIGGERS INCREASE THE PERFORMANCE BECAUSE THE TRIGGER IS FIRED ONLY WHEN THE UPDATION OF CONCERNED COLUMN OCCURS.IT IS NO WHERE CONCERNNED WITH THE UPDATION OF ANY OTHER COLUMNS OF THE DESCRIBED TABLE IN THE TRIGGER.

EXAMPLE:

IN THE DESCRIBED TRIGGER WHICH IMPLEMENTS THE BUSSINESS RULES THAT RESTRICTS THE ACCESS OF DATABASE TABLE AFTER THE OFFICE HOURS AND HOLIDAYS PROVIDED THE WORK DAY CALENDER IS 5 DAYS A WEEK.

CREATE OR REPLACE TRIGGER SECURE_EMPLOYEES
BEFORE INSERT OR DELETE OR UPDATE ON EMPLOYEES
BEGIN
IF (TO_CHAR (SYSDATE,’DY’) IN (‘SAT’,’SUN’)) OR
(TO_CHAR (SYSDATE,’HH24’) NOT BETWEEN ‘08’ AND ‘18’)
THEN
IF INSERTING THEN
RAISE_APPLICATION_ERROR (-20500,’UR STATEMENT’); 
ELSIF DELETING THEN 
RAISE_APPLICATION_ERROR (-20500,’UR STATEMENT’);
ELSIF UPDATING THEN
RAISE_APPLICATION_ERROR (-20500,’UR STATEMENT’);
ELSE
RAISE_APPLICATION_ERROR (-20500,’UR SATAMENT2’);
END IF;
END IF;
END;

DML ROW TRIGGERS

WE HAVE TO SPECIFY A SPECIAL PHRASE FOR INITIATING A ROW TRIGGER.REFERENING TO THE ABOVE MENTIONED SYNTAX OF TRIGGER AFTER THE TABLE NAME ‘FOR EACH ROW’ PHRASE IF SPECIFIED INDICATES THE TRIGGER TO BE A ROW TRIGGER.HERE THE NEW VALUES AND THE OLD VALUES ARE ALSO REFERED FOR CORELATION BETWEEN THE OLD VALUES AND NEW VALUES.

WE CAN ALSO RESTRICT THE FIRING OF A ROW TRIGGER BY SPECIFING A WHEN CLAUSE AFTER THE PHRASE MENTIONED ABOVE.

EXAMPLE

CREATE OR REPLACE TRIGGER RESTRICT_SALARY
BEFORE INSERT OR UPDATE OF SALARY ON EMPLOYEES
FOR EACH ROW
BEGIN
IF NOT (:NEW.JOB_ID IN (‘AD_PRES’,’AD_VP’))
AND (:NEW.SALARY) > 15000
THEN
RAISE_APPLICATION_ERROR (-20500, )
END IF;
END;

EXPALAINATION

FOR AN UPDATE OR INSERT UPON EMPLOYEES TABLE IF THE JOB_ID SPECIED IS OTHER THAN AD_PRES AND AD_VP AND SALARY SPECIFIED IS GREATER THAN 15000 IN THE CASE TRIGGER IS FIRED.IN STRAIGHT EAPLAINTION THE EMPLOYEES WHICH HAVE A JOB_ID OF AD_PRES AND AD_VP CAN ONLY EARN A SALARY GREATER THAN 15000.

RESTRICTING A ROW TRIGGER

CREATE OR REPLACE TRIGGER RESTRICT_SALARY
BEFORE INSERT OR UPDATE OF SALARY ON EMPLOYEES
FOR EACH ROW
WHEN (NEW.JOB_ID = ‘SA_REP’)
BEGIN
IF INSERTING THEN
: NEW .COMMISSION_PCT:= 0;
ELSIF (:OLD.COMMISSION_PCT) IS NULL THEN
: NEW.COMMISSION_PCT:= 0;
ELSE
: NEW.COMMISSION_PCT:=:OLD.COMMISSION_PCT + 0.05;
END IF;
END;

EXPLAINATION

IF AN INSERT OPERATION IS OPERATED UPON THE EMPLOYEES TABLE WITH A JOB_ID SPECIFIED AS SA_REP AND COMMISSION_PCT WITH SOME VALUE THEN THE TRIGGER RESTRICT_SALARY WILL FIRE CAUSING A INSERTION OF ZERO IN COMMISION_PCT IN THE TABLE. (PLEASE NOTE LINE NO 7).OTHER WISE IF THE JOB IS OTHER THAN SA_REP THEN ROW IS FULLY INSERTED AS DESIRED.

IF AN UPDATE OPERATION IS OPERATED UPON THE TABLE THERE ARE TWO CASES TO BE NOTICED.

1) IF THE OLD VALUE OF COMMISSION_PCT IS A NULL THEN WHILE UPDATION OF SALARY RESTRICT_SALARY WILL FIRE AND THE COMMISSION_PCT WILL BE ASSIGNED A VALUE OF ZERO.(PLEASE SEE LINE NO 9)

2) IF THE OLD VALUE OF COMMISSION_PCT IS NOT NULL THEN RAISE_SALARY WILL FIRE AND NEW VALUE WILL BE WHAT EVER VALUE THAT HAS BEEN SPECIFIED IN THE ACTION.(PLEASE SEE LINE NO 11)
INSTEAD OF TRIGGERS

IF THE MODIFICATION IS REQUIERD TO BE DONE ON THE DATA OWNED BY AN UNUPDATEABLE VIEW I.E (A VIEW CONTAINING THE SET OPERATORS, DISTINCT CLAUSE, GROUP FUNCTIONS, OR JOINS BECOMES AN UNUPDATEABLE VIEW) THEN THE INSTEAD OF TRIGGERS ARE USED WHICH ARE FIRED BY THE ORACLE SERVER WITHOUT EXECUTING THE TRIGGERING STATEMENT OPERATING THE DML UPON THE UNDERLYING TABLES SPECIFIED.

CREATE OR REPLACE TRIGGER 
INSTEAD OF
OR OR 
ON VIEW_NAME
FOR EACH ROW


IF THE VIEW IS UPDATEABLE AND CONTAINS THE INSTEAD OF TRIGGERS THEN THESE TRIGGERS TAKE PRECEDENCE.
ALSO THE CHECK OPTIONS ARE NOT TAKEN CARE OF AT THE TIME OF FIRING OF INSTEAD OF TRIGGERS.RATHER IF WANTED THEY MUST BE SPECIFIED IN THE BODY OF INSTEAD OF TRIGGER.

SEE FOR MORE DETAILS ORACLE HAND BOOK

MANAGING TRIGGERS

WE CAN ALTER THE STATUS OF A TRIGGER BY DISABLING OR ENABLING IT.
ALTER TRIGGER DISABLE/ENABLE
FOR IN CASE OF TABLE
ALTER TABLE DISABLE/ENABLE ALL TRIGGERS
FOR RECOMPILING THE TRIGGER BODY
ALTER TRIGGER COMPILE
DROPPING A TRIGGER
DROP TRIGGER TRIGGER_NAME

CREATING DATABASE TRIGGERS

FOR CREATING A DATABASE TRIGGER FIRST THE TRIGGER CMPONENTS ARE TO BE DECIDED.

A TRIGGER DEFINED FOR A SYSTEM EVENT CAN BE AT A LEVEL OF DATABASE OR SCHEMA.FOR EXAMPLE THE LOG OFF TRIGGERS OR TRIGGERS INVOLVING DDL STATEMENTS ARE AT A LEVEL OF EITHER SCHEMA OR DATABASE. THE DATABASE SHUTDOWN TRIGGERS ARE AT A LEVEL OF SCHEMA.

TRIGGERS DEFINED AT SCHEMA LEVEL FIRES WHENEVER THE TRIGGERING EVENT INVOLVES THE SCHEMA OR TABLE.WHEREAS THE DATABASE LEVEL TRIGGERS FIRE FOR ALL USERS.

FOR DLL TRIGGERS THE POSSIBLE EVENTS MAY BE

1) CREATE STATEMENT
2) ALTER STATEMENT]
3) OR A DROP STATEMENT

ANY WAY THE CREATE TRIGGER SYNTAX WILL BE REMAINING THE SAME ALL THE TIME.
FOR A TRIGGER INVOLVED IN THE SYSYTEM EVENTS LIKE

1) AFTER SERVERERROR
2) AFTER LOGON
3) BEFORE LOGOFF
4) AFTER STARTUP
5) BEFORE SHUTDOWN

MUTATING TABLE

IT CAN BE DEFINED AS A TABLE BEING MODIFIED BY AN UPDATE, DELETE INSERT STATEMENT OR THE TABLE IS REQUIRED TO BE UPDATE BY THE EFFECTS OF ON DELETE CASCADE REFERENTIAL INTEGRITY ACTION.

A TRIGGERED TABLE IS ALSO A MUTATING ONE AS WELL AS ANY TABLE REFERENCING IT BY FOREIGN KEY CONSTRAINT.

PROTECTING DATA INTEGRITY WITH TRIGGERS

CREATE OR REPLACE CHECK_SALARY
BEFORE UPDATE OF SALARY ON EMPLOYEES
FOR EACH ROW
WHEN (NEW.SALARY)<(OLD.SALARY)
BEGIN
RAISE_APPLICATION_ERROR (-20508,’DECREASE SALARY NOT ALLOWED’)

END;

SNAPSHOT

IT IS A LOCAL COPY OF A TABLE DATATHAT ORIGINATES FROM ONE OR MORE REMOTE MASTER TABLES.THE DATA OF THE SANAPSHOT CAN BE QUERIED BUT NO DML OPERATIONS CAN BE OPERATED UPON A SNAPSHOT.TO KEEP IN PARALLEL WITH THE BASE TABLES THE SNAPSHOT SHOULD BE REFRESHED REGULARLY.

BENEFITS OF DATABASE TRIGGERS

IT PROVIDES US THE IMPROVED DATA SECURITY
IT ALSO FACILIATES IMPROVED DATA INTEGRITY.

THANK YOU

THIS DATA DOES NOT MAKE YOU MASTER IN TRIGGERS.THERE ARE SO MANY CONCEPTS I JUST TRIED MY LEVEL BEST TO CONCENTRATE ON BASICS

Trigger Solutions

Problems (For DML Triggers):

Keep the backup of department data for DELETE and UPDATE.
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;

Secure emp table from SCOTT user for DELETE or UPDATE of manager and salesman records.
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;

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):

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;

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;

 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)
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;

[
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;