Compound Triggers Seriously, I don't like triggers. But if you have to maintain them, you might as well make the best of it.

While trying to figure out my problem the other day, I ran across Compound Triggers. I hadn't read about it in the 11g New Features guide, but since I don't use triggers, I wasn't sure if it was new or not. Apprently it is...

In essence, you can combine multiple triggers into one. I won't go into the gory details (because I don't know the gory details), but I will provide the example from the docs for your perusal.

CREATE TRIGGER compound_trigger
  FOR UPDATE OF sal ON emp
    COMPOUND TRIGGER

  -- Declaration Section
  -- Variables declared here have firing-statement duration.
  threshold CONSTANT SIMPLE_INTEGER := 200;

  BEFORE STATEMENT IS
  BEGIN
    ...
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  BEGIN
    ...
  END BEFORE EACH ROW;

  AFTER EACH ROW IS
  BEGIN
    ...
  END AFTER EACH ROW;
END compound_trigger;
/

At the very minimum (if you have to use them), you might as well combine them into one and save a bit on maintenance/debugging!

Labels: database, oracle, triggers

  ¶ 7/02/2008 09:19:00 PM 1 Comments Links to this post

  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?

Labels: oracle, plsql, triggers

  ¶ 6/30/2008 08:55:00 PM 4 Comments Links to this post