DB2 Trigger syntax

[Chappy]

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]

On Jun 20, 3:13 pm, Chappy <[e-mail]:

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

[Serge Rielau]

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