#29 Trigger with CALL - unsupported syntax

Released in 1.4x
closed-accepted
Drzewiecki T.
SQL (23)
5
2006-01-31
2006-01-12
Nicholas
No

The trigger text output from Oracle 8 is different
from the syntax required to set the trigger up in the
first place.

This means that when you open a trigger from
the 'Object List' and then attempt to edit and/or run
it, it will result in an error.

Example:

Run the code:

CREATE OR REPLACE TRIGGER ltr_extr_letters_tr_iu
before insert or update
ON ltr_extr_letters
for each row
call auditing.set_usage
(:new.last_updated_at, :new.last_updated_by, :new.upda
te_count, :old.update_count)
/

Query the trigger, and you'll get this output:

CREATE OR REPLACE TRIGGER ltr_extr_letters_tr_iu
before insert or update
ON ltr_extr_letters
for each row
auditing.set_usage
(:new.last_updated_at, :new.last_updated_by, :new.upda
te_count, :old.update_count);
/

Run the output text, and you'll get an error at:

auditing.set_usage (because the 'call' keyword is
missing.)

Discussion

  • Drzewiecki T.
    Drzewiecki T.
    2006-01-18

    Logged In: YES
    user_id=747155

    I confirm - this problem exists in all databases. This
    syntax was is currently not supported by SQLTools.

    I created TestCase:

    CREATE TABLE testcall(x NUMBER);

    CREATE OR REPLACE PROCEDURE proc_testcall(x NUMBER) IS
    BEGIN
    NULL;
    END;
    /

    CREATE OR REPLACE TRIGGER trigger_testcall
    BEFORE INSERT OR UPDATE
    ON testcall
    FOR EACH ROW
    call proc_testcall(:NEW.x)

     
  • Drzewiecki T.
    Drzewiecki T.
    2006-01-18

    • status: open --> open-accepted
     
  • Drzewiecki T.
    Drzewiecki T.
    2006-01-18

    • milestone: 461921 --> 455736
    • assigned_to: nobody --> tmk_pl
    • summary: Trigger SQL, output doesn't match required input syntax --> Trigger with CALL - unsupported syntax
     
  • Drzewiecki T.
    Drzewiecki T.
    2006-01-19

    Logged In: YES
    user_id=747155

    I added this to the CVS. SQLTools release is planned at the
    end of January.

    I have one more problem with this 'special' (?) trigger
    reconstruction. By default 'trigger body' is finished by ';'
    (for example: "proc_testcall(:NEW.x);") and this is not
    accepted syntax for Oracle (ORA-00911 - bad character ;)!!!

    So if we create script:
    CREATE OR REPLACE TRIGGER trigger_testcall
    BEFORE INSERT OR UPDATE
    ON testcall
    FOR EACH ROW
    call proc_testcall(:NEW.x);

    we've got an ORA-00911 (no matter in what environment -
    SQLTools, SQLPlus, Toad, Oracle8/9i/10g).

    Proper version is trigger without ';'
    CREATE OR REPLACE TRIGGER trigger_testcall
    BEFORE INSERT OR UPDATE
    ON testcall
    FOR EACH ROW
    call proc_testcall(:NEW.x)

    Maybe I have strange databases/computer/...? Can somebody
    test this ';'?

     
  • Drzewiecki T.
    Drzewiecki T.
    2006-01-19

    • status: open-accepted --> closed-accepted
     
  • Drzewiecki T.
    Drzewiecki T.
    2006-01-31

    • milestone: 455736 --> Released in 1.4x