Help creating trigger

[gimme_this_gimme_that]

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.

[gimme_this_gimme_that]

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

[Serge Rielau]

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

[Knut Stolze]

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