[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to create trigger that handles multiple records?

Posted on 2009-04-27
7
Medium Priority
?
203 Views
Last Modified: 2012-08-14
I have two tables A and B.
I want to create a trigger on table A for insert, update that should handle multiple records.

In the trigger I have some variable which I calculated as I want from the fields of Table A.

Now I want to create a trigger that will insert or update the Table B using  available variable int the trigger. The trigger should take care of the following cases.

1. If record for the ID (ID is the primary key in Table B) in Table B is available then it updates some fields in Table B.
2. If record for the ID (ID is the primary key in Table B) in Table B is not available then insert new row in Table B.


How do I write such type of trigger in SQL server 2005?

Thanks.
0
Comment
Question by:madhav_ghatole
  • 4
  • 3
7 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 24240288
simple, here is the trigger body:
UPDATE b 
   SET b.somefield = i.somefield
  FROM tableB b
  JOIN INSERTEd i
    ON i.ID = b.ID 
INSERT INTO TableB (ID, somefield)
 SELECT i.ID, i.somefield
   FROM INSERTEd i
   LEFT JOIN TableB b
     ON i.ID = b.ID 
  WHERE b.ID IS NULL

Open in new window

0
 

Author Comment

by:madhav_ghatole
ID: 24240333

If I have to use new variables value which I calculated instead of fields from the inserted  i.somefield then how to do it?

i. e. @Var1 which has result. Its not the column or field of the table A.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24240841
>If I have to use new variables value which I calculated
calculated how?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:madhav_ghatole
ID: 24240948
I have a trigger code as below. How can I convert this so it can handle mutliple records?

Thank you.
ALTER TRIGGER [dbo].[trTest] ON [dbo].[Table1] 
FOR INSERT, UPDATE
AS
BEGIN
 
DECLARE @F1 int
DECLARE @F2 int
DECLARE @F3 int
DECLARE @F4 int
DECLARE @F5 int
DECLARE @F6 int
DECLARE @F7 int
DECLARE @F8 int
DECLARE @F9 int
 
DECLARE @F10 datetime 
DECLARE @F11 char(32)
DECLARE @F12 datetime 
DECLARE @F13  datetime
DECLARE @F14 datetime
DECLARE @F15 datetime
DECLARE @F16 datetime
DECLARE @F17 varchar
 
DECLARE @F18  int
DECLARE @F19  datetime
 
SET @F18 = (SELECT COUNT(ID) FROM INSERTED WHERE ID LIKE 'COID%')
IF @F18 = 0 
BEGIN
	SET @F11=(SELECT Symbol FROM INSERTED)
	SET @F13 =(SELECT DATETODAYS FROM INSERTED)
	SET @F14=(SELECT DATEYESTERDAYS FROM INSERTED)
	SET @F15 =(SELECT DT1 FROM INSERTED)
	SET @F16 =(SELECT DT2 FROM INSERTED)
 
SET @F19=0
SET @F18 = (SELECT COUNT(CDATE) FROM Table3 WHERE (Table3.Symbol=@F11) AND (CDATE <= @F14))
 
IF @F18 >=1
BEGIN
SET @F19=(SELECT MAX(CDATE) FROM Table3 WHERE (Table3.Symbol=@F11) AND CDATE <= @F14)
END
 
IF NOT EXISTS (SELECT DISTINCT(Table3.SYMBOL) FROM Table3 WHERE (Table3.Symbol=@F11) AND (CDate = @F19 ))
BEGIN
	SET @F1=0
	SET @F4=0
	SET @F7=0
END
ELSE
BEGIN
	SET @F1=(SELECT BuyCount FROM Table3 WHERE(Table3.Symbol=@F11) AND (CDate=@F19))
	SET @F4=(SELECT SellCount FROM Table3 WHERE(Table3.Symbol=@F11) AND (CDate=@F19))
	SET @F7=(@F1-@F4)
END
 
SET @F18=(SELECT count(ID)  FROM Table1 WHERE (TimeUpdated BETWEEN @F15 AND @F16) AND (SIDE=1) AND (SymbolName=@F11) AND (IsDeleted=0))
IF @F18 = 0
BEGIN
	SET @F2 = 0
END
ELSE
BEGIN
	SET @F2=(SELECT SUM(QuantityExecuted)  FROM Table1 WHERE (TimeUpdated BETWEEN @F15 AND @F16) AND (SIDE=1) AND (SymbolName=@F11) AND (IsDeleted=0))   
END
 
SET @F18=(SELECT count(ID)  FROM Table1 WHERE (TimeUpdated BETWEEN @F15 AND @F16) AND (SIDE=2) AND (SymbolName=@F11) AND (IsDeleted=0))
IF @F18 = 0
BEGIN
	SET @F5 = 0
END
ELSE
BEGIN
	SET @F5=(SELECT SUM(QuantityExecuted)  FROM Table1 WHERE (TimeUpdated BETWEEN @F15 AND @F16) AND (SIDE=2) AND (SymbolName=@F11) AND (IsDeleted=0))   
END
	SET @F8=(@F2 - @F5)   
 
SET @F3 = @F1 + @F2
SET @F6 = @F4 + @F5
SET @F9 = @F3 - @F6
 
IF NOT EXISTS (SELECT F9.Symbol FROM F9 WHERE F9.Symbol=@F11)
BEGIN
	INSERT INTO F9(Symbol,BuyCount,SellCount,Cposition) 
	VALUES(@F11, @F3,@F6,@F9)
END
ELSE
BEGIN
	  UPDATE F9
	  Set
  		BuyCount =(@F3),
		SellCount =(@F6),
		CPosition=(@F9)
	   WHERE Symbol =@F11
END
 
IF NOT EXISTS (SELECT Table3.Symbol FROM Table3 WHERE (Table3.Symbol=@F11) AND (CDate=@F13))
BEGIN
	INSERT INTO Table3(Symbol,BuyCount,SellCount,Cposition,CDate) 
	VALUES (@F11, @F3,@F6,@F9,@F13)
END
ELSE
BEGIN
 	UPDATE Table3
  	Set
		BuyCount =(@F3),
		SellCount =(@F6),
		CPosition=(@F9)
	 WHERE (Symbol =@F11) AND (CDate=@F13)
END
 
END
 
END

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24240992
note: naming your variables @F1, @f2 etc is a bad choice, it makes the code unreadable !!!

anyhow: your logic seems to be difficultly transposable into set-based, so I would suggest something like this:
ALTER TRIGGER [dbo].[trTest] ON [dbo].[Table1] 
FOR INSERT, UPDATE
AS
BEGIN
 
DECLATE @INSERTED TABLE (  Symbol char(32) )
 
INSERT INTO @INSERTED SELECT Symbol FROM INSERTED
 
WHILE @@ROWCOUNT > 0
BEGIN
  SELECT TOP 1 @F11 = Symbol FROM @INSERTED
  
  IF @@ROWCOUNT > 0
  BEGIN
DECLARE @F1 int
DECLARE @F2 int
DECLARE @F3 int
DECLARE @F4 int
DECLARE @F5 int
DECLARE @F6 int
DECLARE @F7 int
DECLARE @F8 int
DECLARE @F9 int
 
DECLARE @F10 datetime 
DECLARE @F11 char(32)
DECLARE @F12 datetime 
DECLARE @F13  datetime
DECLARE @F14 datetime
DECLARE @F15 datetime
DECLARE @F16 datetime
DECLARE @F17 varchar
 
DECLARE @F18  int
DECLARE @F19  datetime
 
SET @F18 = (SELECT COUNT(ID) FROM INSERTED WHERE ID LIKE 'COID%')
IF @F18 = 0 
BEGIN
 
	SELECT @F13 = DATETODAYS 
              , @F14 = DATEYESTERDAYS 
              , @F15 = DT1 
              , @F16 = DT2 
           FROM INSERTED 
          WHERE symbol = @F11
 
SET @F19=0
SET @F18 = (SELECT COUNT(CDATE) FROM Table3 WHERE (Table3.Symbol=@F11) AND (CDATE <= @F14))
 
IF @F18 >=1
BEGIN
SET @F19=(SELECT MAX(CDATE) FROM Table3 WHERE (Table3.Symbol=@F11) AND CDATE <= @F14)
END
 
IF NOT EXISTS (SELECT DISTINCT(Table3.SYMBOL) FROM Table3 WHERE (Table3.Symbol=@F11) AND (CDate = @F19 ))
BEGIN
	SET @F1=0
	SET @F4=0
	SET @F7=0
END
ELSE
BEGIN
	SET @F1=(SELECT BuyCount FROM Table3 WHERE(Table3.Symbol=@F11) AND (CDate=@F19))
	SET @F4=(SELECT SellCount FROM Table3 WHERE(Table3.Symbol=@F11) AND (CDate=@F19))
	SET @F7=(@F1-@F4)
END
 
SET @F18=(SELECT count(ID)  FROM Table1 WHERE (TimeUpdated BETWEEN @F15 AND @F16) AND (SIDE=1) AND (SymbolName=@F11) AND (IsDeleted=0))
IF @F18 = 0
BEGIN
	SET @F2 = 0
END
ELSE
BEGIN
	SET @F2=(SELECT SUM(QuantityExecuted)  FROM Table1 WHERE (TimeUpdated BETWEEN @F15 AND @F16) AND (SIDE=1) AND (SymbolName=@F11) AND (IsDeleted=0))   
END
 
SET @F18=(SELECT count(ID)  FROM Table1 WHERE (TimeUpdated BETWEEN @F15 AND @F16) AND (SIDE=2) AND (SymbolName=@F11) AND (IsDeleted=0))
IF @F18 = 0
BEGIN
	SET @F5 = 0
END
ELSE
BEGIN
	SET @F5=(SELECT SUM(QuantityExecuted)  FROM Table1 WHERE (TimeUpdated BETWEEN @F15 AND @F16) AND (SIDE=2) AND (SymbolName=@F11) AND (IsDeleted=0))   
END
	SET @F8=(@F2 - @F5)   
 
SET @F3 = @F1 + @F2
SET @F6 = @F4 + @F5
SET @F9 = @F3 - @F6
 
IF NOT EXISTS (SELECT F9.Symbol FROM F9 WHERE F9.Symbol=@F11)
BEGIN
	INSERT INTO F9(Symbol,BuyCount,SellCount,Cposition) 
	VALUES(@F11, @F3,@F6,@F9)
END
ELSE
BEGIN
	  UPDATE F9
	  Set
  		BuyCount =(@F3),
		SellCount =(@F6),
		CPosition=(@F9)
	   WHERE Symbol =@F11
END
 
IF NOT EXISTS (SELECT Table3.Symbol FROM Table3 WHERE (Table3.Symbol=@F11) AND (CDate=@F13))
BEGIN
	INSERT INTO Table3(Symbol,BuyCount,SellCount,Cposition,CDate) 
	VALUES (@F11, @F3,@F6,@F9,@F13)
END
ELSE
BEGIN
 	UPDATE Table3
  	Set
		BuyCount =(@F3),
		SellCount =(@F6),
		CPosition=(@F9)
	 WHERE (Symbol =@F11) AND (CDate=@F13)
END
 
END
 
  END -- end IF
  DELETE @INSERTED WHERE Symbol = @F11
 END -- while @@rowcount
 
END

Open in new window

0
 

Author Comment

by:madhav_ghatole
ID: 24241752
No this code is not able to handle multiple record updates.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24241835
can you explain, why?
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question