Trigger updated values not as expected

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 426,422 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,382 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser: Programming Forums

Thread Tools Display Modes

Views: 745 | Replies: 6

Previous Post in Thread

Next Post in Thread

•

Join Date: Dec 2007

Posts: 4

Reputation: SyCo is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

SyCo

Offline

Newbie Poster

Trigger updated values not as expected

Dec 11th, 2007

Hi,
I have a simple SQL query

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
UPDATE testcourselog SET grade='a' WHERE statusid=4
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.

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
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

Help with Code Tags

(Toggle Plain Text)

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.