By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,610 Members | 2,310 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,610 IT Pros & Developers. It's quick & easy.

DB2 Trigger syntax

P: n/a
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.

Jun 20 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Jun 20, 3:13 pm, Chappy <jamieca...@gmail.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

Jun 20 '07 #2

P: n/a
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
Jun 20 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.