I have a trigger that works as expected when a row in the table is updated, but doesn't work when the row is deleted.
Two questions - (1) any ideas what I have done wrong and (2) if I want to amend this to also work with inserted, how would I code it?
Basically, when a row in [Benefits Risk] table is updated, deleted or inserted, I need to know what [Project Reference] was associated with the change (all this bit works as it works with an Update) and then total up all scores and update a master table. As the change will only ever effect one [Project Reference] at a time, I think using the Top1 to limit the first select is OK.
Appreciate all help (and any better ways of doing what I am doing )
Alter TRIGGER tg_benefits_risk_update
ON [dbo].[Benefits Risk]
FOR DELETE, Update
AS
BEGIN
SET NOCOUNT ON
Declare @Score as Decimal
Declare @Completed as Decimal
Declare @Project varchar(200)
Set @Project = (Select Top 1 [Project Reference]
FROM(Workstreams INNER JOIN Plans On Workstreams.ID = Plans.WorkstreamID) INNER JOIN [Benefits Risk] On Plans.ID = [Benefits Risk].Milestone
Where Plans.ID = (Select Milestone from Deleted))
Set @Score = (Select
Sum([Benefits Risk].BenefitValue)
FROM
(Workstreams INNER JOIN Plans On Workstreams.ID = Plans.WorkstreamID) INNER JOIN [Benefits Risk] On Plans.ID = [Benefits Risk].Milestone
Where
[Project Reference] = @Project)
Set @Completed = (Select
Sum([Benefits Risk].BenefitValue)
FROM
(Workstreams INNER JOIN Plans On Workstreams.ID = Plans.WorkstreamID) INNER JOIN [Benefits Risk] On Plans.ID = [Benefits Risk].Milestone
Where
[Project Reference] = @Project and RAG = 'C')
Update [Portfolio Register]
Set [Risk Score Total] = @Score, [Risk Score Complete] = @Completed, [Risk Percent Complete] = (@Completed / @Score * 100.0)
Where [Project Reference] = @Project;
End
I am not following all the logic in your triggers because I don't understand the data. However, the issue you are seeing is likely because you are joining to the Benefits Risk table and looking for the Milestone value that was deleted.
When the trigger is called, the row has already been deleted from the table. If you want to access what was deleted, look up DELETED table rather than looking in the Benefits_Risk table. So, perhaps it should be something like this (the example of @Score)
SET @Score = ( SELECT SUM([Benefits Risk].BenefitValue)
FROM ( Workstreams
INNER JOIN Plans ON Workstreams.ID = Plans.WorkstreamID
)
INNER JOIN DELETED ON Plans.ID = [Benefits Risk].Milestone
WHERE [Project Reference] = @Project
However, I don't know if that is exactly what you want because I don't know the relationships between the tables (whether they are one-to-one, one-to-many etc.)
Regardless, the key thing to remember is that for a delete operation, when you get to the trigger, the row(s) that you deleted are not in the table.
I too don't understand the overall logic of what is being done, esp. as not all columns have a table alias on them, but I do know that the trigger could be a single UPDATE statement, which not only speeds it up but allows it to handle more than one row being deleted at a time, which the current trigger does not. Please keep in mind that in SQL Server, a trigger fires only once per statement, not per row. Below is a very rough outline of what the trigger should look like:
ALTER TRIGGER tg_benefits_risk_update
ON [dbo].[Benefits Risk]
AFTER DELETE, UPDATE
AS
SET NOCOUNT ON;
Update pr
Set [Risk Score Total] = Score, [Risk Score Complete] = Completed, [Risk Percent Complete] = (Completed * 100.0 / Score )
From [Portfolio Register] pr
INNER JOIN (
Select Sum(/*?*/d.BenefitValue + br.BenefitValue /*?*/) As Score,
Sum(Case When RAG = 'C' Then /*?*/d.BenefitValue + br.BenefitValue /*?*/ Else 0 End) As Completed
From deleted d --?
--?Inner Join [Benefits Risk]
cross apply (
Select Top (1) [Project Reference]
From Workstreams w
INNER JOIN Plans p ON p.WorkStreamID = w.ID
--?after the DELETE is there still a row in BR with that Milestone to join to?
--?INNER JOIN [Benefits Risk] br On Plans.ID = br.Milestone
WHERE
p.ID = d.Milestone
)
GO --end of trigger
Thanks James - I thought that was iit, the row had been deleted, but then I realised it isn't the problem.
Basically I just pull the Milestone (say 27) from the Deleted table and get the related [Project Reference] from the Workstreams table, by joining with the Plans table which will contain a Plan row with the ID 27.
Once found, I sum the value from all rows of the [benefits risk] table which are linked back to the Workstreams table with a Workstreams.[Project Reference] as looked up above.
Given this, the Plans and Workstreams tables are not affected by the delete operation, so they should still be able to return regardless of the [benefits risk] row being deleted.
So, not sure why this doesn't work for deletes - expect I am doing something wrong, but I can't see what (especially as the Update operation works)
)