17 May 2011

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?

7 comments:

  1. This is a quote from your "Oracle PL/SQL Programming":
    "The UPDATING function is overloaded with a version that takes a specific column name as an argument. This is handy for isolating specific column updates. Specification of the column name is not case-sensitive."

    ReplyDelete
  2. I'm glad somebody reads that book. It's too big and heavy for me. :-)

    ReplyDelete
  3. Hello Steven, All,

    I slightly modified your test as follows, using a column name with a blank included,
    which usually in PL/SQL DOES REQUIRE the double quotes to be used.
    ( remember that quiz of Anil Jha last week ? ... )

    DROP TABLE plch_employees
    /

    CREATE TABLE plch_employees
    (
    employee_id INTEGER
    , last_name VARCHAR2 (100)
    , salary NUMBER NOT NULL
    , "my 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 ('"my comment"')
    THEN
    sys.DBMS_OUTPUT.put_line ('Fired "my comment".');
    ELSE
    sys.DBMS_OUTPUT.put_line ('"my 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 ('"MY COMMENT"')
    THEN
    sys.DBMS_OUTPUT.put_line ('Fired "MY COMMENT".');
    ELSE
    sys.DBMS_OUTPUT.put_line ('"MY 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 ('my comment')
    THEN
    sys.DBMS_OUTPUT.put_line ('Fired my comment.');
    ELSE
    sys.DBMS_OUTPUT.put_line ('my 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 ('MY COMMENT')
    THEN
    sys.DBMS_OUTPUT.put_line ('Fired MY COMMENT.');
    ELSE
    sys.DBMS_OUTPUT.put_line ('MY 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 "my comment" = 'This is my comment for emp no 200'
    WHERE employee_id = 200;

    COMMIT;
    END;
    /

    And the result is ( in Oracle version 11.1.0.7.0 ):

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

    PL/SQL procedure successfully completed.


    This shows that, if using double quotes as an argument to a function like UPDATING, a.s.o.
    or, in fact, generally as an argument to any function and NOT as a PL/SQL identifier by itself,
    then the double quotes are in fact part of the string value and ARE NOT removed by Oracle
    as is sometimes the case with PL/SQL identifiers.

    As a result, when NOT using the double quotes at all ( even if the column name contains a blank ! )
    then the result is as expected and the column name is, indeed, case insensitive,
    just as Nikotin said above.


    ... and Yes, Yes, Yes !!!
    We ARE reading your book and enjoying it a lot !!!!!!!!!!!!
    If the day only had more than 24 hours ...

    Thanks & Best Regards,
    Iudith

    ReplyDelete
  4. And as a consequence we get:

    drop table plch_test;

    create table plch_test
    (
    "COL" number,
    "col" number,
    "Col" number
    );

    insert into plch_test values (0, 0, 0);
    commit;

    create or replace trigger plch_test_trg
    after update on plch_test
    for each row
    begin
    if updating('col') then
    dbms_output.put_line('('||:old."COL"||','||:old."col"||','||:old."Col"||')->('
    ||:new."COL"||','||:new."col"||','||:new."Col"||')');
    end if;
    end;
    /

    begin
    update plch_test set "COL" = 1;
    rollback;
    update plch_test set "col" = 1;
    rollback;
    update plch_test set "Col" = 1;
    rollback;
    end;
    /

    Result on 10.2.0.4, 11.1.0.7, 11.2.0.1 and 11.2.0.2:

    (0,0,0)->(1,0,0)
    (0,0,0)->(0,1,0)
    (0,0,0)->(0,0,1)

    PL/SQL procedure successfully completed.

    ReplyDelete
  5. Steven,

    Speaking of the book being to big and heavy, I wish I could get it on my kindle...

    Regards,
    Dan

    ReplyDelete
  6. I think you can do that through Safari.

    ReplyDelete
  7. The O'Reilly Ebooks are available in Mobi format, which you can load onto the Kindle.

    The Kindle also handles PDFs, though the pagination of A4-based PDFs either means small text or a rougher page flow.

    I've got the Mobi version of the Oracle documentation loaded on my Kindle and its a big improvement over the PDFs.

    ReplyDelete