TRIGGERS

TRIGGERS

Triggers are PL/SQL blocks like functions, procedures, packages. Like procedures and functions triggers are also stored within the database. Like packages they doesn’t receive any arguments and they can’t be used as local blocks. Procedures and functions must be called externally by the user. But triggers are automatically executed depending on triggering event. Triggering event are the DML operations and executing a trigger is called as trigger firing.
Triggers are mostly used for the following purposes.
To define the complex constraints those are not possible to define at the time of table creation.
To record the auditing information of table i.e. changes made to the table and who made those changes.
To give instructions to other programs depending on the situtation.

CREATING A TRIGGER :
Create trigger is used to create a trigger and it has the following syntax.
à Syntax : CREATE OR REPLACE TRIGGER TNAME
BEFORE/AFTER INSERT OR UPDATE OR DELETE
ON TABNAME
[ FOR EACH ROW ]
[ WHEN CONDITION ]
DECLARE
VAR DECLARATION
BEGIN
EXECUTABLE STATEMENTS;
EXCEPTION
ERROR HANDLING ROUTINES;
END TNAME;

Second line of the syntax specifies the triggering event when the trigger has to fire. This includes BEFORE / AFTER clause and one or more DML operations. For example if you write triggering event as BEFORE INSERT. Then the trigger is fired before inserting a row into the table.
Third line of syntax specifies the table name on which trigger has to create.
For EACH ROW clause specifies that the trigger is fired for each row which is affected by the DML operations. If we exclude EACH ROW clause then the trigger is fired only once for all rows that are affected by the DML operation.
WHEN CONDITION clause specifies a condition when trigger has to fire during the triggering event and is optional.

TYPES OF TRIGGERS :

Triggers are classified into row level triggers and statement level triggers depending on the number of times trigger executes during the triggering event. The triggers that are executed once for each row affected by the DML operations are called as ROW LEVEL TRIGGERS. The triggers that are executed onlyonce for all rows affected by the DML operation are called as STATEMENT LEVEL TRIGGERS.
Depending on the triggering event triggers are classified into fourteen types.

BEFORE INSERT - ROW LEVEL
BEFORE UPDATE - ROW LEVEL
BEFORE DELETE - ROW LEVEL
BEFORE INSERT - STATEMENT LEVEL
BEFORE UPDATE - STATEMENT LEVEL
BEFORE DELETE - STATEMENT LEVEL
AFTER INSERT - ROW LEVEL
AFTER UPDATE - ROW LEVEL
AFTER DELETE - ROW LEVEL
AFTER INSERT - STATEMENT LEVEL
AFTER UPDATE - STATEMENT LEVEL
AFTER DELETE - STATEMENT LEVEL
INSTEAD OF ( ORACLE 8.0 ) - ROW LEVEL
INSTEAD OF - STATEMENT LEVEL

Write a trigger that restricts the user from making any DML operations on SUNDAY.
CREATE OR REPLACE TRIGGER NOTRANS
BEFORE UPDATE OR DELETE OR INSERT
ON EMP
DECLARE
W VARCHAR2(10) := TO_CHAR( SYSDATE, ‘DY’ );
BEGIN
IF W = ‘SUN’ THEN
RAISE_APPLICATION_ERROR( -20000, ‘ NO TRANSACTION IS ALLOWED ON SUNDAY’ );
END IF;
END NOTRANS;

BIND VARIABLES OR PSEUDO RECORDS :
Two pseudo records :OLD and :NEW can be used with row level triggers. :OLD is used to access the values of a record before it is updated and :NEW is used to access the values of a record after update.
:OLD and:NEW are worked with DML operations as follows.

INSERT
UPDATE
DELETE
:OLD
NOT POSSIBLE
POSSIBLE
POSSIBLE
:NEW
POSSIBLE
POSSIBLE
NOT POSSIBLE

Write a trigger to record the auditing information of DEPT table.

CREATE OR REPLACE TRIGGER BEFUPD
BEFORE UPDATE ON DEPT
FOR EACH ROW
BEGIN
INSERT INTO AUDITDEPT VALUES ( :NEW.DEPTNO, :NEW.DNAME,
:NEW.LOC, :OLD.DEPTNO, :OLD.DNAME, :OLD.LOC,
‘ UPDATED BY ’ USER ‘ ON ’ TO_CHAR (SYSDATE, ‘DD-MON-YY
HH.MI.SS.AM’ );
END;

CREATE OR REPLACE TRIGGER BFINSERT
BEFORE INSERT ON DEPT
FOR EACH ROW
BEGIN
INSERT INTO AUDITDEPT
( DEPTNO, DNAME, LOC, REMARKS ) VALUES ( :NEW.DEPTNO, :NEWDNAME, :NEW.LOC, ‘INSERTED BY’ USER ‘ON’ TO_CHAR ( SYSDATE, ‘DD-MON-YYHH.MI.SSAM ’ );
END BFINSERT;

CREATE OR REPLACE TRIGGER BDELETE
BEFORE DELETE
ON EMP
FOR EACH ROW
BEGIN
INSERT INTO AUDITDEPT( ODEPTNO, ODNAME, OLOC ) VALUES
( :OLD.DEPTNO, :OLD.DNAME, :OLD.LOC, ‘DELETED BY’ USER ‘ON’ TO_CHAR( SYSDATE, ‘DD-MON-YY HH-MI-SS’ ));
END BDELETE;

TRIGGERING PREDICATES :
When a trigger is written with more than one triggering event then we can identify the triggering event based on which the trigger was fired by using the the triggering predicates. Triggering predicates are as follws.
INSERTING.
DELETING.
UPDATING.

CREATE OR REPLACE TRIGGER AUDITDEPT
BEFORE INSERT OR UPDATE OR DELETE
ON DEPT
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO AUDITDEPT ( DEPTNO, DNAME, LOC, REMARKS ) VALUES
( :NEW.DEPTNO, :NEW.DNAME, :NEW.LOC, ‘INSERTED BY’ USER ‘ON’ TO_CHAR(SYSDATE, ‘DD-MON-YY HH.MI.SS AM’ ));
ELSIF UPDATING THEN
INSERT INTO AUDITDEPT VALUES ( :NEW.DEPTNO, :NEW.DNAME, :NEW.LOC, :OLD.DEPTNO, :OLD.DNAME, :OLD.LOC, ‘UPDATED BY’ USER ‘ON’ TO_CHAR( SYSDATE, ‘DD-MON-YY HH.MM.SS’ ));
ELSE
INSERT INTO AUDITDEPT ( ODEPTNO, ODNAME, OLOC, REMARKS ) VALUES
( :OLD.DEPTNO, :OLD.DNAME, :OLD.LOC, ‘DELETED BY’ USER ‘ON’ TO_CHAR( SYSDATE, ‘DD-MON-YY HH.MM.SS AM’ ));
END IF;
END;

Write a trigger that restricts the user from inserting a row into EMP table if the commission is more than 30% of the salary.

CREATE OR REPLACE TRIGGER COMMISSION
BEFORE INSERT OR UPDATE
ON EMP
FOR EACH ROW
BEGIN
IF :NEW.COMM >= (:NEW.SAL*30/100) THEN
RAISE_APPLICATION_ERROR( -20002, ‘COMM MUST BE LESS THAN 30% OF THE SALARY’ );
END IF;
END;

INSTEAD OF TRIGGERS : Instead of triggers are used to perform an action against a table in stead of the original operation performed by the user.

CREATE OR REPLACE VIEW EMPDEPT
AS
SELECT EMPNO, ENAME, JOB, HIREDATE, D.DEPTNO, DNAME, LOC FROM
EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

CREATE OR REPLACE TRIGGER INSTEAD
INSTEAD OF INSERT
ON EMPDEPT
FOR EACH ROW
BEGIN
INSERT INTO EMP VALUES ( :NEW.ENAME, :NEW.EMPNO, :NEW.LOC, :NEW.MGR, :NEW.HIREDATE, :NEW.SAL, :NEW.COMM, :NEW.DEPTNO );
END INSTEAD;

DATA DICTIONARY VIEWS ON TRIGGERS :
A list of triggers, event of those triggers the table name on which the trigger was created etc. can be get from the Data Dictionary View.
USER_TRIGGERS :
SEELCT * FROM USER_TRIGGERS;
DISABLE / ENABLE THE TRIGGERS : Wecan disable or enable the trigger by using the follwing syntax.
à ALTER TRIGGER TNAME ENABLE / DISABLE;
Example : Disable the trigger instins.
SQL> ALTER TRIGGER INSTINS;
We can also enable or disable all triggers written on a table at a time by using the follwing syntax.
SQL> ALTER TABLE TABAME DISABLE / ENABLE ALL TRIGGERS;
Example : The following statement disables all triggers on EMP table.
SQL> ALTER TABLE EMP DISABLE ALL TRIGGERS;

DELETING A TRIGGER :
SYNTAX : SQL> DROP TRIGGER TNAME;

ORDER OF TRIGGER FIRING :
BEFORE STATEMENT LEVEL
BEFORE ROW LEVEL
AFTER ROW LEVEL
AFTER STATEMENT LEVEL
RESTRICTIONS ON TRIGGERS :
1.A trigger may not issue any transaction control statements, COMMIT, ROLLBACK or SAVEPOINT.
2.Any procedures or functions that are called by the trigger body can not issue any transaction control statements.
3.The trigger body can not declare any LONG or RAW variables also :NEW and :OLD can not refer to a LONG or a LONGRAW column in the table for which the trigger is defines.

Triggers in forms are fired depending on the user made action on them. The difference between trigger in forms and database triggers is the database triggers are fired depending on the DML events, while triggers in forms are fired depending on the user made action against the objects triggers in foms are classiffied into the following seven types.
1. Interface Event Triggers.
2. Mouse Event Triggers
3. Transactional Triggers
4. Query Processing Triggers
5. Navigational Triggers
6. New Object Triggers
7. Validation Triggers

1. INTERFACE EVENT TRIGGERS : The triggers that are fired, based on the user made action on the interface of the items are called as Interface Event Triggers.

a) WHEN_BUTTON_PRESSED : This trigger will be fired whenever user clicks on a push button.
b) WHEN_CHECKBOX_CHECKED : This trigger will be fired whenever user clicks on a checkbox at runtime.
c) WHEN_RADIO_CHANGED : This trigger will be fired whenever user selects a new radio button within the radio group.
d) WHEN_LIST_CHANGED : This trigger will be fired whenever user selects a new element within a list item.
e) WHEN_LIST_ACTIVATED : This trigger will be fired whenever user activates a list item and this trigger is fired only for the list items of style Tlist.
f) WHEN_IMAGE_PRESSED : This trigger will be fired whenever user clicks on an image item.
g) WHEN_IMAGE_ACTIVATED : This trigger will be fired whenever user double clicks an image item.
h) WHEN_TIMER_EXPIRED : This trigger will be fired whenever the user created timer expires.

2. MOUSE EVENT TRIGGERS : The triggers that are fired based on the user made action on the items using mouse are called as Mouse Event Triggers.

a) WHEN_MOUSE_ENTER : This trigger will be fired whenever the mouse pointer enters into an item.
b) WHEN_MOUSE_LEAVE : This trigger will be fired whenever the mouse pointer leaves an item.
c) WHEN_MOUSE_MOVE : This trigger will be fired whenever the mouse pointer is moved over an item.
d) WHEN_MOUSE_DOWN : This trigger will be fired whenever user pressed the mouse button.
e) WHEN_MOUSE_UP : This trigger will be fired whenever user releases the mouse button.
f) WHEN_MOUSE_CLICK : This trigger will be fired whenever user clicks on an item.
g) WHEN_MOUSE_DOUBLECLICK : This trigger will be fired whenever user double clicks on an item.

3. TRANSACTIONAL TRIGGERS : The triggers that are fired depending on the DML operation performed by the user are called as Transactional Triggers.

a) PRE_INSERT : This trigger will be fired just before inserting a record into the database.
b) POST_INSERT : This trigger will be fired just after a record is inserted into the database.
c) ON_INSERT : This trigger will be fired to replace the default behaviour of forms for insertion.
d) PRE_UPDATE : This trigger will be fired just before updating a record to the database.
e) POST_UPDATE : This trigger will be fired just after updating a record to the database.
f) ON_UPDATE : This trigger will be fired to replace the default behaviour of forms for updation.
g) PRE_DELETE : This trigger will be fired just before deleting a record from the database.
h) POST_DELETE : This trigger will be fired just after deleting a record from the database.
i) ON_DELETE : This trigger will be fired to replace the default behaviour of forms for deletion.

4. QUERY PROCESSING TRIGGERS : The triggers that are fired based on the query execution process are called as Query Processing Triggers.

a) PRE_QUERY : This trigger will be fired just before executing a query against the database.
b) POST_QUERY : This trigger will be fired once for each row retrieved from the database.

5. NAVIGATIONAL TRIGGERS :

a) PRE_FORM : This trigger will be fired just before the focus enters into the first navigational item of first navigation datablock.
b) POST_FORM : This trigger will be fired whenever the navigation control leaves the form.
c) PRE_BLOCK : This trigger will be fired whenever a block is displayed for the first time.
d) POST_BLOCK : This trigger will be fired whenever the focus transfers to a new data block.
e) PRE_TEXT_ITEM : This trigger will be fired whenever the focus reaches a text item.
f) POST_TEXT_ITEM : This trigger will be fired whenever the focus leaves the text item.

6. NEW OBJECT TRIGGERS :

a) WHEN_NEW_FORM_INSTANCE : This trigger will be fired before a form is displayed for the first time.
b) WHEN_NEW_BLOCK_INSTANCE : This trigger will be fired just before a block is displayed for the first time.
c) WHEN_NEW_ITEM_INSTANCE : This trigger will be fired whenever the focus reaches an item other than the current item.
d) WHEN_NEW_RECORD_INSTANCE : This trigger will be fired whenever the focus reaches a record other than the current record.

7. VALIDATION TRIGGERS :

a) WHEN_VALDIATE_ITEM : This trigger will be fired whenever user is trying to leave an item.
b) WHEN_VALIDATE_RECORD : This trigger will be fired whenever user is trying to leave a record.
c) WHEN_VALIDATE_BLOCK : This trigger will be fired whenever user is trying to leave a block.
d) WHEN_VALIDATE_FORM : This trigger will be fired whenever user is trying to leave a form.

BUILT-INS : Built-ins are nothing but the pre-defined functions and procedures within forms. For example the following built-ins are used to perform the basic operations in forms runtime.
1. EXECUTE_QUERY ( NO_VALIDATE ) : This built-in is used to execute the select statement against the database and request the first record.

2. ENTER_QUERY : This built-in is used to take the block into enter query mode.
3. FIRST_RECORD : This built-in is used to display the first record of the current datablock.
4. NEXT_RECORD : This built-in is used to display the next record of the current record in the current datablock.
5. PREVIOUS_RECORD : This built-in is used to display the previous record of the current record in the current datablock.
6. LAST_RECORD : This built-in is used to display the last record of the current datablcok.
7. CREATE_RECORD : This built-in is used to create a new record for the current datablock.
8. DELETE_RECORD : This built-in is used to delete the current record of the current datablock.
9. COMMIT / COMMIT_FORM : This built-in is used to save the changes of the current datablock or all datablocks of a form module to the database.
10. EXIT_FORM : This built-in is used to close the forms at runtime.