| View previous topic :: View next topic |
| Author |
Message |
drammo
Beginner
Joined: 13 Jan 2006
Posts: 20
Topics: 10
|
 Posted: Wed Jan 18, 2006 8:59 am Post subject: IF statement in Triggers |
 |
|
Does DB2 V8 for Z/OS allow IF statement inside a create trigger SQL? If so, what is the terminator for the statement? Is it "END IF" or "END-IF" or something else?
Thanks in anticipation. |
|
| Back to top |
|
 |
Manas Biswal
Intermediate
Joined: 29 Nov 2002
Posts: 382
Topics: 27
Location: Chennai, India
|
| Posted: Thu Jan 19, 2006 10:45 am Post subject: |
|
|
drammo,
I don't see why not. The terminator should be "END IF".
Regards,
Manas
_________________
There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948) |
|
| Back to top |
|
|
drammo
Beginner
Joined: 13 Jan 2006
Posts: 20
Topics: 10
|
| Posted: Thu Jan 19, 2006 1:09 pm Post subject: |
|
|
Humor me, but I am running this query on z/os and end up with -199. QMF too points to error on line "END IF;". Please help.
| Code: | CREATE TRIGGER TRG1
AFTER UPDATE ON TABLE1
REFERENCING OLD AS O NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
IF N.COL2 = '01' THEN
UPDATE TABLE2 A
SET XCOL1 = XCOL9*0.80,
WHERE A.XCOL3 = N.COL3;
END IF;
END# |
---------+---------+---------+---------+---------+---------+---------+--
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD IF, TOKEN
<END-OF-STATEMENT> WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 857 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
X'00000359' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+-- |
|
| Back to top |
|
|
Bill Dennis
Advanced
Joined: 03 Dec 2002
Posts: 579
Topics: 1
Location: Iowa, USA
|
| Posted: Thu Jan 19, 2006 1:57 pm Post subject: |
|
|
I am new to SQL, but how about dropping the IF and doing the test in WHERE
| Code: | CREATE TRIGGER TRG1
AFTER UPDATE ON TABLE1
REFERENCING OLD AS O NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE TABLE2 A
SET XCOL1 = XCOL9*0.80,
WHERE A.XCOL3 = N.COL3 AND N.COL2 = '01';
END# |
_________________
Regards,
Bill Dennis
Disclaimer: My comments on this foorum are my own and do not represent the opinions or suggestions of any other person or business entity. |
|
| Back to top |
|
|
drammo
Beginner
Joined: 13 Jan 2006
Posts: 20
Topics: 10
|
| Posted: Thu Jan 19, 2006 2:42 pm Post subject: |
|
|
| Its a good suggestion Dennis, but my real SQL has ELSEIF conditions as well. To resolve the error, I reduced the query to bare minimum. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
