Help creating trigger

[gimme_this_gimme_that@yahoo.com]

Hi,

Start up db2 8.2 as "db2 -td@"

and execute the following command :

CREATE OR REPLACE TRIGGER "BZ".ALERT_CREA TED_DATE
BEFORE INSERT ON TS_ALERTS REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN ATOMIC
SET NEW.CREATED_DAT E = 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_trigge r>".
SQLSTATE=42601

Thanks.

[gimme_this_gimme_that@yahoo.com]

Re: Help creating trigger

db2 => CREATE TRIGGER ALERT_CREATED_D ATE
NO CASCADE
BEFORE INSERT ON TS_ALERTS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
SET NEW.CREATED_DAT E = 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_C REATED_DATE"
includes an
invalid use of correlation name or transition table name "OLD". Reason

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

[Serge Rielau]

Re: Help creating trigger

gimme_this_gimm e_that@yahoo.co m wrote:[color=blue]
> db2 => CREATE TRIGGER ALERT_CREATED_D ATE
> NO CASCADE
> BEFORE INSERT ON TS_ALERTS
> REFERENCING OLD AS OLD NEW AS NEW
> FOR EACH ROW
> MODE DB2SQL
> BEGIN ATOMIC
> SET NEW.CREATED_DAT E = 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_C REATED_DATE"
> includes an
> invalid use of correlation name or transition table name "OLD". Reason
>
> code="2". LINE NUMBER=4. SQLSTATE=42898
>[/color]
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]

Re: Help creating trigger

gimme_this_gimm e_that@yahoo.co m wrote:
[color=blue]
> Hi,
>
> Start up db2 8.2 as "db2 -td@"
>
> and execute the following command :
>
> CREATE OR REPLACE TRIGGER "BZ".ALERT_CREA TED_DATE
> BEFORE INSERT ON TS_ALERTS REFERENCING OLD AS OLD NEW AS NEW
> FOR EACH ROW
> BEGIN ATOMIC
> SET NEW.CREATED_DAT E = 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_trigge r>".
> SQLSTATE=42601[/color]

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

--
Knut Stolze
Information Integration
IBM Germany / University of Jena