• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

How to create trigger that handles multiple records?

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
madhav_ghatole
Asked:
madhav_ghatole
  • 4
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
madhav_ghatoleAuthor Commented:

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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>If I have to use new variables value which I calculated
calculated how?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
madhav_ghatoleAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
madhav_ghatoleAuthor Commented:
No this code is not able to handle multiple record updates.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you explain, why?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now