Is it possible to loop in Trigger?

sqlbulkcopy sql-server-2008 triggers

Question

I got this following scenario,

There are four tables COUNTRY, STATE, CITY, STREET
And I have the excel file with the records of the above..possibly 2000 rows as of now.

I used SqlBulkCopy to import the data to a temp table, lets name the table IMPORT.

And i wrote one trigger for insert on the IMPORT table which gets the inserted record and splits country, state, city, street then inserts them to the respective table.

In this trigger i got to do some conditional check,like, if COUNTRY name is already present then returns the COUNTRY_ID else insert it and get the new COUNTRY_ID.

The above works if the Excel file has only one row. Once i put the original Excel for the import i figured it out the following statement in the trigger fails "select country from INSERTED" because sqlbulkcopy makes INSERTED to has more than one records.

Table Structure

COUNTRY

  • Country_ID
  • Country_Name

STATE

  • State_ID
  • Country_ID
  • State_Name

CITY

  • City_ID
  • State_ID
  • Country_ID
  • City_Name

STREET

  • Street_ID
  • City_ID
  • State_ID
  • Country_ID
  • Street_Name

IMPORT

  • Country_Name
  • State_Name
  • City_Name
  • Street_Name

So can i have loop statement in trigger that will loop through all the records in INSERTED?

Or how to address this in the best way?

NOTE: Since they are already using it, i've got no control over those table structure and their relationships.

Thanks in advance.

1
0
11/22/2010 6:07:27 AM

Accepted Answer

Your first isssues is that you should never consider looping through a record set as a first choice. It is almost always the wrong choice as it is here. Your next problem is that triggers processs the whole set of records not one at a time and from your description, I'll bet you wrote it assuming it would process one record at a time. You need a set-based process.

Likely you need something like this in your trigger which would insert all countries in inserted that aren't already in the country table (this assumes country_Id is an integer identitiy column):

Insert country (country_name)
select country_name 
from inserted i
where not exists 
  (select * from country c 
   where c.country_name = i.country_name)

You also could use a stored proc instead of a trigger to insert into the real tables from the staging table.

2
11/24/2010 2:28:12 PM

Popular Answer

I would never put any such processing intensive task into a trigger on a table used for bulk load ! And never ever start putting loops like cursors and stuff like that into a trigger - a trigger must be small, lean and mean - just a quick INSERT into an audit table or something - but it should not do heavy lifting!

What you should do is this:

  • use SqlBulkLoad to get your data into that staging table as quickly as possible, no triggers or anything
  • then based on that staging table, do the necessary post-processing by splitting up column values and stuff like that

Otherwise, you're totally killing off any benefit that SqlBulkLoad has..

And to do this post processing (like determining Country_ID for a given Country), you don't need no cursors or any of those evil bits - just use standard, run-of-the-mill UPDATE statements on your table - that's all you need.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow