trigger not working in all records

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 2000 Forums

SQL Server Development (2000)

trigger not working in all records

Author

Topic

rajesha
Starting Member

36 Posts

Posted - 2002-12-30 : 00:25:22

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

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-30 : 01:49:40

Every sql statement is a transaction, a trigger is part of that statement so is also always included in a transaction - so you don't need the begin trans.

For each statement the trigger will fire once so if it affects multiple records things like
SELECT @Flatsno=Flatsno FROM INSERTED
will end up with one of the records from inserted.

You need to join to inserted/deleted in all your statements using the primary key to get corresponding records.

UPDATE COAManyflats
SET Location=i.Location,Region_code=i.Region_code,Bedroom=i.Bedroom,Area=i.Area
from inserted i
WHERE COAManyflats.Flatsno=i.Flatsno

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources