trap, in a table trigger, the name of the column being updated
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
A Solution Using DBMS_STANDARD.UPDATING Overload 2
Many years ago, two decades to be a bit more exact, I learned to write table triggers and learned how to trap multiple trigger events such as the line, below, highlighted in purple, and how to use IF INSERTING, IF UPDATING, and IF DELETING to isolate the code that acts when the trigger fires.

That was great as far as it went but when I wanted to know if a column value had been changed I often found myself writing code like this:
conn uwclass/uwclass

CREATE TABLE t (
col1 VARCHAR2(10),
col2 VARCHAR2(10),
col3 VARCHAR2(10));

CREATE OR REPLACE TRIGGER updating_demo
BEFORE INSERT OR UPDATE OR DELETE
ON t
FOR EACH ROW
BEGIN
  IF updating THEN
    IF :old.col2 <> :new.col2 THEN
      dbms_output.put_line('Column 2 Is Being Updated');
    END IF;
  END IF;
END updating_demo;
/

set serveroutput on

INSERT INTO t VALUES ('A', 'B', 'C');
UPDATE t SET col1 = 'Test';
UPDATE t SET col2 = 'Test';
UPDATE t SET col3 = 'Test';
Which was fine as far as it went but was a lot more verbose than it seems I needed to be. Take a look at the following trigger and try it.
CREATE OR REPLACE TRIGGER updating_demo
BEFORE INSERT OR UPDATE OR DELETE
ON t
FOR EACH ROW
BEGIN
  IF
dbms_standard.updating('COL2') THEN
    dbms_output.put_line('Updating Column 2');
  END IF;
END updating_demo;
/

DELETE FROM t;
INSERT INTO t VALUES ('A', 'B', 'C');
UPDATE t SET col1 = 'Test';
UPDATE t SET col2 = 'Test';
UPDATE t SET col3 = 'Test';
The only significant difference is that the second cares about whether you are updating but not if the value is being altered. The first only cares if the value is actually being altered.

But if you don't want to explicitly name DBMS_STANDARD you can do it this way too:
CREATE OR REPLACE TRIGGER updating_demo
BEFORE INSERT OR UPDATE OR DELETE
ON t
FOR EACH ROW
BEGIN
  IF
updating('COL2') THEN
    dbms_output.put_line('Updating Column 2');
  END IF;
END updating_demo;
/

set serveroutput on

DELETE FROM t;
INSERT INTO t VALUES ('A', 'B', 'C');
UPDATE t SET col1 = 'Test';
UPDATE t SET col2 = 'Test';
UPDATE t SET col3 = 'Test';
You can't get much less painful than this.
 
Related Topics
DBMS_STANDARD
Table Triggers
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved