Ticket Rule - Query and email work, update fails
My intent is to capture when the person assigned as the Approver in a ticket changes the approval status to Approved then, set a new Approver (known already) set the Approval to 'none' and, send the new approver an email notification. My query captures the appropriate change and notifies the new approver via email but doesn't update the Approver and Approval fields.
The update statement works fine when run with a wizard made query that just looks at category and status, which leads me to believe I have a valid update statement, it just doesn't execute on the ticket this query captures. NOTE - the run log shows adding the ticket comment, but it does not.
Any suggestions would be most welcomed. Thanks
HD_TICKET.ID as TICKNUM, -- $ticknum
'email@example.com' AS NEWTICKETEMAIL, -- $newticketemail
U2.FULL_NAME as SUBMITTER_FULLNAME -- $submitter_fullname
JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
JOIN HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID AND C.ID = <CHANGE_ID>
JOIN HD_TICKET_CHANGE_FIELD HTCF on C.ID = HTCF.HD_TICKET_CHANGE_ID
JOIN HD_CATEGORY ON HD_CATEGORY.ID=HD_CATEGORY_ID
JOIN HD_STATUS on HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
WHERE HD_TICKET.ID = C.HD_TICKET_ID
AND HD_STATUS.NAME != 'Closed'
AND HD_CATEGORY.NAME like 'Data Center%'
AND HD_TICKET.APPROVER_ID NOT LIKE '3791'
update HD_TICKET, USER as T5
set HD_TICKET.APPROVER_ID = T5.ID,
HD_TICKET.APPROVAL = 'none'
where T5.ID = '3791' and
(HD_TICKET.ID in (<TICKET_IDS>))
06/24/2013 11:35:55> Starting: 06/24/2013 11:35:55
06/24/2013 11:35:55> Executing Select Query...
06/24/2013 11:35:55> selected 1 rows
06/24/2013 11:35:55> Adding ticket comments...
06/24/2013 11:35:55> updated 1 tickets
06/24/2013 11:35:55> Sending ticket notifications...
06/24/2013 11:35:55> sent mail to 1 of 1
06/24/2013 11:35:55> Executing Update Query...
06/24/2013 11:35:55> updated 0 rows
06/24/2013 11:35:55> Ending: 06/24/2013 11:35:55
There are no answers at this time