Error in creating a trigger: SQLCODE = -104

itzbalajee · New User Joined: 11 Aug 2006 Posts: 5 Location: Chennai

Hi,

I tried to create a trigger for the ERM_DET table.I got the error (given below). Please help me.

[color=darkred]create trigger emptr001 after insert on emp_det referencing new as e01
for each row mode db2sql when (e01.emp_id > spaces)
begin atomic insert into emp_det_audit
values(e01.emp_id,e01.emp_name,'emptr001');
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "END". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: ;
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 284 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF'
X'0000011C' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+[/color]

William Thompson · Posted: Wed Mar 21, 2007 4:35 pm

You are missing the "END" ant the end of the trigger body.

itzbalajee · New User Joined: 11 Aug 2006 Posts: 5 Location: Chennai

Hi,

Thanks for your reply.

But now i am getting this error

---------+---------+---------+---------+---------+---------+---------+---------+
create trigger emptr001 after insert on emp_det referencing new as e01
for each row mode db2sql when (e01.emp_id > spaces)
begin atomic insert into emp_det_audit
values(e01.emp_id,e01.emp_name,'emptr001');end;
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL " ". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: QUOTE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 158 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF'
X'0000009E' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+

William Thompson · Posted: Wed Mar 21, 2007 9:55 pm

What is the purpose of the last semicolon?

vini_srcna · Posted: Thu Mar 22, 2007 10:31 am

Remove the semicolon before to END and become familiar with the syntax. You can find it in manuals.

senthilssg · New User Joined: 09 Dec 2005 Posts: 64 Location: USA

Hi ,

You will have use set terminator statement to diiferentiate your end of trigger and termination of sql query in triggered action .

Sample code for trigger :

Code:

--#SET TERMINATOR @ ;[/b]
--CREATE TRIGGER TRIG_DEL_EMP
-- AFTER DELETE ON EMP99999
-- FOR EACH ROW MODE DB2SQL
-- BEGIN ATOMIC
-- UPDATE COMPANY_STATS SET NO_OF_EMP=NO_OF_EMP-1 ;
-- END @
--#SET TERMINATOR ;

I hope this help you

Regards
senthil