Trying to import a text file via a trigger

Question

Trying to import a text file via a trigger

Di Strapps

SSC Rookie

Points: 32
More actions
May 5, 2004 at 9:13 am

#108236

Hello there, I'm new to this forum, and also to SQL Server.
I'm trying to customise a (compiled) third-party VB application which allows me to create forms to update tables I've created.
I need to allow users to enter a filename (amongst other things) into this form, and the application to go away and load this file into another table, validate it, then process the data.
I set up a trigger on the table my form updates, which executes a procedure. This does a bulk insert from the file to the table. I read somewhere that this should work as long as it was an instead of trigger (which it is).
However, I get the error 'The BULK INSERT statement is not allowed within a trigger'.
An ideas where I'm going wrong? Can't I do a bulk insert within any kind of trigger? Or is there another way I can do this without creating a separate application to do it?
Thanks,
Di.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Matt Miskinnis SSC-Addicted Points: 461 More actions · Answer 1

The purpose of a trigger is generally used to keep business rules set (validation on updates, inserts, deletions). I dont believe its purpose is for calling stored procedures (but then again I've never tried). I have also had many problems trying to use the bulk insert. I would suggest trying to use the DTS instead as it may be easier then apply an agent that can be called to run the DTS package (but I dont know if triggers allow you to call an agent either). If your heart is set on using the Bulk Copy Program (BCP) my suggestion is to first test it in the query analyzer to make sure the bulk insert works correctly as I have had so much difficulty trying to use it that I have given up on it completely.

gljjr SSChampion Points: 10080 More actions · Answer 2

Basically what I would do in this situation is to create a JobRequest table that holds the information needed to call the SP that does the bulk insert. Then in your trigger you simply add a record to the JobRequest table and then execute sp_startjob to start the job that looks up the newly added record and calls the SP. Be advised that this then becomes a separate transaction and you will have no way to roll back the data in the same transaction.

Like Matt I've never had much luck using Bulk Insert but have only tried a few times. However I have almost never had a problem with BCP.

BTW: A nice side effect of using the JobRequest scenario is that you now have a running log of what has been imported

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Di Strapps SSC Rookie Points: 32 More actions · Answer 3

Many thanks to you both for your response.

I may have to use bcp because the name of the file to be imported will vary, and the user will input it. Unless I can pass a parameter to DTS - I haven't tried that.

I will certainly try the sp_startjob method - that sounds just what I need.

Thanks again.

Di.