Monday, June 30, 2008

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?

6 comments:

Doug Burns said...

When you are connected as HR and update T, what is the new value of update_user going to be, *forgetting* the trigger? Because you don't specify the value for update_user in the update statement, then the new value will also equal the old value (as your dbms_output shows). What else could :new.update_user be? I suppose you could say that it should be NULL, because you're not specifying it, but that's not the value that would be in the new row after the update.

So you would need to do something like this (haven't tested it - no db to hand at the moment)

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
OR USER != :OLD.update_user
THEN
:NEW.update_user := USER;
END IF;
END;
/

Or you could use UPDATING and INSERTING to differentiate between the two different cases you're addressing.

HTH

Roel said...

You are just updating 1 column (update_date), so update_user just kept the value it had before. Completely logical and correct. If you would like the trigger to set the user value you should set the update_user to NULL....

oraclenerd said...

Doug,

Yes, it makes sense that the column would be populated. I got myself twisted into knots trying to figure out what was going on. Maybe the copying of the row should happen after the check for the UPDATE? I have no idea...I try to use them as infrequently as possible.

Thanks!

chet

oraclenerd said...

@roel

Logic doesn't play well with me sometimes! ;)

After having a chance to step back though, I understand that's the way it is.

thanks...

chet

Don Kleppinger said...

I'm having the same problem. This is how I want it to work. If a value is passed in, use the value. If a value is not passed in, use the logged in user name. My problem is I can't tell if a value was passed in because new.update_user is never null.

chet justice said...

@don

Man, it's been awhile since I've had to think about this one. :)

Not sure what can be done about it. Since "update_user" was specified in the DML statement, you would *think* that it would be empty/NULL. Apparently not.

If the option is there, make DML to the table restricted to a package/procedure call so you get to determine what goes in there.