Hi,
I have a simple SQL query
update testcourselog set grade='a' where statusid=4It updates 114 records
I am trying to write a trigger that will change a flag to 1 for all record updated by the query.
USE [online]
GO
/****** Object: Trigger [dbo].[gl_courselog_iscounted_update] Script Date: 12/10/2007 16:49:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[gl_courselog_iscounted_update]
ON [dbo].[testcourselog]
AFTER INSERT,UPDATE
AS
IF UPDATE(grade) and (select count(*) from inserted) > 0
BEGIN
IF @@ROWCOUNT = 1
BEGIN
SET NOCOUNT ON;
UPDATE testcourselog SET iscounted=1 where userid=(select userid from inserted)
END
ELSE
BEGIN
UPDATE testcourselog SET iscounted=1
FROM testcourselog t JOIN deleted d
ON t.userid = d.userid
--WHERE d.statusid=4 --I think this shouldn't be required
END
ENDTHe problem is the trigger is updating 214 records and they appear to have no relation to the join.
The 3 columns below are statusid, grade and iscounted
8 NULL False
8 NULL False
7 NULL False
8 NULL False
8 NULL True
4 a True
4 a True
2 NULL True
2 NULL True
2 NULL False
8 NULL False
6 NULL True
8 NULL False
6 NULL False
8 NULL True
4 a True
8 NULL TrueYou can see the grade updated correctly then the trigger updated extra records. Any idea why?
