February 13, 2013 at 1:04 pm
I've got a trigger (SQL 2008 R2) that does a few simple operations but the results are not logical.
A text file is fed to an SSIS package that loads the records into "RLFL" table. The Data Access Mode is set to "Table or view"
Here is the trigger for RLFL:
ALTER TRIGGER [dbo].[ArchiveBACK] ON [dbo].[RLFL]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @soNo varchar(10),@releaseDate smalldatetime
SELECT @soNo = soNo FROM INSERTED
SELECT @releaseDate=releaseDate FROM INSERTED
-- Check if BACK records were archived, if NOT...
IF(SELECT COUNT(1) FROM BACK_RLFL WHERE salesOrderNo=@soNo AND releaseDate=@releaseDate AND releaseVersion='1') = 0
-- Archive BACK Records, with version 1
INSERT INTO BACK_RLFL SELECT *, @releaseDate,'1' FROM BACK WHERE salesOrderNo=@soNo
END
Now, the BACK table has at least 3 to 4 rows for that soNo (key) that was just inserted into RLFL, however the result in BACK_RLFL after the trigger executes has only the last row from BACK.
If I manually insert a record into RLFL, I get the desired results where all four rows from BACK transfer into RLFL_BACK (exactly what the trigger is supposed to do).
Can anyone explain what's happening?
Thanks,
Mark
February 13, 2013 at 1:21 pm
That trigger assumes that any insert operation inserts one and only one row. An insert that inserts more than one row will result in weird behaviour. I would imagine that's what you're seeing.
To fix it, you need to change the trigger so that it doesn't assume there's only a single row in the inserted 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
February 13, 2013 at 1:51 pm
I was playing around with this, and came up with this as a pretty simple approach:
ALTER TRIGGER [dbo].[ArchiveBACK] ON [dbo].[RLFL]
AFTER INSERT
AS
SET NOCOUNT ON;
INSERT INTO BACK_RLFL
SELECT * FROM INSERTED
EXCEPT
SELECT * FROM BACK_RLFL
;
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
February 14, 2013 at 10:14 am
That does not work.
Here are my table layouts:
BACK Table:
OrderID ItemNo
--------- ---------
9813 1
9813 2
9813 3
9817 1
RLFL gets one record inserted which is OrderID 9813
Trigger is supposed to fire:
INSERT INTO BACK_ARCHIVE SELECT * FROM BACK WHERE OrderID=INSERTED.OrderID
Pretty simple so far...
The results I get in my BACK_ARCHIVE (identical layout to BACK) Table are
OrderId ItemNo
--------- ----------
9813 3
Note, it's always the LAST item that gets recorded int BACK_ARCHIVE.
WHY? I need all of them in BACK_ARCHIVE.
I believe it has something to do with the way SSIS processes it using "OLE DB Destination" because if I insert a record into RLFL manually, the trigger does exactly what it's supposed to do and inserts all 3 records from BACK.
I'm thinking of an sp, but i'd rather not add another level of complexity for something so trivial, supposedly.
How can I accomplish this?
Thanks.
February 14, 2013 at 10:18 am
Mark-545947 (2/14/2013)
Note, it's always the LAST item that gets recorded int BACK_ARCHIVE.WHY? I need all of them in BACK_ARCHIVE.
As I said earlier
That trigger assumes that any insert operation inserts one and only one row (the selecting of a row into a variable). An insert that inserts more than one row will result in weird behaviour (because it will only process one of the rows in the inserted table).
I believe it has something to do with the way SSIS processes it using "OLE DB Destination" because if I insert a record into RLFL manually, the trigger does exactly what it's supposed to do and inserts all 3 records from BACK.
If you insert a single row it will work as expected. If you insert multiple rows it will not work as expected.
How can I accomplish this?
To fix it, you need to change the trigger so that it doesn't assume there's only a single row in the inserted 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
February 14, 2013 at 10:45 am
Mark-545947 (2/14/2013)
That does not work.
Sure it does for the situation of archiving what is not already there, but now you've introduced a new table, BACK_ARCHIVE. So I see four tables: BACK, RLFL, RLFL_BACK, and BACK_ARCHIVE. Can you describe what the process is between these tables? Which one is first and what is the trigger supposed to do after that?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
February 14, 2013 at 10:50 am
I don't understand.
I only have one row that I'm processing in SSIS. Just like I'd be processing one row with an INSERT statement from SSMS (which works flawlessly).
How do I tell the trigger that there are more than one record being inserted?
Thanks
February 14, 2013 at 10:52 am
Sure it does for the situation of archiving what is not already there, but now you've introduced a new table, BACK_ARCHIVE. So I see four tables: BACK, RLFL, RLFL_BACK, and BACK_ARCHIVE. Can you describe what the process is between these tables? Which one is first and what is the trigger supposed to do after that?
No, to make it easier I just renamed the tables.
Target table is RLFL.
LIVE table BACK
Archive is BACK_RLFL
I'm just so confused with this issue. There are only 3 tables involved. Sorry for the confusion.
I simplified the process and described it in the 4th post.
February 14, 2013 at 12:13 pm
Mark-545947 (2/14/2013)
How do I tell the trigger that there are more than one record being inserted?
There's no 'telling the trigger' anything. As far as SQL is concerned, a trigger works on sets of rows, it's not something that's called per-row.
This portion of your trigger will only behave correctly if there's only one row in the inserted table
DECLARE @soNo varchar(10),@releaseDate smalldatetime
SELECT @soNo = soNo FROM INSERTED
SELECT @releaseDate=releaseDate FROM INSERTED
If there's more than one row in Inserted (as there will automatically be if the insert affects more than one row), those selects will fetch one row's data. Which one is not defined, but it will only be one.
You must write your trigger so that it can handle multiple rows in the inserted table, not assume there's one row and assign it to variables.
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
February 14, 2013 at 12:22 pm
Mark-545947 (2/14/2013)
Sure it does for the situation of archiving what is not already there, but now you've introduced a new table, BACK_ARCHIVE. So I see four tables: BACK, RLFL, RLFL_BACK, and BACK_ARCHIVE. Can you describe what the process is between these tables? Which one is first and what is the trigger supposed to do after that?
No, to make it easier I just renamed the tables.
Target table is RLFL.
LIVE table BACK
Archive is BACK_RLFL
I'm just so confused with this issue. There are only 3 tables involved. Sorry for the confusion.
I simplified the process and described it in the 4th post.
I've attempted to take your logic from the trigger and make into a trigger that doesn't rely on variables. It looks like an insert to RLFL will only be written to BACK_RLFL if there is already a matching OrderID in BACK.
See if this makes sense to you.
create table RLFL (OrderID int, ItemNo int);
GO
create table BACK (OrderID int, ItemNo int);
GO
create table BACK_RLFL (OrderID int, ItemNo int);
GO
CREATE TRIGGER [dbo].[ArchiveBACK] ON [dbo].[RLFL]
AFTER INSERT
AS
SET NOCOUNT ON;
INSERT INTO BACK_RLFL
SELECT b.*
FROM BACK b
INNER JOIN INSERTED i on b.OrderID = i.OrderIDGO
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
February 14, 2013 at 12:28 pm
This makes more sense:
CREATE TRIGGER [dbo].[ArchiveBACK] ON [dbo].[RLFL]
AFTER INSERT
AS
SET NOCOUNT ON;
INSERT INTO BACK_RLFL
SELECT b.*
FROM BACK b
WHERE b.OrderID in (select i.OrderID from inserted i)
GO
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
February 14, 2013 at 12:31 pm
I understand your point of trigger firing on batches and not on rows. Then why doesn't my trigger work when I insert into it ONE record (FROM SSIS).
Here is my modified trigger:
ALTER TRIGGER [dbo].[ArchiveBACK] ON [dbo].[RLFL]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.BACK_RLFL SELECT *,'','' FROM dbo.BACK WHERE OrderID='A121114541'
END
Very simple, I'm inserting one record into RLFL and all that the trigger needs to do is perform the operation on completely different tables.
Why am I still getting 1 record in BACK_RLFL but if I do the insert from SSMS i get all of them and everything works fine. In both cases I am inserting 1 record into RLFL. my batch consists of 1 record. trigger fires once.
???
February 14, 2013 at 12:59 pm
Mark-545947 (2/14/2013)
I understand your point of trigger firing on batches and not on rows. Then why doesn't my trigger work when I insert into it ONE record (FROM SSIS).Here is my modified trigger:
ALTER TRIGGER [dbo].[ArchiveBACK] ON [dbo].[RLFL]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.BACK_RLFL SELECT *,'','' FROM dbo.BACK WHERE OrderID='A121114541'
END
Very simple, I'm inserting one record into RLFL and all that the trigger needs to do is perform the operation on completely different tables.
Why am I still getting 1 record in BACK_RLFL but if I do the insert from SSMS i get all of them and everything works fine. In both cases I am inserting 1 record into RLFL. my batch consists of 1 record. trigger fires once.
???
That is something different. Would there be any triggers on BACK or BACK_RLFL at all?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
February 14, 2013 at 1:01 pm
No triggers anywhere else.
It has to be some setting issue in SSIS otherwise, why would it work from SSMS?
This is driving me crazy.
February 14, 2013 at 1:06 pm
Mark-545947 (2/14/2013)
No triggers anywhere else.It has to be some setting issue in SSIS otherwise, why would work from SSMS.
This is driving me crazy.
Take the BACK tables out of the equation. Run this and then run the insert from SSIS and then SSMS.
Create table Track_Inserts (NumInserts int, insert_date datetime);
GO
ALTER TRIGGER [dbo].[ArchiveBACK] ON [dbo].[RLFL]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Track_Inserts select (SELECT count(*) from inserted), getdate()
END
GO
select * from Track_Inserts
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply