| View previous topic :: View next topic |
| Author |
Message |
SMS
Beginner
Joined: 16 Oct 2005
Posts: 53
Topics: 24
|
 Posted: Mon Jul 21, 2008 9:50 am Post subject: Declaring variables in Trigger |
 |
|
Hi,
I am having an issue in declaring variables inside a trigger. The trigger statement is as follows. If I remove the declare and set, able to create the trigger. Otherwise getting the SQL error 20100. Also in the Db2 manual, I don't see a DECLARE statement inside the CREATE TRIGGER statement. So please let know whether it is possible to use DECLARE statement inside a trigger or not.
| Code: |
CREATE TRIGGER SS.TEMP
AFTER INSERT ON SS.TEST1
BEGIN
DECLARE v1 DECIMAL(7,3);
SET V1=125;
INSERT INTO SS.TEST1 (VAR1) VALUES(v1);
END;
-20100: SQL0969N
AN ERROR OCCURRED WHEN BINDING A TRIGGERED SQL STATEMENT. INFORMATION RETURNED: SECTION NUMBER : section-number SQLCODE sqlerror, SQLSTATE sqlstate, AND MESSAGE TOKENS token-list.
|
In the error message returned from my SQL editor, it is not showing the section number,sqlstate...so I am stuck up.. It just shows the error as follows
20100: SQL0969N There is no message text corresponding to SQL error "-20100" in the message file on this workstation. The error was returned from module "DSNX0TG".
I would appreciate any help in this regard.
_________________
Regards,
SMS |
|
| Back to top |
|
 |
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Mon Jul 21, 2008 10:08 am Post subject: |
|
|
since you know what the variable is going to be, why code it that way?. Code a literal.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
CraigG
Intermediate
Joined: 02 May 2007
Posts: 202
Topics: 0
Location: Viginia, USA
|
| Posted: Mon Jul 21, 2008 10:11 am Post subject: |
|
|
How about
| Code: | CREATE TRIGGER SS.TEMP
AFTER INSERT ON SS.TEST1
BEGIN
INSERT INTO SS.TEST1 (VAR1) VALUES(125);
END;
|
|
|
| Back to top |
|
|
SMS
Beginner
Joined: 16 Oct 2005
Posts: 53
Topics: 24
|
| Posted: Mon Jul 21, 2008 11:11 am Post subject: |
|
|
Thanks for your responses. I have shown just the portion of my trigger. Actual intention of using this variable is different. So I need to use the DECLARE statement, but not sure whether it is possible or not. Please throw some light.
_________________
Regards,
SMS |
|
| Back to top |
|
|
jsharon1248
Intermediate
Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
|
| Posted: Mon Jul 21, 2008 11:25 am Post subject: |
|
|
| Have you changed the statement delimiter to something other than ';'? |
|
| Back to top |
|
|
SMS
Beginner
Joined: 16 Oct 2005
Posts: 53
Topics: 24
|
| Posted: Mon Jul 21, 2008 2:09 pm Post subject: |
|
|
No, I didn't change the delimiter
_________________
Regards,
SMS |
|
| Back to top |
|
|
jsharon1248
Intermediate
Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
|
| Posted: Mon Jul 21, 2008 2:25 pm Post subject: |
|
|
| You need to change the statement delimiter to something else, like #. If you don't, the tool you're executing the command from thinks the end of the command is the first ; that it encounters. What tool are you using to submit the command? |
|
| Back to top |
|
|
SMS
Beginner
Joined: 16 Oct 2005
Posts: 53
Topics: 24
|
| Posted: Tue Jul 22, 2008 7:34 am Post subject: |
|
|
I am using SQL Assistant. Let me try changing the delimiter. Thanks!
_________________
Regards,
SMS |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
