Weird Behavior of Trigger

Question

Weird Behavior of Trigger

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

How are you inserting from SSIS? If using one of the bulk load options, have you set 'fire triggers' property on?

p.s. Is hardcoding an OrderID in the trigger going to work?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mark_1820 Old Hand Points: 315 More actions · Answer 2

GilaMonster (2/14/2013)
How are you inserting from SSIS? If using one of the bulk load options, have you set 'fire triggers' property on?
p.s. Is hardcoding an OrderID in the trigger going to work?

There is no setting to set trigger on. That option is only available when using SQL Server Destination, but I am using "OLE DB Destination" with "Data table access mode" set to "table or view" which allows triggers to fire.

The trigger does fire, and it does honor the hard code, but it just produces weird results

Mark_1820 Old Hand Points: 315 More actions · Answer 3

Both SSMS and SSIS reported 1 record which is the correct result.

What's next?

toddasd (2/14/2013)
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

toddasd SSCertifiable Points: 6298 More actions · Answer 4

Mark-545947 (2/14/2013)
Both SSMS and SSIS reported 1 record which is the correct result.
What's next?

So the trigger itself runs correctly from both sources. And there are no triggers on the tables BACK or BACK_RLFL. The only part left is the guts of the trigger.

What does this do when you run it alone in SSMS?

INSERT INTO dbo.BACK_RLFL SELECT *,'','' FROM dbo.BACK WHERE OrderID='A121114541'

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

Mark_1820 Old Hand Points: 315 More actions · Answer 5

toddasd (2/14/2013)
So the trigger itself runs correctly from both sources. And there are no triggers on the tables BACK or BACK_RLFL. The only part left is the guts of the trigger.
What does this do when you run it alone in SSMS?
INSERT INTO dbo.BACK_RLFL SELECT *,'','' FROM dbo.BACK WHERE OrderID='A121114541'

The trigger runs correctly in a way that it processes one row from each source, but the results are different. Running the query from SSMS will give me 3 items in BACK_RLFL, BUT if executed from SSIS I'll get only ONE item in BACK_RLFL

toddasd SSCertifiable Points: 6298 More actions · Answer 6

Mark-545947 (2/14/2013)
toddasd (2/14/2013)
So the trigger itself runs correctly from both sources. And there are no triggers on the tables BACK or BACK_RLFL. The only part left is the guts of the trigger.
What does this do when you run it alone in SSMS?
INSERT INTO dbo.BACK_RLFL SELECT *,'','' FROM dbo.BACK WHERE OrderID='A121114541'
The trigger runs correctly in a way that it processes one row from each source, but the results are different. Running the query from SSMS will give me 3 items in BACK_RLFL, BUT if executed from SSIS I'll get only ONE item in BACK_RLFL

Well, we verified that the trigger fires only once with one row from each source. So the problem has nothing to do with the number of executions or the number of rows. Please post your exact insert statement you use when testing in SSMS and the text file contents that the SSIS is grabbing.

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

Mark_1820 Old Hand Points: 315 More actions · Answer 7

toddasd (2/14/2013)
Well, we verified that the trigger fires only once with one row from each source. So the problem has nothing to do with the number of executions or the number of rows. Please post your exact insert statement you use when testing in SSMS and the text file contents that the SSIS is grabbing.

Exactly. It has nothing to do with number of recs I insert b/c when my file has more than one order # the archive file still has one of each and NOT the entire order for each inserted order.

SSMS execution:

INSERT INTO BACK_RLFL SELECT *,'','' FROM BACK WHERE salesOrderNo='A121114541'

SSIS Text file (order # is the second delimiter):

4124|A121114541|12|SKU239567-3|4163.0||RELEASED|02/14/13 08:53:27|||bms3|||||||||||