DB2 TRIGGER to MYSQL
hello, I need some help with this trigger I'm migrating from DB2 to MySQL.
The error gives me is as follows:
Not allowed to return a result set from a trigger
It gives this error?
Select Do not allow inside a trigger?
How can I replace it?
I need to run the wheat if the record of changes in the table has an attribute of them in another table, otherwise do not shoot, I need to make this comparison to know if I should do something or not.
I hope you understand?
Tanks.
SQL CODE:
DELIMITER |
CREATE TRIGGER CASTORDES3.CTASDOSU AFTER UPDATE ON CASTORDES3.CTASIM01
FOR EACH ROW BEGIN
DECLARE NO_ENCONTRO INT DEFAULT 0;
DECLARE DIFEXT DECIMAL ( 13 , 2 );
DECLARE DIFMOV DECIMAL ( 13 , 2 );
DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR NOT_FOUND SET NO_ENCONTRO = 1;
SET NO_ENCONTRO = 0;
SELECT CTASCUENTA FROM CASTORDES3.CTASDOS WHERE CASTORDES3.CTASDOS.CTASCUENTA = OLD.CUENTA AND CASTORDES3.CTASDOS.EMPID = OLD.EMPRESA;
IF NO_ENCONTRO = 0
THEN
SET DIFEXT = - OLD.IMPEXTJ * OLD.DBCR;
SET DIFMOV = - OLD.IMPMOV * OLD.DBCR;
UPDATE CASTORDES3.TRGCCTE
SET IMPEXTJ = CASTORDES3.TRGCCTE.IMPEXTJ + DIFEXT ,
IMPMOV = CASTORDES3.TRGCCTE.IMPMOV + DIFMOV
WHERE CASTORDES3.TRGCCTE.CUENTA = OLD.CUENTA
AND CASTORDES3.TRGCCTE.AUXIL = OLD.AUXIL
AND CASTORDES3.TRGCCTE.ACMPBCD = OLD.ACMPBCD
AND CASTORDES3.TRGCCTE.ACMPBNR = OLD.ACMPBNR
AND CASTORDES3.TRGCCTE.ABARRA = OLD.ABARRA
AND CASTORDES3.TRGCCTE.EMPRESA = OLD.EMPRESA;
IF NO_ENCONTRO = 1
THEN
INSERT INTO CASTORDES3.TRGCCTE ( AUXIL , ACMPBCD , ACMPBNR , ABARRA , EMPRESA , CUENTA , IMPEXTJ , IMPMOV )
VALUES ( OLD.AUXIL , OLD.ACMPBCD , OLD.ACMPBNR , OLD.ABARRA , OLD.EMPRESA , OLD.CUENTA , DIFEXT , DIFMOV );
END IF;
END IF;
SET NO_ENCONTRO = 0;
SELECT 1 FROM CASTORDES3.CTASDOS WHERE CASTORDES3.CTASDOS.CTASCUENTA = NEW.CUENTA AND CASTORDES3.CTASDOS.EMPID = NEW.EMPRESA;
IF NO_ENCONTRO = 0
THEN
SET DIFEXT = NEW.IMPEXTJ * NEW.DBCR;
SET DIFMOV = NEW.IMPMOV * NEW.DBCR;
UPDATE CASTORDES3.TRGCCTE
SET IMPEXTJ = CASTORDES3.TRGCCTE.IMPEXTJ + DIFEXT ,
IMPMOV = CASTORDES3.TRGCCTE.IMPMOV + DIFMOV
WHERE CASTORDES3.TRGCCTE.CUENTA = NEW.CUENTA
AND CASTORDES3.TRGCCTE.AUXIL = NEW.AUXIL
AND CASTORDES3.TRGCCTE.ACMPBCD = NEW.ACMPBCD
AND CASTORDES3.TRGCCTE.ACMPBNR = NEW.ACMPBNR
AND CASTORDES3.TRGCCTE.ABARRA = NEW.ABARRA
AND CASTORDES3.TRGCCTE.EMPRESA = NEW.EMPRESA;
IF NO_ENCONTRO = 1
THEN
INSERT INTO CASTORDES3.TRGCCTE ( AUXIL , ACMPBCD , ACMPBNR , ABARRA , EMPRESA , CUENTA , IMPEXTJ , IMPMOV )
VALUES ( NEW.AUXIL , NEW.ACMPBCD , NEW.ACMPBNR , NEW.ABARRA , NEW.EMPRESA , NEW.CUENTA , DIFEXT , DIFMOV );
END IF;
END IF;
END |
DELIMITER;
Edited 2 time(s). Last edit at 02/25/2009 08:43AM by Matias Sabino.