Trigger to Acknowledge and Resolve All Assignments when Call is Closed

Version 1

    Details

    Trigger to Acknowledge and Resolve All Assignments when Call is Closed


    Resolution

     

    INTERNAL/PARTNER ONLY


    FrontRange Solutions Technical Support DOES NOT build, modify or troubleshoot database triggers.  This is just an example of one way to automatically Acknowledge and Resolve all Assignments on a Call Ticket when the Call is Closed.

    CREATE TRIGGER ResolveAsgnmntOnClose 
    ON CallLog 
    AFTER UPDATE
    AS
    IF (Update(ClosedBy))
    BEGIN
    DECLARE 
    @CallID varchar(8),
    @CallStatus varchar(50),
    @ClosedBy varchar(96),
    @ClosedDate varchar(10),
    @ClosedTime varchar(8)



       SELECT
       @ClosedBy = ClosedBy,
       @ClosedDate = ClosedDate,
       @ClosedTime = ClosedTime,
       @CallID =  CallID,
       @CallStatus = CallStatus
       FROM INSERTED
         UPDATE Asgnmnt
         SET DateResolv =@ClosedDate,
         TimeResolv = @ClosedTime,
         WhoResolv = @ClosedBy,
         Resolution = 'Completed',
         TotalAsgnmntTime = '5'
         WHERE CallID = @CallID AND
         (RTRIM(WhoResolv) IS NULL OR WhoResolv = ' ')
        
         UPDATE Asgnmnt
         SET DateAcknow  =@ClosedDate,
         TimeAcknow= @ClosedTime,
         WhoAcknow = @ClosedBy
         WHERE CallID = @CallID AND
         (RTRIM(WhoAcknow) IS NULL OR WhoAcknow = ' ')
    End