trigger not fired when imort data

Question

Post reply

trigger not fired when imort data

raj acharya

SSCrazy

Points: 2805
More actions
March 9, 2009 at 6:44 am

#128255

hi i have created one trigger for example on table1
trigger is for insert and after insert i m doing some calculation on that data
so when i m inserting one by one data into table1 trigger runs completely
but when imporing data from another table than it bypass this trigger or trigger not fire ....
anybody know abt this strange behaviour than pls tell me
Raj Acharya

Viewing 12 posts - 1 through 11 (of 11 total)

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

JamieX Hall of Fame Points: 3763 More actions · Answer 1

I would start looking down this path

http://msdn.microsoft.com/en-us/library/ms187640(SQL.90).aspx

How are you bulk inserting the data?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Raj,

This is the third time I've seen this post and the second one I've seen that has answers. Please only post once so we don't duplicate answers across threads.

The other post that has an answer is likely your problem.

If you post the trigger code, we will be able to post more accurate solutions or suggestions.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

raj acharya SSCrazy Points: 2805 More actions · Answer 3

Hi by mistake i have do two post so i m going to unsubscribe from one ....

Raj Acharya

raj acharya SSCrazy Points: 2805 More actions · Answer 4

hi but i m not importing data through bulk insert actually ... i m doing that through import and export wizard .....

so can u pls help out me for this

Raj Acharya

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Like I asked earlier, can you post the trigger? Without seeing the code anything we say would be a guess. The main idea is that you probably are using variables in the trigger so it will only handle a single row while SQL Server works on sets. Checkout this article[/url]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

raj acharya SSCrazy Points: 2805 More actions · Answer 6

ALTER TRIGGER [dbo].[tr_insert]

ON [dbo].[table1]

AFTER INSERT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @i INT

SELECT @i = COUNT(*) FROM inserted

SELECT @i

IF (@i > 1)

BEGIN

inser into table2

select * from table1 where date not in (select date from table2)

end

IF (@i = 1 )

begin

INSERT INTO [dbo].[table1]

([ReportDate]

,[EmployeeID]

,[CreatedDate]

,[CreatedBy])

select * from inserted

end

Raj Acharya

JamieX Hall of Fame Points: 3763 More actions · Answer 7

Did you check the link I posted above? I think you need to specify the fire_triggers. Setup your Import/Export job, save to disk (do not run). Open it in visual studio and go into the data flow task. Click on the destination server and (in the Properties section) add FIRE_TRIGGERS to the "FastLoadOptions" property.

This may be your issue.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Okay, I have to ask, what are you trying to accomplish with this trigger?

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

raj acharya (3/9/2009)
hi but i m not importing data through bulk insert actually ... i m doing that through import and export wizard .....
so can u pls help out me for this

Import/Export uses either bcp or BULK INSERT (can't recall which) which does not fire triggers by default.

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

rene-500237 Hall of Fame Points: 3769 More actions · Answer 10

Hi raj

I had a same problem as you and I solved the problem in SSIS using 'Data access mode' 'Table or view' and not 'Table or view - fast load'.

I think if you use the fast load option that the trigger isn't fired

Regards

floringavrila Newbie Points: 7 More actions · Answer 11

jamie (3/9/2009)
Did you check the link I posted above? I think you need to specify the fire_triggers. Setup your Import/Export job, save to disk (do not run). Open it in visual studio and go into the data flow task. Click on the destination server and (in the Properties section) add FIRE_TRIGGERS to the "FastLoadOptions" property.
This may be your issue.

Can you explain in more details? I checked that MSDN page, I don't,actually want to use the bcp utility to import the data(tried but was unable to do so) I want to use the import wizard. So I would greatly appreciate if you would detail on how I can setup the Import/Export job file, no idea how to get there and all the other steps you mentioned in there, just presume I don't know any information on how to do that.

Thank you