Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Posts
    15

    Unanswered: EXEC SQL in a triggers

    I am using DB2 V8.1 Express Edition


    Is it possible to execute an sql statement using EXEC SQL or EXEC IMMEDIAT
    Then can I do this instead:

    CREATE TRIGGER DB2INST1.TEST AFTER UPDATE OF totoId
    ON DB2INST1.TOTO REFERENCING OLD AS od NEW AS nw OLD_TABLE AS OLD NEW_TABLE AS NEW
    FOR EACH ROW MODE DB2SQL
    WHEN ( od.totoId <> nw.totoId )
    BEGIN ATOMIC
    DECLARE query VARCHAR(256);
    SET query = 'update toto set (totoId)=(3) where totoId=1'
    EXEC SQL :query;
    END@

    I got the following error still:
    [db2inst1@hs204 SCRIPTS]$ db2 -td@ -vf schema_test_trigger.db2
    CREATE TRIGGER DB2INST1.TEST AFTER UPDATE OF totoId ON DB2INST1.TOTO REFERENCING OLD AS od NEW AS nw OLD_TABLE AS OLD NEW_TABLE AS NEW FOR EACH ROW MODE DB2SQL WHEN ( od.totoId <> nw.totoId ) BEGIN ATOMIC DECLARE query VARCHAR(256); SET query = 'update toto set (totoId)=(3) where totoId=1'; EXEC SQL :query; END
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "SQL" was found following "here totoId=1';
    EXEC". Expected tokens may include: "JOIN <joined_table>". LINE NUMBER=1.
    SQLSTATE=42601

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There is no EXEC command in SQL. To execute Dynamic SQL, you use
    PREPARE and EXECUTE. But I do not think you need dynamic in this instance anyway. What happens if you try:

    CREATE TRIGGER DB2INST1.TEST AFTER UPDATE OF totoId
    ON DB2INST1.TOTO REFERENCING OLD AS od NEW AS nw OLD_TABLE AS OLD NEW_TABLE AS NEW
    FOR EACH ROW MODE DB2SQL
    WHEN ( od.totoId <> nw.totoId )
    BEGIN ATOMIC
    update toto set totoId=3 where totoId=1;
    END@

    Andy

  3. #3
    Join Date
    Jun 2004
    Posts
    15
    Thanks Andy for the reply,

    I just that in DB2 book that I have that one of the restrictions when developping SQL triggers

    - PREPARE, EXECUTE and EXECUTE IMMEDIATE statements are not allowed in triggers and UDFs. Trigger and UDF SQL is inherently dynamic.
    - Stored procedure calls from triggers and UDFs are not currently supported.

    So I don't know what's my solution now. If I use your code it will work but it is not what i want to do.

    I would like to construct the query string dynamicly then execute the statement.
    DECLARE query CHAR;
    SET query='column1,colomn2,colomn5';

    update toto set (query) = (value1,value2,value5);

    And that is not working

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Can you explain what you want you are trying to accomplish?

    Andy

  5. #5
    Join Date
    Jun 2004
    Posts
    15
    I would like to construct the query string dynamiquelly then execute the statement, for example:

    DECLARE query CHAR;
    SET query='colomn=1';

    update toto set query where totoId=1;

    My query string here could contain two or three or more colomn o update

    I could have also:

    SET query='colomn=1,colomn2=0';

    update toto set query where totoId=1;


    Is it ok ??

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You cannot do it dynamically. Please explain, in english, what you want the trigger to do?

    Andy

  7. #7
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    A suggestion would be to build a function that can be used to build your SQL statement and execute it. Functions can be called within triggers (stored procedures cannot if using UDB linux/unix/windows).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •