June 15, 2016 at 11:33 pm
I have a trigger which updates a column in the same table. It works when updating the row via management studio but not when executed via SqlCommand in C#. (the row is inserted). I have used the @@NESTLEVEL to prevent loop but is this the cause and should I be using something else.
This is the first trigger I am writing from scratch. The SQL code for my trigger is below.
ALTER TRIGGER [Event].[trUpdateActivityStatusAfterUpdate]
ON [Event].[ServiceAreaActivityProcess]
FOR UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @@NESTLEVEL > 1
RETURN
DECLARE @i INT
DECLARE @iRowCount INT
DECLARE @ProcessTable TABLE
(
RowID INT IDENTITY (1,1),
ServiceAreaActivityProcessId INT,
DueDate DATE,
ExtDueDate DATE,
CompletionDate DATE
);
DECLARE @iActivityStatus INT
DECLARE @ServiceAreaActivityProcessId BIGINT
DECLARE @DueDate DATE
DECLARE @ExtDueDate DATE
DECLARE @CompletionDate DATE
DECLARE @CurrentDate DATE
INSERT @ProcessTable
SELECT ServiceAreaActivityProcessId, DueDate, ExtDueDate, CompletionDate FROM inserted
SET @CurrentDate = (SELECT CONVERT(DATE, GETDATE()))
SET @iRowCount = @@ROWCOUNT
SET @i=1
WHILE @i <= @iRowCount
BEGIN
SELECT @ServiceAreaActivityProcessId = ServiceAreaActivityProcessId, @DueDate = DueDate, @ExtDueDate = ExtDueDate, @CompletionDate = CompletionDate
FROM @ProcessTable
WHERE RowID = @i
IF @ExtDueDate IS NOT NULL
BEGIN
IF @ExtDueDate <= @CurrentDate
SET @iActivityStatus = (SELECT ActivityStatusId FROM ActivityStatus WHERE ActivityStatusCode = 'CURRENT') -- Current
ELSE
SET @iActivityStatus = (SELECT ActivityStatusId FROM ActivityStatus WHERE ActivityStatusCode = 'ODUENCOMP') -- Overdue: Not Completed
END
IF @CompletionDate IS NOT NULL
BEGIN
IF @CompletionDate <= @DueDate
SET @iActivityStatus = (SELECT ActivityStatusId FROM ActivityStatus WHERE ActivityStatusCode = 'COMPLETE') -- Completed
ELSE
SET @iActivityStatus = (SELECT ActivityStatusId FROM ActivityStatus WHERE ActivityStatusCode = 'ODUECOMP') -- Overdue: Completed
IF @ExtDueDate IS NOT NULL
BEGIN
IF @CompletionDate <= @ExtDueDate
SET @iActivityStatus = (SELECT ActivityStatusId FROM ActivityStatus WHERE ActivityStatusCode = 'EXTCOMP') -- Extension Approved
END
END
UPDATE Event.ServiceAreaActivityProcess
SET ActivityStatusId = @iActivityStatus
WHERE ServiceAreaActivityProcessId = @ServiceAreaActivityProcessId
SET @i = @i + 1
END
END
Any help will be very much appreciated.
Thanks Brian
You are never an expert, you are always learning!June 16, 2016 at 10:08 pm
If you check what the application is doing, you will probably find that a sproc is being called to do the update. This will cause @@nestlevel to be set to 1. Then when the trigger fires, @nestlevel will be incremented again.
June 17, 2016 at 12:03 am
Thankyou for your reply.
The application uses SqlCommand to do the updates, I was thinking that the @@nestlevel was somehow more than 1.
Is there anyway to cover this, should I set the @@nestlevel to 2 or 3?
Thanks Brian
You are never an expert, you are always learning!June 17, 2016 at 1:04 am
This is not a good practice at all. I assume in your c# code, rows getting inserted directly, instead of using a Stored Procedures. Usually these kind of things happens when Someone do not want to change the code for this.
If you rows are getting inserted directly, then you should change the code, so that all the logic remain on the front end.
If you are using a SP, then you should move your logic from the the trigger into SP and do the insert from there.
If you want to do something then i would suggest do it in a better way. Hope it helps.
June 17, 2016 at 5:06 am
To be clear, you can use the trigger_nestlevel() function - https://msdn.microsoft.com/en-us/library/ms182737.aspx
It's aimed for triggers.
Igor Micev,My blog: www.igormicev.com
June 19, 2016 at 7:34 pm
Thankyou Igor, trigger_nestlevel is what I was after, I have seen this used elsewhere but got it confused with @@nestlevel.
Thanks Brian
You are never an expert, you are always learning!June 20, 2016 at 6:33 am
Your trigger looks too complex to perform well under load.
In this case it looks as though it can be made set based.
An alternative would be to make the processing asynchronous either by using Service Broker or by rolling your own queue.
(Google if you are interested.)
Try something like the following:
ALTER TRIGGER [Event].[trUpdateActivityStatusAfterUpdate]
ON [Event].[ServiceAreaActivityProcess]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF (SELECT TRIGGER_NESTLEVEL() ) > 1
RETURN;
DECLARE @CurrentDate date = CURRENT_TIMESTAMP;
WITH AStatii
AS
(
SELECT [CURRENT],[ODUENCOMP],[COMPLETE],[ODUECOMP],[EXTCOMP]
FROM
(
SELECT ActivityStatusId, ActivityStatusCode
FROM #ActivityStatus
) S
PIVOT
(
MAX(ActivityStatusId)
FOR ActivityStatusCode IN ([CURRENT],[ODUENCOMP],[COMPLETE],[ODUECOMP],[EXTCOMP])
) P
)
UPDATE P
SET ActivityStatusId =
CASE
WHEN P.CompletionDate IS NOT NULL
AND P.ExtDueDate IS NOT NULL
AND P.CompletionDate <= P.ExtDueDate
THEN S.EXTCOMP
WHEN P.CompletionDate IS NOT NULL
AND P.CompletionDate <= P.DueDate
THEN S.COMPLETE
WHEN P.CompletionDate IS NOT NULL
THEN S.ODUECOMP
WHEN P.ExtDueDate IS NOT NULL
AND P.ExtDueDate <= @CurrentDate
THEN S.[CURRENT]
WHEN P.ExtDueDate IS NOT NULL
THEN S.ODUENCOMP
ELSE P.ActivityStatusId
END
FROM [Event].[ServiceAreaActivityProcess] P
CROSS JOIN AStatii S
WHERE EXISTS
(
SELECT 1
FROM inserted I
-- Assuming ServiceAreaActivityProcessId is the PK of [Event].[ServiceAreaActivityProcess]
WHERE I.ServiceAreaActivityProcessId = P.ServiceAreaActivityProcessId
);
END
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply