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 tEasy enough.
(
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
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 REPLACEEasy enough.
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
UPDATE tOK, not too bad. Let's try specifying the UPDATE_USER.
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.
UPDATE tGood, everything's working as expected...or is it? Let's connect as another user:
SET update_date = ADD_MONTHS( SYSDATE, 12 ),
update_user = 'BOLLOCKS';
UPDATE_DA UPDATE_USER
--------- ------------------------------
30-JUN-09 BOLLOCKS
30-JUN-09 BOLLOCKS
CJUSTICE@XE>conn hr/testing@xeWhat? Why didn't the user get updated with HR? Let's add some dbms_output statements to the trigger:
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
CREATE OR REPLACESo 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.
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.
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?
