October 6, 2008 at 7:49 am
Hello All:
I have an AFTER INSERT trigger that fires when new rows are added to a table.
The trigger checks for the existence of other records and does some processing.
When rows are inserted one at a time (i.e. INSERT(...) VALUES(...)), everything works great.
However, when multiple rows are inserted at a time (i.e. INSERT(...) SELECT...), the trigger does not evaluate things properly. I know that this is happening because the trigger only fires once ALL rows have been inserted.
I need the trigger to fire after EACH row has been inserted.
Is this possible?
Any thoughts / insights would be greatly appreciated.
Regards,
-Simon
October 6, 2008 at 8:28 am
if an insert operation inserts more than one row, the INSERT trigger will be fired only once. Your code inside the trigger should be written such that it can handle multiple rows.
If you dont have that, try one of the following.
1. Write a loop inside the trigger and call your code/sp and pass each row to process [WARNING: row-by-row processing may not be a good idea most of the times]
2. Rewrite your processing logic so that you can do a batch process rather than a row-by-row processing.
.
October 6, 2008 at 9:38 am
jacob hit the nail on the head; chances are , if you can post the trigger here, we can show you how to re-write it so it handles multiple rows gracefully;
as a rule of thumb, if you declared a variable in a trigger, you tackled it wrong, because you were assuming a single row of data... here's a loosy goosy example while we wait for more info:
if you were doing conditional processing, like
if @thisfield = 1
insert into invoicetable
else
[do something else]
you should be able to do everything you need to do with set based logic, ie
INSERT INTO INVOICETABLE
SELECT FROM INSERTED WHERE THISFIELD = 1
-- everything that fit the first condition, not just one row any more
INSERT INTO OTHERTABLE
SELECT FROM INSERTED WHERE THISFIELD <> 1
Lowell
October 6, 2008 at 10:07 am
Thanks so much, Jacob and Lowell, for your input.
Here is the code for my trigger - hopefully it will help to clarify what's happening:
-- INSERT Trigger on FileTransfer, to insert or update a row into the VelocityDataLog table.
USE OE
GO
/* Drop the trigger if it already exists */
IF EXISTS (SELECT 1 FROM sysobjects WHERE [name] = 'trg_FileTransfer_OnInsert')
BEGIN
DROP TRIGGER trg_FileTransfer_OnInsert
END
GO
CREATE TRIGGER trg_FileTransfer_OnInsert
ON FileTransfer
AFTER INSERT
AS
-- If no row exists for that PeriodID, ClassID, or WholesalerID...
IF NOT EXISTS
(
SELECT *
FROM VelocityDataLog AS VDL
INNER JOIN INSERTED AS I
ON VDL.PeriodID = I.PeriodID AND VDL.ClassID = I.ClassID AND VDL.WholesalerID = I.WholesalerID
)
-- ... then INSERT a new row.
BEGIN
INSERT VelocityDataLog
(
WholesalerID,
PromotionAbr,
PeriodID,
ClassID,
VelocityDataLogStatusID,
DateReceived,
DateProcessed,
Notes
)
SELECT
WholesalerID,
PromotionAbr,
PeriodID,
ClassID,
1,
DateAdded,
NULL,
'Received via website.'
FROM INSERTED
WHERE
INSERTED.FileTypeID = 1
PRINT LTRIM(STR(@@ROWCOUNT)) + ' Rows(s) inserted into the VelocityDataLog table, based on an INSERT into the FileTransfer table.'
RETURN
END
-- If a row exists for that PeriodID, ClassID, and WholesalerID, and VelocityDataLogStatusID = 1...
IF EXISTS
(
SELECT *
FROM VelocityDataLog AS VDL
INNER JOIN INSERTED AS I
ON VDL.PeriodID = I.PeriodID AND VDL.ClassID = I.ClassID AND VDL.WholesalerID = I.WholesalerID
WHERE VDL.VelocityDataLogStatusID = 1
)
-- ... then UPDATE that existing row.
BEGIN
UPDATE VDL
SET VDL.VelocityDataLogStatusID = 1, VDL.DateReceived = I.DateAdded, VDL.Notes = 'Received via website (UPDATED).'
FROM VelocityDataLog AS VDL
INNER JOIN INSERTED AS I
ON VDL.PeriodID = I.PeriodID AND VDL.ClassID = I.ClassID
WHERE I.FileTypeID = 1
PRINT LTRIM(STR(@@ROWCOUNT)) + ' Row(s) updated in the VelocityDataLog table, where there was a row for that period, class, and wholesaler, and its status was ''Received''...'
RETURN
END
Basically, there are two sections to the trigger:
Scenario 1) If no row exists based on the join criteria, do an INSERT
Scenario 2) If a row exists based on the join criteria and the VelocityDataLogStatusID = 1, do an UPDATE on the row added in Scenario 1
The problem is, when multiple rows are added at the same time via an INSERT...SELECT statement, the business logic in Scenario 2 never gets a chance to run.
Thanks again for your help - it's much appreciated.
-Simon
October 6, 2008 at 10:31 am
This is pretty easy - just make the insert SET-Based:
[font="Courier New"]CREATE TRIGGER trg_FileTransfer_OnInsert
ON FileTransfer
AFTER INSERT
AS
/* Update all existing records - do this first so you don't update newly inserted records */
UPDATE
V
SET
V.VelocityDataLogStatusID = 1
, V.DateReceived = I.DateAdded
, V.Notes = 'Received via website (UPDATED).'
FROM
VelocityDataLog V
INNER JOIN INSERTED AS I
ON V.PeriodID = I.PeriodID AND V.ClassID = I.ClassID
WHERE I.FileTypeID = 1
/* Insert All New Records */
INSERT VelocityDataLog
(
WholesalerID,
PromotionAbr,
PeriodID,
ClassID,
VelocityDataLogStatusID,
DateReceived,
DateProcessed,
Notes
)
SELECT
I.WholesalerID,
I.PromotionAbr,
I.PeriodID,
I.ClassID,
1,
I.DateAdded,
NULL,
'Received via website.'
FROM INSERTED I
LEFT JOIN VelocityDataLog V ON V.PeriodID = I.PeriodID AND V.ClassID = I.ClassID AND V.WholesalerID = I.WholesalerID
WHERE
I.FileTypeID = 1
AND V.PeriodID IS NULL --VelocityDataLog Does not exist
--Print statement removed - don't print stuff in a trigger[/font]
October 6, 2008 at 12:05 pm
Thank you for your post, Michael, but unfortunately the code you suggested didn't produce the desired result.
When I implemented your code into the trigger and ran an INSERT...SELECT statement against the table, the trigger produced two new rows in the VelocityDataLog table, instead of doing what I would like it to do, namely:
1) INSERT the first row based on criteria ABC
2) UPDATE that first row if another row that matches criteria ABC is inserted
I think I will have to re-write the SP that inserts the rows into the triggered table, to insert them one at a time (ouch - I know.).
Unless anyone else can suggest something?....
Thanks very much to everyone.
Regards,
-Simon
October 6, 2008 at 12:18 pm
Two questions.
Why do you only want to insert/update the first row if there are multiple rows in the insert?
How do you define 'first row'? SQL doesn't put any meaning on the order or rows unless there's an order by statement.
Can you give us an example of rows that will be inserted and what should be inserted into the DataLog table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2008 at 12:57 pm
Gail:
Thanks for getting involved, and thanks to anyone else who still might be playing along at home...
I've been trying to simplify things, but I'll explain in a little more detail what exactly is going on, to answer your questions properly.
FileTransfer is the name of the table that the trigger is being built on.
There are actually two FileTransfer tables.
The first is a FileTransfer table that resides on a MySQL server.
It stores rows that represent files that have been uploaded to us via our website.
Each time a file is uploaded via our website, a row is written via PHP to that MySQL table.
Every hour an SP runs that transfers rows from the MySQL FileTransfer table to the SQL Server 2005 FileTransfer table. That SP runs nicely, thanks to a script I found on this site for configuring MySQL servers as linked servers in SQL Server 2005.
If a client uploads a certain type of file via our website, it means that they are participating in a promotion that we run, for that period.
The VelocityDataLog table tracks which clients (Wholesalers) are participating in which period.
If we receive a certain kind of file (one with FileTypeID = 1 in the FileTransfer table), we want a row added to the VelocityDataLog table to indicate their participation.
However, clients sometimes upload more than one of the same file for a promotional period. So, to answer your first question, as to why we only want to insert the first row - we don't want the VelocityDataLog table to show more than one participation record for that client in that period. We don't count how many times they participated in the period - only that they participated.
As for your question about how we define the "first" row: it is the first instance of a row where FileTypeID = 1, for that wholesaler, period, and class. If a client uploads more than one file with that type, it doesn't matter which row is considered the "first" one.
As for what the data looks like, I've attached JPGs of the two tables.
However, I think I've found a solution to my own problem, one that doesn't involve RBAR implementation.
Please let me know what you think, and if you see a better solution.
Basically, I'm using a SELECT DISTINCT in my INSERT statement, to make sure that 2+ rows don't get created for the same Wholesaler in the same Period, in the same Class.
-- INSERT Trigger on FileTransfer, to insert or update a row into the VelocityDataLog table.
USE OE
GO
/* Drop the trigger if it already exists */
IF EXISTS (SELECT 1 FROM sysobjects WHERE [name] = 'trg_FileTransfer_OnInsert')
BEGIN
DROP TRIGGER trg_FileTransfer_OnInsert
END
GO
CREATE TRIGGER trg_FileTransfer_OnInsert
ON FileTransfer
AFTER INSERT
AS
-- If no row exists for that PeriodID, ClassID, or WholesalerID...
IF NOT EXISTS
(
SELECT *
FROM VelocityDataLog AS VDL
INNER JOIN INSERTED AS I
ON VDL.PeriodID = I.PeriodID AND VDL.ClassID = I.ClassID AND VDL.WholesalerID = I.WholesalerID
)
-- ... then INSERT a new row.
BEGIN
INSERT VelocityDataLog
(
WholesalerID,
PromotionAbr,
PeriodID,
ClassID,
VelocityDataLogStatusID,
DateReceived,
DateProcessed,
Notes
)
SELECT DISTINCT
I.WholesalerID,
I.PromotionAbr,
I.PeriodID,
I.ClassID,
1,
getdate(),
NULL,
'Received via website.'
FROM INSERTED AS I
WHERE
I.FileTypeID = 1
RETURN
END
-- If a row exists for that PeriodID, ClassID, and WholesalerID, and VelocityDataLogStatusID = 1...
IF EXISTS
(
SELECT *
FROM VelocityDataLog AS VDL
INNER JOIN INSERTED AS I
ON VDL.PeriodID = I.PeriodID AND VDL.ClassID = I.ClassID AND VDL.WholesalerID = I.WholesalerID
WHERE VDL.VelocityDataLogStatusID = 1
)
-- ... then UPDATE that existing row.
BEGIN
UPDATE VDL
SET VDL.VelocityDataLogStatusID = 1, VDL.DateReceived = I.DateAdded, VDL.Notes = 'Received via website (UPDATED).'
FROM VelocityDataLog AS VDL
INNER JOIN INSERTED AS I
ON VDL.PeriodID = I.PeriodID AND VDL.ClassID = I.ClassID
WHERE I.FileTypeID = 1
RETURN
END
October 6, 2008 at 2:04 pm
That should work.
Just one thing, you don't need the second if exists. Just put the update in the else part of the if.
As it's currently written, the second if exists will always return true, because it's going to pick up the rows you've just inserted.
It's a bit of a waste to check existence twice and then update that which you just inserted.
So
If Exists (...)
UPDATE ...
ELSE
INSERT ...
SELECT DISTINCT ....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2008 at 2:18 pm
You're right - thanks for the help, Gail, and others.
-Simon
October 6, 2008 at 3:40 pm
Good Evening,
I still have some concerns with the queries.
The first IF statement checks if any of the rows is already available in your log table.
So if two Wholesalers have submitted files and only one is available in the log table, your EXISTs statement will return TRUE and will therefore not add the missing Wholesaler to the log table.
Secondly, the second update statement seems to randomly update multiple rows at the same time. You probably want the MAX date for the key combination.
The Wholesaler should probably be part of the key and added to the WHERE clause.
The PromotionAbr is also unclear (part of the key?)
-- Update existing rows first
UPDATE VDL
SET VDL.VelocityDataLogStatusID = 1
,VDL.DateReceived = MaxDateAdded
,VDL.Notes = 'Received via website (UPDATED).'
FROM (
SELECT PeriodID, ClassID, WholesalerID ,MAX(DateAdded) MaxDateAdded
FROM INSERTED
WHERE FileTypeID = 1
GROUP BY PeriodID, ClassID, WholesalerID
) I
JOIN VelocityDataLog VDL ON VDL.PeriodID = I.PeriodID AND VDL.ClassID = I.ClassID AND VDL.WholesalerID = I.WholesalerID
GO
-- Now insert new rows for key combinations not in the destination table.
INSERT VelocityDataLog (WholesalerID, PromotionAbr, PeriodID, ClassID, VelocityDataLogStatusID, DateReceived, DateProcessed, Notes)
SELECT I.WholesalerID,
MAX(I.PromotionAbr), -- ??? Is this part of the key?
I.PeriodID,
I.ClassID,
1,
getdate(),
NULL,
'Received via website.'
FROM INSERTED AS I
WHERE I.FileTypeID = 1
AND NOT EXISTS(
SELECT NULL FROM VelocityDataLog VDL
WHERE VDL.PeriodID = I.PeriodID
AND VDL.ClassID = I.ClassID
AND VDL.WholesalerID = I.WholesalerID
)
GROUP BY I.WholesalerID
,I.PeriodID
,I.ClassID
EDIT: Un-Tabbed code.
Best Regards,
Chris Büttner
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply