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?

Doh, my first post and it turned out I messed up!

I was joining on a non unique value. Hence the confused results.

It's still be nice if you say hi :)

Hiii :icon_mrgreen:

See, that was nice.

^

welcome back :P

Hi, I got my kick in the butt email. My bosds sprang for an expex account, but honestly it's not the greatest value.

I guess I'll try to be here more often, see you around!

commented: See you around then .. +5
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.