Help creating trigger

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gimme_this_gimme_that@yahoo.com - 30 Apr 2005 00:39 GMT

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.

Reply to this Message

gimme_this_gimme_that@yahoo.com - 30 Apr 2005 01:24 GMT

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

Reply to this Message

Serge Rielau - 30 Apr 2005 02:40 GMT

> db2 => CREATE TRIGGER ALERT_CREATED_DATE
> NO CASCADE
[quoted text clipped - 19 lines]
>
> 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

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

Knut Stolze - 30 Apr 2005 06:37 GMT

> Hi,
>
[quoted text clipped - 21 lines]
> "<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/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin
/r0000931.htm

Signature

Knut Stolze
Information Integration
IBM Germany / University of Jena

Reply to this Message