Go Back

PLSQL 7

PL/SQL -> PLSQLBasics

INTRODUCTION TO PL/SQL 6:

PLSQL Triggers(DML):

TRIGGERS (DML Manipulations:):

A TRIGGER is a PL/SQL block that executes implicitly whenever a particular event takes place. The specified event is associated with either a table, a view, a schema, or the database, and it is one of the following:

1. Database manipulation (DML) statement (DELETE, INSERT, or UPDATE)

2. Database definition (DDL) statement (CREATE, ALTER, or DROP)

3. Database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)

CREATING TRIGGERS:

When creating triggers we need to focous mainly on the Trigger Timing, Trigger Event, Trigger Type.

TRIGGER TIMING:

FOR TABLE : BEFORE,AFTER

FOR VIEW : INSTEAD OF.

TRIGGERING EVENT: INSERT, UPDATE OR DELETE.

TABLE NAME: ON TABLE OR VIEW.

TRIGGER TYPE : ROW OR STATEMENT.

WHEN CLAUSE : RESTRICTING CONDITION

TRIGGER BODY : PL/SQL BLOCK.

TRIGGER COMPONENTS:

TRIGGER TIMING: when a should the trigger fire?

BEFORE: EXECUTE the TRIGGER body BEFORE the triggering dml event on a table.

AFTER: EXECUTE the TRIGGER body AFTER the triggering dml event on a table.

INSTEAD OF: EXECUTE the TRIGGER body INSTEAD of the triggering statement. Used for views that are not otherwise modifiable.

TRIGGER TYPE:

STATEMENT: The TRIGGER body executes once for the triggering event. this is the default.

ROW: The TRIGGER body executes once FOR EACH ROW affected by the triggering event.

What is the action that should trigger perform?

The TRIGGER BODY is a PL/SQL BLOCK or a call to a procedure.
When the triggering data manipulation statement affects many rows, the statement trigger fires exactly once, and the row trigger fires once for every row affected by statement.

CREATING STATEMENT TRIGGERS:

 
CREATE OR replace TRIGGER business_day_emp 
  BEFORE INSERT ON emp 
BEGIN 
    IF ( To_char(SYSDATE, 'DY') IN ( 'SAT', 'SUN' ) ) 
        OR ( To_char(SYSDATE, 'HH24') NOT BETWEEN '08' AND '18' ) THEN 
      Raise_application_error (-20500, 'YOU MAY ONLY INSERT INTO EMP DURING BUSINESS HOURS'); 
    END IF; 
END; 
/

Trigger created.

INSERT INTO EMP during non office hours.

INSERT INTO emp 
            (empno, 
             ename, 
             deptno) 
     VALUES (1, 
             'RAJESH', 
             40); 
INSERT INTO EMP
            *
ERROR at line 1:
ORA-20500: YOU MAY ONLY INSERT INTO EMP DURING BUSINESS HOURS 
ORA-06512: at "SCOTT.BUSINESS_DAY_EMP", line 4 
ORA-04088: error during execution of trigger 'SCOTT.BUSINESS_DAY_EMP' 


INSERT INTO EMP DURING OFFICE HOURS.

INSERT INTO emp 
            (empno, 
             ename, 
             deptno) 
     VALUES (1, 
             'RAJESH', 
             40); 
1 row created.
             

USING CONDITIONAL PREDICATES:

 
CREATE OR replace TRIGGER business_day_emp1 
  BEFORE INSERT OR UPDATE OR DELETE ON emp 
BEGIN 
    IF ( To_char(SYSDATE, 'DY') IN ( 'SAT', 'SUN' ) ) 
        OR ( To_char(SYSDATE, 'HH24') NOT BETWEEN '08' AND '18' ) THEN 
      IF deleting THEN 
        Raise_application_error (-20502, 
                         'YOU MAY ONLY DELETE FROM  EMP DURING BUSINESS HOURS'); 
      ELSIF inserting THEN 
        Raise_application_error (-20500, 
        'YOU MAY ONLY INSERT INTO EMP DURING BUSINESS HOURS' 
        ); 
      ELSIF Updating ('SAL') THEN 
        Raise_application_error (-20503, 
        'YOU MAY ONLY UPDATE SAL IN EMP DURING BUSINESS HOURS' 
        ); 
      ELSE 
        Raise_application_error (-20504, 
             'YOU MAY ONLY UPDATE  EMP DURING BUSINESS HOURS'); 
      END IF; 
    END IF; 
END; 
/
Trigger created.

Creating ROW LEVEL TRIGGERS using OLD AND NEW qualifiers:

 
CREATE TABLE dept1 AS 
  SELECT * 
    FROM dept 
    /
Table created.    

CREATE TABLE audit_dept_table 
  ( 
       username VARCHAR2(10), 
      todaydate TIMESTAMP, 
     new_deptno NUMBER(10), 
     old_deptno NUMBER(10), 
      new_dname VARCHAR2(10), 
      old_dname VARCHAR2(10), 
        new_loc VARCHAR2(10), 
        old_loc VARCHAR2(10) 
  ) 
  /
  Table created.
  
  CREATE OR replace TRIGGER audit_dept_values 
  AFTER DELETE OR INSERT OR UPDATE ON dept1 
  FOR EACH ROW 
BEGIN 
    INSERT INTO audit_dept_table 
         VALUES (USER, 
                 systimestamp, 
                 :NEW.deptno, 
                 :OLD.deptno, 
                 :NEW.dname, 
                 :OLD.dname, 
                 :NEW.loc, 
                 :OLD.loc); 
END; 
/
Trigger created.

DATA OPERATION:	OLD VALUE:	         NEW VALUE:
INSERT	         NULL	               INSERTED VALUE
UPDATE            VALUE BEFORE UPDATE	VALUE AFTER UPDATE
DELETE 	         VALUE BEFORE DELETE	NULL

The OLD AND NEW qualifiers are only available in row triggers.

PREFIX these qualifiers with a colon(:) in every SQL and PL/SQL statement.

DELETING:

 
DELETE FROM dept1 
 WHERE deptno = 10; 

1 row deleted.

SQL> SELECT * FROM AUDIT_DEPT_TABLE;

USERNAME   TIMESTAMP NEW_DEPTNO OLD_DEPTNO NEW_DNAME  OLD_DNAME  NEW_LOC    OLD_LOC                                               
---------- --------- ---------- ---------- ---------- ---------- ---------- ----------                                            
SCOTT      09-JUN-05                    		10            			ACCOUNTING            NEW YORK

INSERTING:

 
INSERT INTO dept1 
     VALUES (50, 
             'COMPUTERS', 
             'NEW YORK'); 

1 row created.

SQL> SELECT * FROM AUDIT_DEPT_TABLE;

Answer...

UPDATING with WHERE clause:

 
UPDATE dept1 
   SET dname = 'RAJESH' 
 WHERE deptno = 50; 

1 row updated.

SQL> SELECT * FROM AUDIT_DEPT_TABLE;
Answer...
           

Updating WITHOUT where CLAUSE:

 
UPDATE dept1 
   SET deptno = 1; 

4 rows updated.


SQL> SELECT * FROM AUDIT_DEPT_TABLE;

Answer...   

PROCEDURE VS TRIGGERS:

           
TRIGGER:	
-------
Defined with create TRIGGER.	
Data dictionary contains source code.	
Implicitly invoked.	
COMMIT, SAVEPOINT, ROLL BACK are not allowed.	
 
PROCEDURE:
---------
Defined with create PROCEDURE.
Data dictionary contains source code.
Explictly invoked. 
COMMIT, SAVEPOINT, ROLL BACK are allowed.

USER_TRIGGERS for source code:

 
SQL> DESC USER_TRIGGERS;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 TRIGGER_NAME                                                                     VARCHAR2(30)
 TRIGGER_TYPE                                                                     VARCHAR2(16)
 TRIGGERING_EVENT                                                                 VARCHAR2(227)
 TABLE_OWNER                                                                      VARCHAR2(30)
 BASE_OBJECT_TYPE                                                                 VARCHAR2(16)
 TABLE_NAME                                                                       VARCHAR2(30)
 COLUMN_NAME                                                                      VARCHAR2(4000)
 REFERENCING_NAMES                                                                VARCHAR2(128)
 WHEN_CLAUSE                                                                      VARCHAR2(4000)
 STATUS                                                                           VARCHAR2(8)
 DESCRIPTION                                                                      VARCHAR2(4000)
 ACTION_TYPE                                                                      VARCHAR2(11)
 TRIGGER_BODY                                                                     LONG


SQL> SELECT TRIGGER_NAME,TRIGGER_BODY FROM USER_TRIGGERS;

TRIGGER_NAME                   TRIGGER_BODY                                                                                       
------------------------------ --------------------------------------------------------------------------------                   
BUSINESS_DAY_EMP                     BEGIN                                                                                              
                                 IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR                                                   
                                 (TO_CHAR(SYSDATE,'HH24'                                                                          
                                                                                                                               

SELECTTRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,
TRIGGER_BODY FROM   USER_TRIGGERS;

TRIGGER_NAME                   TRIGGER_TYPE                                                                                       
------------------------------ ----------------                                                                                   
TRIGGERING_EVENT                                                                                                                  
----------------------------------------------------------------------------------------------------------------------------------
TRIGGER_BODY                                                                                                                      
--------------------------------------------------------------------------------                                                  
BUSINESS_DAY_EMP                     BEFORE STATEMENT                                                                                   
INSERT                                                                                                                            
BEGIN                                                                                                                             
  IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR                                                                                  
  (TO_CHAR(SYSDATE,'HH24'                                                                                                         
                                                                               
DISABLE OR REENABLE A CL DATABASE TRIGGER:
ALTER TRIGGER TRIGGER_NAME DISABLE/ENABLE.

DISABLE OR REENABLE ALL TRIGGERS FOR A TABLE:
ALTER TABLE TABLE_NAME DISABLE/ENABLE ALL TRIGGERS.

RECOMIPLE A TRIGGER FOR A TABLE:
ALTER TRIGGER TRIGGER_NAME COMPILE.

Go Back

Go Back