April 30, 2015 at 10:15 am
I have a script that updates assignments of closed help desk tickets and I modified it and now it updates all open assignments and not just the one ticket you are working on. Any suggestions at why this changed caused this.
Here is the way it looked before being modified:
Select
@ClosedBy = ClosedBy,
@ClosedDate =ClosedDate,
@ClosedTime =ClosedTime,
@CallID =CallId,
@CallStatus =CallStatus
FROM Inserted
Select
@CallStatusBefore = CallStatus
FROM Deleted
If (@CallStatus = 'Closed' and
@CallStatus <> @CallStatusBefore)
Begin
Update Asgnmnt
Set DateResolv =@ClosedDate,
TimeResolv =@ClosedTime,
WhoResolv =@ClosedBy
Where CallId = @CallId and
(Rtrim(WhoResolv) is null or WhoResolv = ' ')
Select @ResolvRows = @@ROWCOUNT
Update Asgnmnt
Set DateAcknow =@ClosedDate,
TimeAcknow=@ClosedTime,
WhoAcknow =@ClosedBy
Where CallId = @CallId and
(Rtrim(WhoAcknow) is null or WhoAcknow = ' ')
Select @AckRows = @@ROWCOUNT
If (@ResolvRows > 0 or @AckRows > 0)
Begin
INSERT INTO RoundRobinLog (log)
VALUES (
+'Asgnmnts ACKd, etc. for CallId:' + @CallId
+' Closed,' + convert(varchar(3),@AckRows)
+ ' row(s) acknowledged, '
+ convert(varchar(3),@ResolvRows)
+ ' row(s) resolved.')
This is the one just showing what was modified:
Begin
Update Asgnmnt
Set DateAcknow =@ClosedDate,
TimeAcknow=@ClosedTime,
WhoAcknow =@ClosedBy,
DateResolv = @ClosedDate,
TimeResolv = @ClosedTime,
WhoResolv = @ClosedBy
Where CallId = @CallId and
(Rtrim(WhoAcknow) is null or WhoAcknow = ' ') or
(Rtrim(WhoResolv) is null or WhoResolv = ' ')
Select @AckRows = @@ROWCOUNT
Select @ResolvRows = @@ROWCOUNT
If (@ResolvRows > 0 or @AckRows > 0)
Begin
INSERT INTO RoundRobinLog (log)
VALUES (
+'Asgnmnts ACKd, etc. for CallId:' + @CallId
+' Closed,' + convert(varchar(3),@AckRows)
+ ' row(s) acknowledged, '
+ convert(varchar(3),@ResolvRows)
+ ' row(s) resolved.')
April 30, 2015 at 10:29 am
You made changes to your update statements to merge them into one, but got lost with some logic.
Update Asgnmnt
Set DateAcknow =@ClosedDate,
TimeAcknow=@ClosedTime,
WhoAcknow =@ClosedBy,
DateResolv = @ClosedDate,
TimeResolv = @ClosedTime,
WhoResolv = @ClosedBy
Where CallId = @CallId
and (Rtrim(WhoAcknow) is null or WhoAcknow = ' ')
or (Rtrim(WhoResolv) is null or WhoResolv = ' ') --If this condition is true, it won't matter if the others are false
Select @AckRows = @@ROWCOUNT
Select @ResolvRows = @@ROWCOUNT -- This will always return 1
If you get to update more than one CallId in the same statement, your trigger might not work properly.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply