Hi,
I have a simple SQL query
UPDATE testcourselog SET grade='a' WHERE statusid=4
It 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
END
THe 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 True
You can see the grade updated correctly then the trigger updated extra records. Any idea why?
Last edited by SyCo : Dec 11th, 2007 at 6:03 pm.
