Using double quotes in trigger event functions (1424)

In the 16 May quiz, we tested your knowledge of ways to restrict firing of a trigger based on the column being updated. The following choice was scored as correct:

CREATE OR REPLACE TRIGGER plch_employees_trg
   AFTER UPDATE OR INSERT
   ON plch_employees
   FOR EACH ROW
BEGIN
   IF UPDATING ('salary')
   THEN
      sys.DBMS_OUTPUT.put_line ('Updated');
   END IF;
END;
/

And this explanation was provided: "This choice relies on the UPDATING function to determine if an update is taking place on the salary column, by passing the name of the column to the function. Since I do not enclose the name in double quotes, Oracle will automatically upper-case the name of the column and so will correctly detect that in the case of the first update on last_name, no output will be displayed. As a result, "Updated" is displayed just once."

Two players wrote to note that "11gR2 does not interpret double quotes as enclosing character for the updating function, and automatically upper-case column names even if it has been defined in the table with lower case names."

I put together the following script to examine this more closely:

DROP TABLE plch_employees
/

CREATE TABLE plch_employees
(
   employee_id   INTEGER
 , last_name     VARCHAR2 (100)
 , salary        NUMBER NOT NULL
 , "comment"     VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO plch_employees
        VALUES (100
              , 'Jobs'
              , 1000000
              , NULL);

   INSERT INTO plch_employees
        VALUES (200
              , 'Ellison'
              , 1000000
              , NULL);

   COMMIT;
END;
/

CREATE OR REPLACE TRIGGER plch_employees_trg1
   AFTER UPDATE OR INSERT
   ON plch_employees
   FOR EACH ROW
BEGIN
   IF UPDATING ('comment')
   THEN
      sys.DBMS_OUTPUT.put_line ('Fired comment.');
   ELSE
      sys.DBMS_OUTPUT.put_line ('comment will not fire.');
   END IF;
END;
/

CREATE OR REPLACE TRIGGER plch_employees_trg2
   AFTER UPDATE OR INSERT
   ON plch_employees
   FOR EACH ROW
BEGIN
   IF UPDATING ('COMMENT')
   THEN
      sys.DBMS_OUTPUT.put_line ('Fired COMMENT.');
   ELSE
      sys.DBMS_OUTPUT.put_line ('COMMENT will not fire.');
   END IF;
END;
/

CREATE OR REPLACE TRIGGER plch_employees_trg3
   AFTER UPDATE OR INSERT
   ON plch_employees
   FOR EACH ROW
BEGIN
   IF UPDATING ('"comment"')
   THEN
      sys.DBMS_OUTPUT.put_line ('Fired "comment".');
   ELSE
      sys.DBMS_OUTPUT.put_line ('"comment" will not fire.');
   END IF;
END;
/

CREATE OR REPLACE TRIGGER plch_employees_trg4
   AFTER UPDATE OR INSERT
   ON plch_employees
   FOR EACH ROW
BEGIN
   IF UPDATING ('"COMMENT"')
   THEN
      sys.DBMS_OUTPUT.put_line ('Fired "COMMENT".');
   ELSE
      sys.DBMS_OUTPUT.put_line ('"COMMENT" will not fire.');
   END IF;
END;
/

BEGIN
   sys.DBMS_OUTPUT.put_line ('Update salary column');

   UPDATE plch_employees
      SET salary = 2 * salary
    WHERE employee_id = 200;

   sys.DBMS_OUTPUT.put_line ('Update comment column');

   UPDATE plch_employees
      SET "comment" = 'This is comment for emp no 200'
    WHERE employee_id = 200;

   COMMIT;
END;
/

And the output I see is:

Update salary column
"COMMENT" will not fire.
"comment" will not fire.
COMMENT will not fire.
comment will not fire.
Update comment column
"COMMENT" will not fire.
"comment" will not fire.
Fired COMMENT.
Fired comment.

Very curious. Any thoughts on this?