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

Help creating trigger

P: n/a
Hi,

Start up db2 8.2 as "db2 -td@"

and execute the following command :

CREATE OR REPLACE TRIGGER "BZ".ALERT_CREATED_DATE
BEFORE INSERT ON TS_ALERTS REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN ATOMIC
SET NEW.CREATED_DATE = CURRENT DATE;
END
@

Why does this error occur?
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 "CREATE OR REPLACE TRIGGER" was found
following
"BEGIN-OF-STATEMENT". Expected tokens may include:
"<create_trigger>".
SQLSTATE=42601

Thanks.

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
db2 => CREATE TRIGGER ALERT_CREATED_DATE
NO CASCADE
BEFORE INSERT ON TS_ALERTS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
SET NEW.CREATED_DATE = CURRENT DATE;
END

The same message appears for AFTER INSERT as
for BEFORE INSERT.
Do you want to execute the above command ? (y/n) y
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0696N The definition of trigger "BOBNET.ALERT_CREATED_DATE"
includes an
invalid use of correlation name or transition table name "OLD". Reason

code="2". LINE NUMBER=4. SQLSTATE=42898

Nov 12 '05 #2

P: n/a
gi*******************@yahoo.com wrote:
db2 => CREATE TRIGGER ALERT_CREATED_DATE
NO CASCADE
BEFORE INSERT ON TS_ALERTS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
SET NEW.CREATED_DATE = CURRENT DATE;
END

The same message appears for AFTER INSERT as
for BEFORE INSERT.
Do you want to execute the above command ? (y/n) y
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0696N The definition of trigger "BOBNET.ALERT_CREATED_DATE"
includes an
invalid use of correlation name or transition table name "OLD". Reason

code="2". LINE NUMBER=4. SQLSTATE=42898

I presume you figred out that CREATE OR REPLACE is not DB2 syntax then....
Now, what would be thr meaning of OLD for INSERT?
What would be the meaning od NEW for DELETE?
Just leave OLD away for INSERT.
Only UPDATE has the concept of OLD as well as NEW

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

P: n/a
gi*******************@yahoo.com wrote:
Hi,

Start up db2 8.2 as "db2 -td@"

and execute the following command :

CREATE OR REPLACE TRIGGER "BZ".ALERT_CREATED_DATE
BEFORE INSERT ON TS_ALERTS REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN ATOMIC
SET NEW.CREATED_DATE = CURRENT DATE;
END
@

Why does this error occur?
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 "CREATE OR REPLACE TRIGGER" was found
following
"BEGIN-OF-STATEMENT". Expected tokens may include:
"<create_trigger>".
SQLSTATE=42601


You must not specify the "OR REPLACE". Have a look at the syntax diagram
for the CREATE TRIGGER statement:
http://publib.boulder.ibm.com/infoce...n/r0000931.htm

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.