Fun With Triggers

I don't care a whole lot for triggers. About the only thing I see of use is for some sort of auditing.

That said, I don't know a whole lot about them either. I don't know the specifics of how they work. I was tested today...

CREATE TABLE t
(
  update_date DATE DEFAULT SYSDATE,
  update_user VARCHAR2(30)
);

INSERT INTO t ( update_date ) 
VALUES ( ADD_MONTHS( SYSDATE, 100 ) );

INSERT INTO t ( update_date ) 
VALUES ( ADD_MONTHS( SYSDATE, -22 ) );

CJUSTICE@XE>SELECT * FROM T;

UPDATE_DA UPDATE_USER
--------- ------------------------------
31-OCT-16
31-AUG-06

Easy enough.

My requirement is that I capture the update_user, specifically in the case where it is not supplied. And this is where I ran into a wall.

CREATE OR REPLACE
TRIGGER biu_t
  BEFORE INSERT OR UPDATE
  ON t
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
BEGIN
  IF :NEW.update_user IS NULL THEN
    :NEW.update_user := USER;
  END IF;
END;
/
show errors

Easy enough.

UPDATE t 
  SET update_date = ADD_MONTHS( SYSDATE, -12 );

CJUSTICE@XE>SELECT * FROM T;

UPDATE_DA UPDATE_USER
--------- ------------------------------
30-JUN-07 CJUSTICE
30-JUN-07 CJUSTICE

2 rows selected.

OK, not too bad. Let's try specifying the UPDATE_USER.

UPDATE t
  SET update_date = ADD_MONTHS( SYSDATE, 12 ),
      update_user = 'BOLLOCKS';

UPDATE_DA UPDATE_USER
--------- ------------------------------
30-JUN-09 BOLLOCKS
30-JUN-09 BOLLOCKS

Good, everything's working as expected...or is it? Let's connect as another user:

CJUSTICE@XE>conn hr/testing@xe
Connected.

UPDATE cjustice.t SET update_date = SYSDATE - 100;

HR@XE>SELECT * FROM cjustice.t;

UPDATE_DA UPDATE_USER
--------- ------------------------------
22-MAR-08 BOLLOCKS
22-MAR-08 BOLLOCKS

What? Why didn't the user get updated with HR? Let's add some dbms_output statements to the trigger:

CREATE OR REPLACE
TRIGGER biu_t
  BEFORE INSERT OR UPDATE
  ON t
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
BEGIN
  dbms_output.put_line( 'New Value:  ' || :NEW.update_user );
  dbms_output.put_line( 'Old Value:  ' || :OLD.update_user );

  IF :NEW.update_user IS NULL THEN
    :NEW.update_user := USER;
  END IF;
END;
/
show errors

HR@XE>UPDATE cjustice.t SET update_date = SYSDATE + 100;
New Value:  BOLLOCKS
Old Value:  BOLLOCKS
New Value:  BOLLOCKS
Old Value:  BOLLOCKS

2 rows updated.

Elapsed: 00:00:00.04
HR@XE>SELECT * FROM cjustice.t;

UPDATE_DA UPDATE_USER
--------- ------------------------------
08-OCT-08 BOLLOCKS
08-OCT-08 BOLLOCKS

2 rows selected.

So UPDATE_USER was not updated with the value of HR, which I would expect. The :NEW and :OLD values are exactly the same...I'm guessing that Oracle makes a copy of the record first (puts it into a collection or something?) so UPDATE_USER would never be NULL.

Ultimately, I scrapped the update of UPDATE_USER in the trigger. I did force the UPDATE_DATE = SYSDATE, and that was it.

Ideally, I would specify that in the package call, but we aren't there yet. In my opinion, no other user would have UPDATE, INSERT or DELETE privileges on the table. But that's what I have.

Can someone with more experience with triggers help me out? Or advise me on what's going on?