DB2 Trigger syntax

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Chappy

    #1

    DB2 Trigger syntax

    Hi,

    I'm new to DB2 from SQL Server, There's a problem with the syntax
    below, is anyone able to see what the issue is? Do I have too many
    semi colans?

    CREATE TRIGGER AUDIT
    AFTER UPDATE ON ABEFORE
    REFERENCING OLD AS O
    FOR EACH ROW MODE DB2SQL
    WHEN ('YES' IN SELECT CONTROLFLAG FROM TRIGGERCONTROL WHERE
    TRIGGERNAME = 'AUDIT')
    BEGIN ATOMIC
    INSERT INTO AAFTER (AAFTERID,NAME, TIMESTAMP) VALUES
    (O.ABEFOREID,O. NAME,CURRENT TIMESTAMP);
    END;

    I have a table called ABEFORE and a table called AAFTER, the insert
    statement works fine when I pass in 1,'TEST' CURRENT TIMESTAMP

    Any help would be appreciated.

  • Chappy

    #2
    Re: DB2 Trigger syntax

    On Jun 20, 3:13 pm, Chappy <jamieca...@gma il.comwrote:
    Hi,
    >
    I'm new to DB2 from SQL Server, There's a problem with the syntax
    below, is anyone able to see what the issue is? Do I have too many
    semi colans?
    >
    CREATE TRIGGER AUDIT
    AFTER UPDATE ON ABEFORE
    REFERENCING OLD AS O
    FOR EACH ROW MODE DB2SQL
    WHEN ('YES' IN SELECT CONTROLFLAG FROM TRIGGERCONTROL WHERE
    TRIGGERNAME = 'AUDIT')
    BEGIN ATOMIC
    INSERT INTO AAFTER (AAFTERID,NAME, TIMESTAMP) VALUES
    (O.ABEFOREID,O. NAME,CURRENT TIMESTAMP);
    END;
    >
    I have a table called ABEFORE and a table called AAFTER, the insert
    statement works fine when I pass in 1,'TEST' CURRENT TIMESTAMP
    >
    Any help would be appreciated.
    There generally seems to be a problem with me using BEGIN ATOMIC

    Comment

    • Serge Rielau

      #3
      Re: DB2 Trigger syntax

      There generally seems to be a problem with me using BEGIN ATOMIC
      Indeed.
      First there is a typo ABEFORE should be A BEFORE. Perhaps a copy-paste
      in the post.
      You main problem is that by default the CLP treats ";" very much like
      "GO" and that conflicts with the ";' which is part of the SQL PL language.

      You can change the termination character in the clp with db2 -td@ (to
      set it to "@"). In the GUI there is a setting either in the preferences
      pane or somewhere on the pane of the editor itself.
      In DB2 9 I think multi character terminators are also supported. So you
      could use GO for convenience.

      Within a script or CLP I liek to change teh terminator before each
      CREATE PROCEDURE/TRIGGER/FUNCTION with:
      --#SET TERMINATOR @
      CREATE TRIGGER ...
      BEGIN ATOMIC
      ....
      END
      @
      --#SET TERMINATOR ;

      Cheers
      Serge
      --
      Serge Rielau
      DB2 Solutions Development
      IBM Toronto Lab

      Comment

      Working...