Re: Help with Triggers

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 22 Aug 2006 12:17:43 -0700
Message-ID: <1156274262.69503@bubbleator.drizzle.com>

FM wrote:

> Also, the 10gR2 documentation indicates that :
>
> Restrictions on AFTER Triggers AFTER triggers are subject to the
> following restrictions:
> You cannot write either the :OLD or the :NEW value.
>
> So you should change the trigger to a BEFORE trigger.
>
> FM

Where in the documentation does it say that?

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Aug 22 12:15:36 2006

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> CREATE TABLE person (

2 fname VARCHAR2(15),
3 lname VARCHAR2(15));

Table created.

SQL>
SQL> CREATE TABLE audit_log (

   2  o_fname    VARCHAR2(15),
   3  o_lname    VARCHAR2(15),
   4  n_fname    VARCHAR2(15),
   5  n_lname    VARCHAR2(15),

6 chng_by VARCHAR2(10),
7 chng_when DATE);

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER referencing_clause

   2 AFTER UPDATE
   3 ON person
   4 REFERENCING NEW AS NEW OLD AS OLD
   5 FOR EACH ROW
   6
   7 DECLARE
   8 act VARCHAR2(1);
   9 BEGIN
  10 INSERT INTO audit_log
  11 (o_fname, o_lname, n_fname, n_lname, chng_by, chng_when)   12 VALUES
  13 (:OLD.fname, :OLD.lname, :NEW.fname, :NEW.lname, USER, SYSDATE);   14 END referencing_clause;
  15 /

Trigger created.

SQL> INSERT INTO person (fname, lname) VALUES ('Dan', 'Morgan');

1 row created.

SQL> UPDATE person

2 SET lname = 'Cline';

1 row updated.

SQL>
SQL> SELECT * FROM person;

FNAME LNAME
--------------- ---------------
Dan Cline

SQL> SELECT * FROM audit_log;

O_FNAME O_LNAME N_FNAME N_LNAME CHNG_BY --------------- --------------- --------------- --------------- ---------- CHNG_WHEN

Dan Morgan Dan Cline UWCLASS 22-AUG-06 SQL> Because it is not correct. Nor do I think it was correct in 8i or 9i and possibly long before that.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Aug 22 2006 - 14:17:43 CDT