Question on bulk import trigger / procedure

[biglanceh]

Good morning everyone,

I need some experienced or more knowledgabe than myself (not saying much) input on a design question.

I have built a bulk import table, with set columns for receiving either DTP flat file import, or direct ODBC dumps.

I have probably about 1000 - 1500 lines of code that I will need to process.

I'm at a loss as to whether that would be WAY too much code for a trim and streamlined trigger to operate, or if MS SQL can chew that up and spit it out easily.

I will be importing anywhere from 1 record to 30,000 + in a single dump.


So my questions are:

1.) Should I have a basic trigger tossing parameters to stored procedures to chop up the code? Or leave the code in a single body within the trigger?

2.) Second question is what type of trigger should I use. My reference is in light of Oracle, where there is a FOR EACH RECORD clause that will cursor each row reliably.

I am not sure if I should do an INSTEAD OF, or AFTER INSERT.

I guess I'm just looking for someone with some mileage on this type of code organization with MS SQL 2000 to maybe steer me in the right direction.

Thank you so much!

Lance

[skhanal]

1. I don't think it makes a difference whether you put all your code in a trigger or keep it separate and call it from the trigger.

2. There is no FOR EACH ROW trigger in SQL Server and all triggers are AFTER, that eliminates your choices.

INSTEAD OF trigger is usually used with a view or if you want your trigger to fire before constraint checking is done.

[biglanceh]

Thank you Shailesh,

One last question then on the AFTER INSERT triggers in MS SQL 2000.

Do they fire after each record inserted? Or after the bulk load has completed?

Thanks again!