October 21, 2016 at 10:40 am
I have a table for example called ServiceCase, it has a closedDate. Meaning if the case status changed to something means end of the case, then it should mark a closedDate. Otherwise, if it is reopened, then it should mark the ClosedDate to null
So we have a trigger for the ServiceCase table. It does this:
CREATE TRIGGER [dbo].[trgServiceCase_AfterIU]
ON [dbo].[ServiceCase]
AFTER INSERT, UPDATE
AS
BEGIN
IF @@ROWCOUNT = 0 RETURN
SET NOCOUNT ON;
-- Check the new statuses
--
-- Set not-End Status updates closed status to null is not null
UPDATE dbo.ServiceCase
SET ClosedDate = NULL
FROM ServiceCase TC
INNER JOIN Inserted i
ON I.ID = TC.ID
INNER JOIN [dbo].[ServiceStatus] TS
ON TS.ID = I.ServiceStatusID
AND TS.IsEndState = 0
WHERE TC.ClosedDate IS NOT NULL
--
-- Set End Status updates closed status to today is null
UPDATE dbo.ServiceCase
SET ClosedDate = GETDATE()
FROM ServiceCase TC
INNER JOIN Inserted i
ON I.ID = TC.ID
INNER JOIN [dbo].[ServiceStatus] TS
ON TS.ID = I.ServiceStatusID
AND TS.IsEndState = 1
WHERE TC.ClosedDate IS NULL
END
;
GO
I kind of don't like the trigger much, if it is for one time update , it is fine.
But the serviceCase table is a table frequently updated, the above statement will be triggered each time that does an update.
Any suggestions to improve this?
Thanks
October 21, 2016 at 10:52 am
sqlfriends (10/21/2016)
I have a table for example called ServiceCase, it has a closedDate. Meaning if the case status changed to something means end of the case, then it should mark a closedDate. Otherwise, if it is reopened, then it should mark the ClosedDate to nullSo we have a trigger for the ServiceCase table. It does this:
CREATE TRIGGER [dbo].[trgServiceCase_AfterIU]
ON [dbo].[ServiceCase]
AFTER INSERT, UPDATE
AS
BEGIN
IF @@ROWCOUNT = 0 RETURN
SET NOCOUNT ON;
-- Check the new statuses
--
-- Set not-End Status updates closed status to null is not null
UPDATE dbo.ServiceCase
SET ClosedDate = NULL
FROM ServiceCase TC
INNER JOIN Inserted i
ON I.ID = TC.ID
INNER JOIN [dbo].[ServiceStatus] TS
ON TS.ID = I.ServiceStatusID
AND TS.IsEndState = 0
WHERE TC.ClosedDate IS NOT NULL
--
-- Set End Status updates closed status to today is null
UPDATE dbo.ServiceCase
SET ClosedDate = GETDATE()
FROM ServiceCase TC
INNER JOIN Inserted i
ON I.ID = TC.ID
INNER JOIN [dbo].[ServiceStatus] TS
ON TS.ID = I.ServiceStatusID
AND TS.IsEndState = 1
WHERE TC.ClosedDate IS NULL
END
;
GO
I kind of don't like the trigger much, if it is for one time update , it is fine.
But the serviceCase table is a table frequently updated, the above statement will be triggered each time that does an update.
Any suggestions to improve this?
Thanks
Suggestion 1: Change the update logic so that it does things properly, via a stored procedure, obviating the need for a trigger.
Suggestion 2: If you can afford a time lag between an update to the table and this ServiceCase update, have a process running periodically which performs all of the outstanding updates whenever it runs.
October 21, 2016 at 10:56 am
Suggestion 3: Test and determine whether the trigger is really a problem before spending time on it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2016 at 11:07 am
Thanks all.
We do have some front end checking and validation like if user changed the status to anything means end, then closed date is required message pop up before they submit the form.
And if it changed a status from closed status to open again, then closed date is set to null.
We first thought to leave the trigger there to double check, and it seems no harm, so suggestion No 3, how can I test to see if it is really a problem. I just thought each time there is an update, it will run the update statement. It seems a burden to the table, but I really don't know how burdensome it is .
Thanks,
October 21, 2016 at 11:08 am
I agree with Gail - see if it hurts before bothering with it.
If it DOES hurt, then I agree with Phil - this is a VERY silly thing for a trigger to be doing. This belongs with the code that actually changes the status of the records!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 21, 2016 at 11:21 am
sqlfriends (10/21/2016)
I just thought each time there is an update, it will run the update statement.
Yes, that's what will happen. Doesn't necessarily mean it's a problem worth spending time on.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2016 at 11:33 am
By looking at the code again, I see it really just updated the inserted.row, not for all, so it is not too bad.
Each time we talked about trigger there will be a debate. My front end developer seems like trigger more than DBA likes. They want some constraints done in database like trigger.
thanks,
October 21, 2016 at 12:46 pm
sqlfriends (10/21/2016)
By looking at the code again, I see it really just updated the inserted.row, not for all, so it is not too bad.Each time we talked about trigger there will be a debate. My front end developer seems like trigger more than DBA likes. They want some constraints done in database like trigger.
thanks,
I am a huge proponent for using the right tool for the job. Sometimes a trigger is just that. But in this case it REALLY REALLY REALLY sounds like the application code should simply be updating an additional field when business rules require it as other fields are updated. A trigger for what you are doing is just silly from where I sit.
Oh, I note that this logic would need to be included in INSERT code too since this is an INSERT/UPDATE trigger. And that seems even sillier that apparently you are allowed to create a NEW ServiceCase that starts out as Closed! :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 21, 2016 at 1:09 pm
Thank you, the insert will be only used for our data conversion and migration from old application database to new application database, it will be removed later after data conversion.
Also the application code does that check too, but the developer like database to do the constraints too.
public bool IsValid()
{
if (_caseStatus.IsEndState && _closedDate == null)
{
BrokenRuleMessage = "Selected status requires Closed Date";
return false;
}
if (!_caseStatus.IsEndState && _closedDate != null)
{
BrokenRuleMessage = "Closed Date not allowed with selected status";
return false;
}
if (_closedDate.GetValueOrDefault().Date > DateTime.Today)
{
BrokenRuleMessage = "Future Closed Date not allowed";
return false;
}
return true;
}
October 21, 2016 at 2:32 pm
sqlfriends (10/21/2016)
Thank you, the insert will be only used for our data conversion and migration from old application database to new application database, it will be removed later after data conversion.Also the application code does that check too, but the developer like database to do the constraints too.
public bool IsValid()
{
if (_caseStatus.IsEndState && _closedDate == null)
{
BrokenRuleMessage = "Selected status requires Closed Date";
return false;
}
if (!_caseStatus.IsEndState && _closedDate != null)
{
BrokenRuleMessage = "Closed Date not allowed with selected status";
return false;
}
if (_closedDate.GetValueOrDefault().Date > DateTime.Today)
{
BrokenRuleMessage = "Future Closed Date not allowed";
return false;
}
return true;
}
My response to this cannot be printed as it may offend some peoples' delicate sensibilities. :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply