SEO

I have the following text in a text file that I run using the command
"db2batch -d sample -f trigger.txt ....I can't get passed syntax errors ...
the output follows the trigger txt.

--#SET DELIMITER !

drop trigger Test1 !

CREATE TRIGGER USER1.Test1
AFTER UPDATE OF LASTNAME
ON USER1.EMPLOYEE
FOR EACH ROW
MODE DB2SQL

BEGIN ATOMIC
for v1 as
SELECT lastname AS #name FROM EMPLOYEE
DO
SELECT colName as #cn FROM MASTERNAMES WHERE colName = upper(#name) !
UPDATE EMPLOYEE SET LASTNAME = '#cn' WHERE LASTNAME = '#name' !
END FOR !
END

==========
output from db2batch


C:\Documents and Settings\User1\My Documents>db2batch -d sample -f
db2test.txt

---------------------------------------------
--#SET DELIMITER !

Statement number: 1

drop trigger Test1

SQL0204N "USER1.TEST1" is an undefined name. SQLSTATE=42704


Elapsed Time is: 0.010 seconds

---------------------------------------------

Statement number: 2

CREATE TRIGGER USER1.Test1
AFTER UPDATE OF LASTNAME
ON USER1.EMPLOYEE
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
for v1 as
SELECT lastname AS #name FROM EMPLOYEE
DO
SELECT colName as #cn FROM MASTERNAMES WHERE colName = upper(#name)

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "lName
=
upper(#name)". Expected tokens may include: "<delim_semicolon>". LINE
NUMBER=1. SQLSTATE=42601


Elapsed Time is: 0.000 seconds

---------------------------------------------

Statement number: 3

UPDATE EMPLOYEE SET LASTNAME = '#cn' WHERE LASTNAME = '#name'

SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table. SQLSTATE=02000


Elapsed Time is: 0.000 seconds

---------------------------------------------

Statement number: 4

END FOR

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END
FOR
". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601


Elapsed Time is: 0.000 seconds

---------------------------------------------

Statement number: 5

END

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END ".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601


Elapsed Time is: 0.010 seconds


Summary of Results
==================
Elapsed Agent CPU Rows Rows
Statement # Time (s) Time (s) Fetched Printed
1 0.010 Not Collected 0 0
2 Not Collected Not Collected 0 0
3 Not Collected Not Collected 0 0
4 Not Collected Not Collected 0 0
5 0.010 Not Collected 0 0

Arith. mean 0.010
Geom. mean 0.010

Put the ! at the final END of the trigger.
All the statements keep using ;

Cheers
Serge

"Joe Richardson" <x@x.com> wrote
> I have the following text in a text file that I run using the command
> "db2batch -d sample -f trigger.txt ....I can't get passed syntax errors ...
> the output follows the trigger txt.
>
> --#SET DELIMITER !
>
> drop trigger Test1 !
>
> CREATE TRIGGER USER1.Test1
> AFTER UPDATE OF LASTNAME
> ON USER1.EMPLOYEE
> FOR EACH ROW
> MODE DB2SQL
>
> BEGIN ATOMIC
> for v1 as
> SELECT lastname AS #name FROM EMPLOYEE
> DO
> SELECT colName as #cn FROM MASTERNAMES WHERE colName = upper(#name) !
> UPDATE EMPLOYEE SET LASTNAME = '#cn' WHERE LASTNAME = '#name' !
> END FOR !
> END
You should use ";" instead of "!" and add "!" to last "END".
It would be better to remove quatation mark for '#cn' and '#name'.
You may not be able to use alias #cn in later SQL statement.
So, your triggar body might be like this:
BEGIN ATOMIC
DECLARE #cn VARCHAR(15);
FOR v1 as
SELECT lastname AS #name FROM EMPLOYEE
DO
SET #cn = (SELECT colName FROM MASTERNAMES WHERE colName = upper(#name)) ;
UPDATE EMPLOYEE SET LASTNAME = #cn WHERE LASTNAME = #name ;
END FOR ;
END !

I may have made some mistake. But, some of above would give you hints.