MySQL Forums
Forum List  »  Triggers

DB2 TRIGGER to MYSQL
Posted by: Matias Sabino
Date: February 25, 2009 08:30AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
DB2 TRIGGER to MYSQL
3884
February 25, 2009 08:30AM
2213
February 25, 2009 02:52PM
2222
February 27, 2009 12:43PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.