Insert multiple values through Trigger
-
Hi frends I have written a trigger in MS-SQL 2000 runing successfully to insert single row.....but if i need to insert multiple rows from a select query in this trigger ......I am Confused ? :confused: Purpose of Trigger whenever a column will be updated in a table the data from two tables will be inserted into two tables where one table has multiple rows to insert. I am trying to use While loop but confused that what should be the condition there to break tha loop......pls help if someone knows. :confused: My trigger is IF (COLUMNS_UPDATED() ) > 49 BEGIN Declare @ServerDate datetime; Declare @TranID numeric(18,0); Declare @OppID numeric(18,0); Declare @ProductID int; Declare @Qty int; Declare @Cost numeric(18,2); Declare @Price numeric(18,2); Declare @Discount numeric(18,2); Declare @TaxAmt numeric(18,2); Declare @UserID numeric(18,0); Declare @ContactID numeric(18,0) Declare @CountProduct int SELECT @ServerDate=getdate() SELECT @OppID=Opp.OppId, @ContactID=Opp.ContactID, @UserID=Opp.UserID FROM Opportunity Opp INNER JOIN INSERTED ON Opp.OppId = INSERTED.OppId where Inserted.StatusID=3 if (Convert(varchar(10),@OppID) != '') BEGIN INSERT INTO [Transaction] (OppID,TranDate, ContactID,UserID) values (@OppId, @ServerDate, @ContactID, @UserID) SELECT @TranID=TranID from [Transaction] where TranDate=@ServerDate SELECT @ProductID=Opp.ProductID, @Qty=Opp.Qty, @Cost=Opp.Cost, @Price=Opp.Price, @Discount=Opp.Discount, @TaxAmt=Opp.TaxAmt FROM vOpportunityList Opp INNER JOIN INSERTED ON Opp.OppId = INSERTED.OppId where Inserted.StatusID=3 --Count row SELECT @CountProduct=count(Opp.ProductID) FROM vOpportunityList Opp INNER JOIN INSERTED ON Opp.OppId = INSERTED.OppId where Inserted.StatusID=3 --WHILE (-----------) --BEGIN INSERT INTO [TranProduct] (TranID,ProductID,Qty,Cost,Price,Discount,TaxAmt) values(@TranID, @ProductID, @Qty, @Cost, @Price, @Discount, @TaxAmt) --END END END
Dinesh Sharma
-
Hi frends I have written a trigger in MS-SQL 2000 runing successfully to insert single row.....but if i need to insert multiple rows from a select query in this trigger ......I am Confused ? :confused: Purpose of Trigger whenever a column will be updated in a table the data from two tables will be inserted into two tables where one table has multiple rows to insert. I am trying to use While loop but confused that what should be the condition there to break tha loop......pls help if someone knows. :confused: My trigger is IF (COLUMNS_UPDATED() ) > 49 BEGIN Declare @ServerDate datetime; Declare @TranID numeric(18,0); Declare @OppID numeric(18,0); Declare @ProductID int; Declare @Qty int; Declare @Cost numeric(18,2); Declare @Price numeric(18,2); Declare @Discount numeric(18,2); Declare @TaxAmt numeric(18,2); Declare @UserID numeric(18,0); Declare @ContactID numeric(18,0) Declare @CountProduct int SELECT @ServerDate=getdate() SELECT @OppID=Opp.OppId, @ContactID=Opp.ContactID, @UserID=Opp.UserID FROM Opportunity Opp INNER JOIN INSERTED ON Opp.OppId = INSERTED.OppId where Inserted.StatusID=3 if (Convert(varchar(10),@OppID) != '') BEGIN INSERT INTO [Transaction] (OppID,TranDate, ContactID,UserID) values (@OppId, @ServerDate, @ContactID, @UserID) SELECT @TranID=TranID from [Transaction] where TranDate=@ServerDate SELECT @ProductID=Opp.ProductID, @Qty=Opp.Qty, @Cost=Opp.Cost, @Price=Opp.Price, @Discount=Opp.Discount, @TaxAmt=Opp.TaxAmt FROM vOpportunityList Opp INNER JOIN INSERTED ON Opp.OppId = INSERTED.OppId where Inserted.StatusID=3 --Count row SELECT @CountProduct=count(Opp.ProductID) FROM vOpportunityList Opp INNER JOIN INSERTED ON Opp.OppId = INSERTED.OppId where Inserted.StatusID=3 --WHILE (-----------) --BEGIN INSERT INTO [TranProduct] (TranID,ProductID,Qty,Cost,Price,Discount,TaxAmt) values(@TranID, @ProductID, @Qty, @Cost, @Price, @Discount, @TaxAmt) --END END END
Dinesh Sharma
I would suggest a cursor but using a cursor in a trigger is bad practice as it will be slow. Is there no way to do this update from your application inside of commitment control? If a trigger is your only option then use a cursor to do the loop. You don't need a row count variable either just loop through the results of your select statement. Check Here for more info.
how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things --thedailywtf 3/21/06
-
I would suggest a cursor but using a cursor in a trigger is bad practice as it will be slow. Is there no way to do this update from your application inside of commitment control? If a trigger is your only option then use a cursor to do the loop. You don't need a row count variable either just loop through the results of your select statement. Check Here for more info.
how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things --thedailywtf 3/21/06
Thanks ToddHileHoffer I done this and now its working properly... Thanks a lot Dinesh Sharma
Dinesh Sharma