December 29, 2002 at 10:37 pm
I have written a trigger in a table flatmaster the code is given below.
if i update a record in this table depending on the value in flatcode coloumn it will update
curresponding tables
1.coam -coamanyflats
2.coas -coasingleflats
3.cla -claflats
the problem is that when am updating more than one record in the flatmaster only last record is
getting updated in the curresponding table ..what is the problem here?
CREATE TRIGGER [UPDATETRIGGER] ON dbo.FlatMaster
FOR UPDATE
AS
DECLARE @Flatsno INT
DECLARE @Region_code VARCHAR(4)
DECLARE @Location INT
DECLARE @Bedroom INT
DECLARE @Area FLOAT
DECLARE @Flatcode CHAR(5)
BEGIN TRAN
SELECT @Flatsno=Flatsno FROM INSERTED
SELECT @Region_code=Region_code FROM INSERTED
SELECT @Location=Location FROM INSERTED
SELECT @Bedroom=Bedroom FROM INSERTED
SELECT @Area=Area FROM INSERTED
SELECT @Flatcode=Flatcode FROM INSERTED
IF (@Flatcode)='COAM'
BEGIN
UPDATE COAManyflats
SET Location=@Location,Region_code=@Region_code,Bedroom=@Bedroom,Area=@Area
WHERE Flatsno=@Flatsno
END
-----------If any problem rollback the transaction-----------------------------
IF @@ERROR !=0
ROLLBACK TRAN
-------------------------------------------------------------------------------------------------
---
IF (@Flatcode)='COAS'
BEGIN
UPDATE COASingleflats
SET Location=@Location,Region_code=@Region_code,Bedroom=@Bedroom,Area=@Area
WHERE Flatsno=@Flatsno
END
-----------If any problem rollback the transaction-----------------------------
IF @@ERROR !=0
ROLLBACK TRAN
-------------------------------------------------------------------------------------------------
---
IF (@Flatcode)='CLA'
BEGIN
UPDATE CLAFlats
SET Location=@Location,Region_code=@Region_code,Bedroom=@Bedroom,Area=@Area
WHERE Flatsno=@Flatsno
END
-----------If any problem rollback the transaction-----------------------------
IF @@ERROR !=0
ROLLBACK TRAN
-------------------------------------------------------------------------------------------------
---
COMMIT TRAN
December 30, 2002 at 3:55 am
Triggers fire per transaction, not per row. You need to either use a cursor (not the best idea) to apply your code to each record one at a time, or change to a set oriented solution.
Andy
December 30, 2002 at 10:00 am
For the first item, something like this would work for multiple rows:
UPDATE COAManyflats
SET Location=i.Location,Region_code=i.Region_code,Bedroom=i.Bedroom,Area=i.Area
from inserted i
WHERE Flatsno=i.Flatsno
Steve Jones
December 30, 2002 at 11:03 am
That aint no ansi join!
Andy
December 30, 2002 at 11:11 am
UPDATE c
SET location = i.Location,
Region_code = i.Region_code,
Bedroom = i.Bedroom,
Area = i.Area
FROM COAManyflats c
JOIN inserted i
ON c.Flatsno = i.Flatsno
Edited by - mromm on 12/30/2002 11:11:11 AM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply