Oracle Triggers

Controlling When a Trigger Is Fired (BEFORE and AFTER Options)
The BEFORE or AFTER option in the CREATE TRIGGER statement specifies exactly when to fire the trigger body in relation to the triggering statement that is being run. In a CREATE TRIGGER statement, the BEFORE or AFTER option is specified just before the triggering statement. For example, the PRINT_SALARY_CHANGES trigger in the previous example is a BEFORE trigger.

In general, you use BEFORE or AFTER triggers to achieve the following results:

Use BEFORE row triggers to modify the row before the row data is written to disk.

Use AFTER row triggers to obtain, and perform operations, using the row ID.




Views that Require INSTEAD OF Triggers
A view cannot be modified by UPDATE, INSERT, or DELETE statements if the view query contains any of the following constructs:

A set operator

A DISTINCT operator

An aggregate or analytic function

A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause

A collection expression in a SELECT list

A subquery in a SELECT list

A subquery designated WITH READ ONLY

Joins, with some exceptions, as documented in Oracle Database Administrator's Guide

If a view contains pseudocolumns or expressions, then you can only update the view with an UPDATE statement that does not refer to any of the pseudocolumns or expressions.








INSTEAD OF Trigger Example

Note:

You may need to set up the following data structures for this example to work:

CREATE TABLE Project_tab (

   Prj_level NUMBER, 

   Projno    NUMBER,

   Resp_dept NUMBER);

CREATE TABLE Emp_tab (

   Empno     NUMBER NOT NULL,

   Ename     VARCHAR2(10),

   Job       VARCHAR2(9),

   Mgr       NUMBER(4),

   Hiredate  DATE,

   Sal       NUMBER(7,2),

   Comm      NUMBER(7,2),

   Deptno    NUMBER(2) NOT NULL);



CREATE TABLE Dept_tab (

   Deptno    NUMBER(2) NOT NULL,

   Dname     VARCHAR2(14),

   Loc       VARCHAR2(13),

   Mgr_no    NUMBER,

   Dept_type NUMBER);

The following example shows an INSTEAD OF trigger for inserting rows into the MANAGER_INFO view.

CREATE OR REPLACE VIEW manager_info AS

    SELECT e.ename, e.empno, d.dept_type, d.deptno, p.prj_level,

           p.projno

        FROM   Emp_tab e, Dept_tab d, Project_tab p

        WHERE  e.empno =  d.mgr_no

        AND    d.deptno = p.resp_dept;



CREATE OR REPLACE TRIGGER manager_info_insert

INSTEAD OF INSERT ON manager_info

REFERENCING NEW AS n                 -- new manager information



FOR EACH ROW

DECLARE

   rowcnt number;

BEGIN

   SELECT COUNT(*) INTO rowcnt FROM Emp_tab WHERE empno = :n.empno;

   IF rowcnt = 0  THEN

       INSERT INTO Emp_tab (empno,ename) VALUES (:n.empno, :n.ename);

   ELSE

      UPDATE Emp_tab SET Emp_tab.ename = :n.ename

         WHERE Emp_tab.empno = :n.empno;

   END IF;

   SELECT COUNT(*) INTO rowcnt FROM Dept_tab WHERE deptno = :n.deptno;

   IF rowcnt = 0 THEN

      INSERT INTO Dept_tab (deptno, dept_type) 

         VALUES(:n.deptno, :n.dept_type);

   ELSE

      UPDATE Dept_tab SET Dept_tab.dept_type = :n.dept_type

         WHERE Dept_tab.deptno = :n.deptno;

   END IF;

   SELECT COUNT(*) INTO rowcnt FROM Project_tab 

      WHERE Project_tab.projno = :n.projno;

   IF rowcnt = 0 THEN

      INSERT INTO Project_tab (projno, prj_level) 

         VALUES(:n.projno, :n.prj_level);

   ELSE

      UPDATE Project_tab SET Project_tab.prj_level = :n.prj_level

         WHERE Project_tab.projno = :n.projno;

   END IF;

END;


Example: Monitoring Logons with a Trigger 

Note:

You may need to set up data structures similar to the following for certain examples to work: 

CONNECT system/manager

GRANT ADMINISTER DATABASE TRIGGER TO scott;

CONNECT scott/tiger

CREATE TABLE audit_table (

   seq number,

   user_at  VARCHAR2(10),

   time_now DATE,

   term     VARCHAR2(10),

   job      VARCHAR2(10), 

   proc     VARCHAR2(10), 

   enum     NUMBER);

CREATE OR REPLACE PROCEDURE foo (c VARCHAR2) AS 

   BEGIN 

      INSERT INTO Audit_table (user_at) VALUES(c);

   END;

CREATE OR REPLACE TRIGGER logontrig AFTER LOGON ON DATABASE

-- Just call an existing procedure. The ORA_LOGIN_USER is a function

-- that returns information about the event that fired the trigger.

CALL foo (ora_login_user)