April 30, 2009 at 11:51 pm
I have a update query as below.
Update equipment
set name = 'A'
where name = 'B'
And in the Update trigger, i will try to update some other columns too.
Like Address, age etc.
there are 31 records in table equipment having name = 'B'.
But still the trigger is updating only the first row and not the other rows.
the name column is changed in all rows but other columns like address,age which i update through trigger are not updated except in first row.
Any one having any idea on this, Please let me know.
Thanks,
Nahid Ahmed
May 1, 2009 at 12:28 am
Why aren’t you doing the whole update in the statement? In any case no one will be able to tell you what went wrong in your trigger if you won’t show us the trigger’s code.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 3, 2009 at 6:14 pm
nahid.mansuri (4/30/2009)
I have a update query as below.Update equipment
set name = 'A'
where name = 'B'
And in the Update trigger, i will try to update some other columns too.
Like Address, age etc.
there are 31 records in table equipment having name = 'B'.
But still the trigger is updating only the first row and not the other rows.
the name column is changed in all rows but other columns like address,age which i update through trigger are not updated except in first row.
Any one having any idea on this, Please let me know.
Thanks,
Nahid Ahmed
It's probably because you told the trigger to just update 1 row. Are you using "@" anywhere in the trigger? Please post the trigger code so we can help you fix it.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2009 at 10:51 pm
Hi,
Thanks guys for your suggestions. the update trigger is a common trigger for all update operation. However i have a particular update operation in which i have to modify other columns of the table. Its something like performing some calculation and then modifying the columns. So i cannot write all the columns in the update statement.
However i have found the solution through a loop statement. I first find whether its my particular update operation or not. If it is then i will loop through all records and modify the other columns.
If you have any other better solution then please let me know.
I am writing the code below. I had used a sample of Employee table to explain the things. the actual trigger that you may find is different.
Thanks a lot,
Nahid Ahmed
ALTER TRIGGER [dbo].[Equipment_SetToNull] on [dbo].[Equipment]
For INSERT,UPDATE AS
Declare @MKMDID bigint
Declare @RomPrimaryid bigint
DECLARE @EquipId BIGINT
DECLARE @LMdl char(20)
DECLARE @LDC char(50)
DECLARE @LUID char(10)
DECLARE @l_ROOMID char(50)
DECLARE @mdl varchar(20)
DECLARE @UID varchar(10)
DECLARE @DC varchar(50)
Declare @Room varchar(50)
Declare @Count int
Declare @Iterator int
Declare @MergeRecords varchar(10)
Set @MergeRecords = 'False'
Select @Count=Count(*) from inserted
Select @EquipId=EquipId ,@MDL=Mdl, @UID=UID,@DC=DC,@RomPrimaryid=RomPrimaryid,@MKMDID=MKMDID,
@Room=Room FROM inserted
Select @LMdl=Model,@LDC=DeviceCategory,@LUID=UniversalId from "DBO"."MakeModelDevView" as M where M.MkmdId = @MKMDId
BEGIN
Set @mdl = @LMdl
Set @MergeRecords = 'True'
END
IF @DC @LDC or @DC is null
BEGIN
Set @DC = @LDC
Set @MergeRecords = 'True'
END
IF @UID @LUID or @UID is null
BEGIN
Set @UID = @LUID
Set @MergeRecords = 'True'
END
SELECT @l_Roomid=Roomid From "dbo"."Rooms" WHERE ROMPrimaryid = @ROMPrimaryid;
IF @l_Roomid is not null
BEGIN
Set @Room=@l_Roomid
END
If @MergeRecords = 'True' -- this is my typical update operation
Begin
Set @Iterator = 0
While (@Iterator < @Count)
BEGIN
UPDATE [dbo].[Equipment] SET Room=@Room,UID = @UID,DC = @DC,Mdl=@Mdl,
LastUpdateTime=getdate(),LastUpdateUser=user_name() WHERE [dbo].[Equipment].[MKMDID]=@MKMDID
Set @Iterator = @Iterator + 1
End
End
Else -- this is the usual update operation
Begin
UPDATE [dbo].[Equipment] SET Room=@Room,LastUpdateTime=getdate(),LastUpdateUser=user_name() WHERE [dbo].[Equipment].[EquipId]=@EquipId
End
May 3, 2009 at 11:30 pm
You trigger is written to work when one record is updated. Take a look at this statement that was taken from your code:
Select @EquipId=EquipId ,@MDL=Mdl, @UID=UID,@DC=DC,@RomPrimaryid=RomPrimaryid,@MKMDID=MKMDID,
@Room=Room FROM inserted
If you updated or inserted only one record to the table, then this line will work just fine. If on the other hand you updated or inserted multiple records, then each variable will get the value according to one of the records that was modified.
You also have a loop that runs an update statement, but it runs the exact same update statement each time:
While (@Iterator < @Count)
BEGIN
UPDATE [dbo].[Equipment]
SET Room=@Room,UID = @UID,DC = @DC,Mdl=@Mdl,
LastUpdateTime=getdate(),LastUpdateUser=user_name()
WHERE [dbo].[Equipment].[MKMDID]=@MKMDID
Set @Iterator = @Iterator + 1
End
Since you never modify the value of @MKMDID, the same record will be updated each time.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply