Problem with Update trigger - SQL Server Forums

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)

Problem with Update trigger

Author

Topic

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2008-04-17 : 04:50:23

Hi

When triiger fire within a loop then it not update all assosiated records properlly ,it update only last row why ?

------------------------------------------------------------
Create TRIGGER Update_Expertise
ON dbo.tbl_Doctors
AFTER UPDATE
AS
BEGIN

declare @MedicalSchoolrat as decimal(18,2)

select @MedicalSchoolrat = isnull((select rating from tbl_medicalschool where id = dr.MSID),0) from inserted dr

UPDATE tbl_docTraingExpertiseRating set MedicalSchool=@MedicalSchoolrat
WHERE dr_id = dr.ID

END
---------------------------------------------------------------
Create procedure Update_MedicalSchoolrat
as
begin
declare @totalMS int,@counter int,@MSID as int
set @counter=1
select @totalMS= count(*) from tbl_medicalschool
while @counter<=@totalMS
begin

select @MSID=MSID from tbl_medicalschool where ID=@counter

update tbl_medicalschool set rating= rating * 2.0
update tbl_Doctors set Fname=Fname where MSID=@MSID ---When This Update Query will Fire Then above Trigger (Update_Expertise) should be fire
set @counter = @counter+1
end
end

------------------------------------------------
--Before Execution of procedure Three Tables's data
--****tbl_medicalschool Rows*******

ID rating
1 3.2
2 3.6
3 3.8
4 3.7
5 3.3
------------tbl_Doctors-------------
ID MSID Fname
11 1 a
21 2 b
31 3 c
41 4 d
51 5 e
------------tbl_docTraingExpertiseRating---
dr_id MedicalSchool

11 3.2
21 3.6
31 3.8
41 3.7
51 3.3

-----------------------------------------------------------------

--After Execution of procedure
--****tbl_medicalschool*******

ID rating
1 6.4
2 7.2
3 7.6
4 7.4
5 6.6
------------tbl_Doctors-------------
ID MSID Fname
11 1 a
21 2 b
31 3 c
41 4 d
51 5 e
------------tbl_docTraingExpertiseRating---
dr_id MedicalSchool

11 3.2
21 3.6
31 3.8
41 3.7
51 6.6 -----------Only Last row updated while it should change all

Ranjeet Kumar Singh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 04:56:25

[code]CREATE TRIGGER Update_Expertise ON dbo.tbl_Doctors
AFTER UPDATE
AS

UPDATE t
SET t.MedicalSchool = x.rating
FROM inserted AS i
INNER JOIN tbl_docTraingExpertiseRating AS t ON t.dr_id = i.ID
INNER JOIN tbl_medicalschool AS x ON x.id = i.MSID
[/code]

E 12°55'05.25"
N 56°04'39.16"

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2008-04-17 : 05:05:32

Hi

Please can you tell me that why Inner join necessary.

Ranjeet Kumar Singh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 05:14:23

To avoid the programming error in your original code.
Because when you update one record only, your original code may work.
But when your update affects two two or more records, your original code does not work.

E 12°55'05.25"
N 56°04'39.16"

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2008-04-17 : 05:20:16

Thanks............

Ranjeet Kumar Singh

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-04-17 : 05:49:07

A trigger only fires once for every "batch" of update/inserts/deletes you do, and the updated and deleted-tables will have multiple rows in them. That's why you need to join...

--
Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources