MySQL Forums
Forum List  »  General

Re: UPDATE statement in AFTER INSERT TRIGGER doesn't get excecuted
Posted by: Rick James
Date: May 25, 2013 08:17PM

First, let's do some cleanup...

> DECLARE TID INT;
> @TID
Those are two different things.

In TRIGGERs, use the pseudo tables NEW and OLD to talk about the row that is changing.

> SET @TID = (SELECT TICKETID FROM TICKETHISTORY WHERE ID=(select
MAX(ID) FROM TICKETHISTORY));
-->
SELECT @TID := TICKETID FROM TICKETHISTORY
ORDER BY ID DESC LIMIT 1;

> SET @THID = (SELECT MAX(ID) FROM TICKETHISTORY);
> SET @UID = (SELECT TICKETHISTORY.USERID FROM TICKETHISTORY WHERE ID=(SELECT
> MAX(ID) FROM TICKETHISTORY));
-->
SELECT @THID := MAX(ID), @UID := USERID FROM TICHETHISTORY;

> IF (SELECT TICKET.STATUSID FROM TICKET WHERE
> TICKET.ID=@TID)=7
> AND (SELECT TICKETHISTORY.COMMENTTYPE FROM TICKETHISTORY WHERE
> ID=@THID)=0
> THEN UPDATE TICKET SET TICKET.STATUSID='4' WHERE ID=@TID;
> END IF;
-->
IF ( OLD.COMMENTTYPE = 0 )
THEN
UPDATE TICKET
SET TICKET.STATUSID = 4
WHERE TICKET.ID = @TID
AND TICKET.STATUSID = 7
END IF;

Well, I am not following your logic, so I can't give you the full answer, but please understand the changes I have here and work that direction.

Options: ReplyQuote


Subject
Written By
Posted
Re: UPDATE statement in AFTER INSERT TRIGGER doesn't get excecuted
May 25, 2013 08:17PM


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.