Re: UPDATE statement in AFTER INSERT TRIGGER doesn't get excecuted
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.
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.