SQL Server Update Trigger

[jondow]

Hi All,

I have the following trigger which sums the values in related records in Course Details then updates this value in Course Request. This works for Inserts and Deletes but I am unable to get it to work where a Course Detail record is simply updated

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [tr_updatecpd]
ON [dbo].[tblCourseDetails]
FOR INSERT,DELETE,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

UPDATE tblCourseRequest SET
req_cpd = (
SELECT
SUM (tblCourseDetails.cor_cpd)
FROM tblCourseDetails
WHERE tblCourseDetails.req_id = tblCourseRequest.req_id)
FROM tblCourseRequest
WHERE tblCourseRequest.req_id IN (
SELECT Inserted.req_id
FROM Inserted
)
OR
tblCourseRequest.req_id IN (
SELECT Deleted.req_id
FROM Deleted
)
END



Is there some way of effectivly doing:
OR
tblCourseRequest.req_id IN (
SELECT Updated.req_id
FROM Updated
)


Thank you!

 

[SQLSister]

There is no Updated table. When record is updated it is in both the deleted and inserted pseudotables.

Questions about posting. See faq183-874