INSERT/UPDATE Trigger not working in case of multiple record insert.

Hi,

I am using SQL Server 2005. I am having two tables [1Min] and [1MinCPY] as in code window and a Trigger for INSERT/UPDATE on the table [1Min]. The trigger is responsible for inserting a record in [1MinCPY] table if no record exists for Symbol and Date;  Update a record in [1MinCPY] if exists.
It handles the multiple rows updation but does not handle multiple record insert, if I insert records in a bulk manner.

I tested insertion of multiple record using following query.

INSERT INTO [1Min](Symbol,Date,Price)
SELECT 'S','04/10/2010 23:59:59',300.5
UNION
SELECT 'S','04/10/2010 23:59:59',310.5
UNION
SELECT 'S2','04/10/2010 23:59:59',100
UNION
SELECT 'S3','04/10/2010 23:59:59',200
UNION
SELECT 'S','04/10/2010 23:59:59',120
UNION
SELECT 'S','04/10/2010 23:59:59',260
UNION
SELECT 'S','04/10/2010 23:59:59',450

So, by this it should insert record for "'S','04/10/2010 23:59:59',300.5"
and update that record later on for all the rows inserted. There should be only one record in the table [1MinCPY] with values 'S','04/10/2010 23:59:59',450

Please suggest me, how to achieve this for inserting multiple records.

Thanks,
Madhav
Table [1Min]

CREATE TABLE [dbo].[1Min](
	[Symbol] [varchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
	[Date] [datetime] NOT NULL,
	[Price] [decimal](18, 4) NOT NULL
) ON [PRIMARY]

--------------------------------------------------------------------------------
Table [1MinCPY]

CREATE TABLE [dbo].[1MinCPY](
	[Symbol] [varchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
	[Date] [datetime] NOT NULL,
	[Price] [decimal](18, 4) NOT NULL
) ON [PRIMARY]

--------------------------------------------------------------------------------

Trigger on [1Min]

CREATE TRIGGER trTEST
   ON  [1Min]
   AFTER INSERT,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here

	UPDATE [1MinCPY]
		SET Price = I.Price
		FROM [1MinCPY] M1 INNER JOIN INSERTED I
			ON M1.Symbol = I.Symbol AND M1.Date = I.Date
	
	INSERT INTO [1MinCPY] (Symbol,Date,Price)
	SELECT Ins.Symbol,Ins.Date,Ins.Price
		FROM INSERTED Ins LEFT JOIN [1MinCPY] M
			ON Ins.Symbol = M.Symbol AND Ins.Date = M.Date
		WHERE M.Symbol IS NULL AND M.Date IS NULL

END

Open in new window

madhav_ghatoleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gothamiteCommented:
Hi Madhav,

The INSERT command you have supplied is one atomic transaction, so it is inserting 7 rows of data at once. The trigger code will then be given these 7 rows as it's input. It is processing all rows at once

If you take out the UNIONs from your query and simply INSERT each row one after the other you will get the effect you describe, as the trigger will be called 7 times (once per row) rather than once for all 7 rows:

INSERT INTO [1Min](Symbol,Date,Price)
SELECT 'S','04/10/2010 23:59:59',300.5
INSERT INTO [1Min](Symbol,Date,Price)
SELECT 'S','04/10/2010 23:59:59',310.5
INSERT INTO [1Min](Symbol,Date,Price)
SELECT 'S2','04/10/2010 23:59:59',100
INSERT INTO [1Min](Symbol,Date,Price)
SELECT 'S3','04/10/2010 23:59:59',200
INSERT INTO [1Min](Symbol,Date,Price)
SELECT 'S','04/10/2010 23:59:59',120
INSERT INTO [1Min](Symbol,Date,Price)
SELECT 'S','04/10/2010 23:59:59',260
INSERT INTO [1Min](Symbol,Date,Price)
SELECT 'S','04/10/2010 23:59:59',450

Open in new window

0
madhav_ghatoleAuthor Commented:
Thanks, for your response.

But, the problem in my case is that, I am using SQLBulkcopy (Datatable to table on server) in C# with the option fire trigger. In this case trigger does not work properly. Could you please suggest any idea?
0
gothamiteCommented:
I think in principle you need to have some iteration here and some stage - because as I said before, SQL is processing all of these new rows as a set. So you could either

a) stop using SQLBulkCopy and iterate over the rows individually in the c# code
b) do the bulk import into a third table and then call another stored procedure which would use a cursor to insert rows one at a time from this third table into your 1min table.
c) change the trigger to include a cursor to iterate over the input rows (but this would possibly be quite slow with lots of rows and hold the transaction open for a long time which could cause excessive locks and other performance issues) This would be my least favourite option but means you wouldn't need to touch your c# code.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

madhav_ghatoleAuthor Commented:
I have to insert 20000 rows in a table if I use option "a) stop using SQLBulkCopy and iterate over the rows individually in the c# code" it slows down the process.

I have also tried Option C), but it still slows down not as much as option a).

If I will use option b) which will have cursor in the stored procedure, wouldn't that be simillar to option c) ?, since we are going to use Cursor in stored procedure instead of in Trigger.

My problem is that, I have to insert large number of rows(20000) in a table which has a Trigger; so I want to insert rows in a faster way and trigger should work properly. Please suggest me how it can be done.
0
gothamiteCommented:
Well you could set up a Service Broker queue to update the destination table using the logic you have in your trigger, and then replace the code in the current trigger with a SEND command on the SB queue. Sending a message to a queue is very fast and non-blocking (asynchronous).
0
JR2003Commented:
You can change the trigger so that when it does the insert it inserts from the a join on max(Rownumber) and grouped by Symbol and Date CTE of the INSERTED table:
CREATE TRIGGER trTEST
   ON  [1Min]
   AFTER INSERT,UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here

    UPDATE [1MinCPY]
            SET Price = I.Price
            FROM [1MinCPY] M1 INNER JOIN INSERTED I
                    ON M1.Symbol = I.Symbol AND M1.Date = I.Date
    ;
    WITH X AS 
    (
        SELECT ROW_NUMBER() OVER (ORDER BY Rand()) AS RowNumber,
               Ins.Symbol,
               Ins.Date,
               Ins.Price
          FROM INSERTED Ins 
         WHERE NOT EXISTS(SELECT 1
                            FROM [1MinCPY] M
                           WHERE Ins.Symbol = M.Symbol 
                             AND Ins.Date = M.Date)
    )
    INSERT INTO [1MinCPY] 
    (
        Symbol,
        Date,
        Price
    )
    SELECT A.Symbol,
           A.Date,
           A.Price
      FROM X A
     INNER JOIN (SELECT X.Symbol,
                        X.Date,
                        Max(X.Rownumber) Rownumber
                   FROM X 
                  GROUP BY X.Symbol,
                           X.Date) AS B
             ON A.Symbol = B.Symbol
            AND A.Date = B.Date
            AND A.RowNumber = B.RowNumber
               
END

Open in new window

0
LowfatspreadCommented:
the code should be more like this
CREATE TRIGGER trTEST
   ON  [1Min]
   AFTER INSERT,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here

	UPDATE [1MinCPY]
		SET Price = I.Price
		FROM [1MinCPY] M1 
                INNER JOIN (select symbol,date,sum(price) as totprice 
                             from INSERTED I
                            group by symbol,date
                           ) as i  
			ON M1.Symbol = I.Symbol
                       AND M1.Date = I.Date
	
	INSERT INTO [1MinCPY] (Symbol,Date,Price)
	SELECT Ins.Symbol,Ins.Date,Ins.Price
		FROM (select symbol,date,sum(price) as totprice 
                             from INSERTED I
                            group by symbol,date
                           ) AS iNS 
                LEFT JOIN [1MinCPY] M
			ON Ins.Symbol = M.Symbol
                       AND Ins.Date = M.Date
		WHERE M.Symbol IS NULL 
END

Open in new window

0
madhav_ghatoleAuthor Commented:
Thanks, for your responses.
 
 Hi gothamite,

QUOTE
Well you could set up a Service Broker queue to update the destination table using the logic you have in your trigger, and then replace the code in the current trigger with a SEND command on the SB queue. Sending a message to a queue is very fast and non-blocking (asynchronous).
UNQUOTE

I don't have any idea about this concept. Can you please provide more details with some example. How can I achieve this in my case.

0
JR2003Commented:
Lowfatspread,
You are inserting the SUM of the price. This doesn't appear to be what the author wants from the initial question and the UPDATE statment which also does no summing.
0
LowfatspreadCommented:
jr2003

on reflection you are correct i had assumed that the price was being updated not replaced.

 Madhav
sorry
I now do not believe that there is anyway that a trigger can function for this scenario,
as there is no guarantee that the Last processed row in any multiple set can be determined (or even any order ) and this all that could be done is to set proce randomly in that scenario....

you will need to explain the business logic in detail, and what would be desired in this scenario... otherwise all a trigger can do is Stop a multiple update case from being allowed to process.

   
0
madhav_ghatoleAuthor Commented:
Thanks for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.