June 6, 2014 at 7:57 am
Hi,
I have a table with a column which has list of ID in numbers. Any number can be master and child, but once an ID is mapped to child it can never be a master or child. And if an ID is mapped to master it can be master but not child. This action is happening in Application1. The table has two columns called group1 and group2. Now before triggering this I need to check if master ID group value and child ID group value are similar. If similar I don't have to update and if they are different they should be updated.
The below triggers works fine and updates all rows if the Master and Child ID group size are same. How can I set a condition here.
CREATE TRIGGER [dbo].[SizeChanges] ON [dbo].[Application] for UPDATE
AS
BEGIN
declare @ID1 varchar(max),@appname1 varchar(max),@grp1 varchar(100),@site1 varchar(max),@service1 varchar(100),@group1 varchar(55),@group2 varchar(55);
declare @Master_ID1 varchar(20);
declare @Child_ID1 varchar(20);
select @ID1=i.ID from inserted i;
select @appname1=i.[App_Name] from inserted i;
select @grp1=i.L2_Group from inserted i;
select @site1=i.Serv_Desc from inserted i;
select @service1=i.Serv_Team from inserted i;
select @group1=i.group1_Size from inserted i;
select @group2=i.group2_Size from inserted i;
DECLARE childcodeupdate CURSOR FOR
SELECT [Master_ID],Child_ID
FROM Application1
where [Master_ID]=@ID1
SET NOCOUNT OFF
OPEN childcodeupdate
FETCH NEXT FROM childcodeupdate INTO
@Master_ID1,
@Child_ID1
WHILE (@@FETCH_STATUS = 0)
BEGIN
UPDATE [Application]
SET [group1_Size] = @group1 , [group2_Size]=@group2
WHERE ID = @Child_ID1 and Serv_Desc=@prod1
FETCH NEXT FROM childcodeupdate INTO
@Master_ID1,
@Child_ID1
end
CLOSE childcodeupdate
DEALLOCATE childcodeupdate
SET NOCOUNT OFF
end
GO
June 9, 2014 at 1:34 am
Would you be so kind to provide DDL (create table) scripts and some sample data? Then we don't have to do any guesswork:-D
😎
June 9, 2014 at 8:02 am
vigneshkumart50 (6/6/2014)
Hi,I have a table with a column which has list of ID in numbers. Any number can be master and child, but once an ID is mapped to child it can never be a master or child. And if an ID is mapped to master it can be master but not child. This action is happening in Application1. The table has two columns called group1 and group2. Now before triggering this I need to check if master ID group value and child ID group value are similar. If similar I don't have to update and if they are different they should be updated.
The below triggers works fine and updates all rows if the Master and Child ID group size are same. How can I set a condition here.
CREATE TRIGGER [dbo].[SizeChanges] ON [dbo].[Application] for UPDATE
AS
BEGIN
declare @ID1 varchar(max),@appname1 varchar(max),@grp1 varchar(100),@site1 varchar(max),@service1 varchar(100),@group1 varchar(55),@group2 varchar(55);
declare @Master_ID1 varchar(20);
declare @Child_ID1 varchar(20);
select @ID1=i.ID from inserted i;
select @appname1=i.[App_Name] from inserted i;
select @grp1=i.L2_Group from inserted i;
select @site1=i.Serv_Desc from inserted i;
select @service1=i.Serv_Team from inserted i;
select @group1=i.group1_Size from inserted i;
select @group2=i.group2_Size from inserted i;
DECLARE childcodeupdate CURSOR FOR
SELECT [Master_ID],Child_ID
FROM Application1
where [Master_ID]=@ID1
SET NOCOUNT OFF
OPEN childcodeupdate
FETCH NEXT FROM childcodeupdate INTO
@Master_ID1,
@Child_ID1
WHILE (@@FETCH_STATUS = 0)
BEGIN
UPDATE [Application]
SET [group1_Size] = @group1 , [group2_Size]=@group2
WHERE ID = @Child_ID1 and Serv_Desc=@prod1
FETCH NEXT FROM childcodeupdate INTO
@Master_ID1,
@Child_ID1
end
CLOSE childcodeupdate
DEALLOCATE childcodeupdate
SET NOCOUNT OFF
end
GO
Actually this trigger does not work fine. It assumes that you will never update more than 1 row. Additionally you have a cursor in a trigger. This is a performance timebomb. We can help you turn this RBAR (row by agonizing row) process into an efficient set based process if you can post the details that Eirikur suggested.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply