How to make a trigger update one row, not all? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to make a trigger update one row, not all?

Author

Topic

invisible777
Starting Member

10 Posts

Posted - 2007-04-25 : 12:19:50

Hi, kind of new to SQL (using server2005).

In short, I have a trigger that is updating every record of a given table. I need it to update only one record of that column at a time, as defined by a Client_ID.

I need Total_Credits in the Education table to update whenever a new row is inserted in the School_Info table (trigger on School_Credits). But I only want Total_Credits to update per Client_ID, right now Total_Credits updates regardless of Client_ID. It's a one to many relationship from the Education to School_Info (one education profile can have many Schools). Hope this makes sense.




CREATE TRIGGER TotalCredits_tr
       on School_Info
   after insert
  as
    declare @foo int
    select   @foo = School_ID from inserted

    UPDATE Education
    set Total_Credits = Total_Credits + inserted.School_Credits
	from Education
	JOIN School_Info on Education.Education_ID = School_Info.School_ID;

jshepler
Yak Posting Veteran

60 Posts

Posted - 2007-04-25 : 12:29:39

JOIN inserted ON Education.Education_ID = inserted.School_ID

/jeff

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-25 : 12:36:43

CREATE TRIGGER TotalCredits_tr
on School_Info
after insert
as

UPDATE e
set e.Total_Credits = e.Total_Credits + i.School_Credits
from Education e
Inner JOIN School_Info s
on e.Education_ID = s.School_ID
inner join inserted i
on i.school_ID = s.School_ID

invisible777
Starting Member

10 Posts

Posted - 2007-04-26 : 00:06:30

Thanks guys... one more quick question... If I select something from a given table is there any way to hide it in the output/results?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 00:20:51

quote:
Originally posted by invisible777

Thanks guys... one more quick question... If I select something from a given table is there any way to hide it in the output/results?

Select into variable ? another table ?

Why do you want to do this ?
What is your requirement ?

KH

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-26 : 00:37:06

quote:
Originally posted by invisible777

Thanks guys... one more quick question... If I select something from a given table is there any way to hide it in the output/results?

Wouldn't it be easier to just not select it?

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources