|
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. |
