Issue in creating the triggers
Posted: Tue Jan 08, 2013 3:38 pmHi,
i want to create a trigger that check before insert in DNBA.T04DOM10 table if there is already no GE001 or GE006 for the cod-enmpresa/cod-persona with stato-riga = '01' exist in table T04CNB01 when not then insert it like that.So i tried it, but getting the following error
i want to create a trigger that check before insert in DNBA.T04DOM10 table if there is already no GE001 or GE006 for the cod-enmpresa/cod-persona with stato-riga = '01' exist in table T04CNB01 when not then insert it like that.So i tried it, but getting the following error
CREATE TRIGGER DNBA.T04DOM10F AFTER INSERT ON DNBA.T04DOM10
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.COD_TIPO_DIREC = '1' OR
N.COD_TIPO_DIREC = '8')
BEGIN ATOMIC
IF (SELECT COUNT(*) FROM T04CNB01 A, T04DBA01 B
WHERE A.COD_PERSONA = N.COD_PERSONA
AND A.COD_EMPRESA = N.COD_EMPRESA
AND STATO_RIGA = '01'
AND COD_DATO NOT IN ('GE001','GE006')
AND A.COD_PERSONA = B.COD_PERSONA
AND A.COD_EMPRESA = B.COD_EMPRESA
AND B.COD_CENTRO_INS = '0039') > 0 THEN
INSERT INTO T04CNB01
(COD_EMPRESA,
COD_PERSONA,
COD_DATO,
COD_AMBITO,
STATO_RIGA,
FEC_MODIFICACION,
COD_USU_MODIF,
TIMESTAMP_SIGLO)
VALUES (N.COD_EMPRESA,
N.COD_PERSONA,
'GE006',
'GENER',
'01',
CURRENT DATE,
'30MINUPD',
CURRENT TIMESTAMP) ;
END IF;
END
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "<END-OF-STATEMENT>". SOME
SYMBOLS THAT MIGHT BE LEGAL ARE: FOR ; NOT ATOMIC
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 3 0 0 -1 2122 502 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000003' X'00000000' X'00000000' X'FFFFFFFF'
X'0000084A' X'000001F6' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.COD_TIPO_DIREC = '1' OR
N.COD_TIPO_DIREC = '8')
BEGIN ATOMIC
IF (SELECT COUNT(*) FROM T04CNB01 A, T04DBA01 B
WHERE A.COD_PERSONA = N.COD_PERSONA
AND A.COD_EMPRESA = N.COD_EMPRESA
AND STATO_RIGA = '01'
AND COD_DATO NOT IN ('GE001','GE006')
AND A.COD_PERSONA = B.COD_PERSONA
AND A.COD_EMPRESA = B.COD_EMPRESA
AND B.COD_CENTRO_INS = '0039') > 0 THEN
INSERT INTO T04CNB01
(COD_EMPRESA,
COD_PERSONA,
COD_DATO,
COD_AMBITO,
STATO_RIGA,
FEC_MODIFICACION,
COD_USU_MODIF,
TIMESTAMP_SIGLO)
VALUES (N.COD_EMPRESA,
N.COD_PERSONA,
'GE006',
'GENER',
'01',
CURRENT DATE,
'30MINUPD',
CURRENT TIMESTAMP) ;
END IF;
END
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "<END-OF-STATEMENT>". SOME
SYMBOLS THAT MIGHT BE LEGAL ARE: FOR ; NOT ATOMIC
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 3 0 0 -1 2122 502 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000003' X'00000000' X'00000000' X'FFFFFFFF'
X'0000084A' X'000001F6' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+