Re: Create SQL Trigger Issue

[MKirkpatrick@xxxxxxxxxxxxxxxxx]

I believe you need a semi-colon at the end of the INSERT stmt.

CREATE TRIGGER UNICMFIL/DEGREECONFERRED AFTER UPDATE OF RSI4AA ON
UNICMFIL/DGRHSP REFERENCING OLD AS OLD_ROW NEW AS NEW_ROW FOR EACH
ROW  MODE DB2ROW  WHEN ( OLD_ROW.RSI4AA <> NEW_ROW.RSI4AA )
BEGIN ATOMIC
     INSERT INTO UNION/DGRCONFERP (RSNMID, OLD_ROW.RSI4AA ,
NEW_ROW.RSI4AA , USER, CURRENT DATE, CURRENT TIME) WITH NC ;



Michael Kirkpatrick
Western Dental Services, Inc.
Information Systems / System i Development
(
Phone:
 (714) 571.3582
(
Toll free:
 (800) 471.4444
7
Fax:
 (714) 480.3001
*
Email:
 mkirkpatrick@xxxxxxxxxxxxxxxxx
8
Web:
 http://www.westerndental.com/

 



"Mark Allen" <scprideandms@xxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
09/27/2007 10:17 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc

Subject
Create SQL Trigger Issue






Could have SWORN I done this before and looking in OPS NAV at existing
triggers it looks the same.  Searched InfoCenter and I don't see a
reasoneither, might be lack of sleep, anyways I keep getting the following
in interactive SQL:

CREATE TRIGGER UNICMFIL/DEGREECONFERRED AFTER UPDATE OF RSI4AA ON
UNICMFIL/DGRHSP REFERENCING OLD AS OLD_ROW NEW AS NEW_ROW FOR EACH
ROW  MODE DB2ROW  WHEN ( OLD_ROW.RSI4AA <> NEW_ROW.RSI4AA )
BEGIN ATOMIC
     INSERT INTO UNION/DGRCONFERP (RSNMID, OLD_ROW.RSI4AA ,
NEW_ROW.RSI4AA , USER, CURRENT DATE, CURRENT TIME) WITH NC
Keyword DATE not expected. Valid tokens: ) ,.

What OBVIOUS thing am I missing..... (and yes those date fields ARE 7 
long,
vendor's way of dealing with Y2K)

The DDS for the DGRCONFERP file is:

A          R RDGRCONFER
A            RSNMID         9S 0       TEXT('Name ID')
A                                      COLHDG('Name ID')
A            OLRSI4AA       7S 0
A                                      COLHDG('Old Date Conf')
A            NWRSI4AA       7S 0
A                                      COLHDG('New Date Conf')
A            USERHS        10A
A                                      TEXT('User Who Changed')
A                                      COLHDG('User Who' 'Changed')
A            DATEHS          L
A                                      TEXT('Changed Date')
A                                      COLHDG('Changed' 'Date')
A            TIMEHS          T
A                                      TEXT('Changed Time')
A                                      COLHDG('Changed' 'Time')