在触发器中循环？
我得到以下场景，
有四个表 COUNTRY、STATE、CITY、STREET
我有包含上述记录的 Excel 文件..到目前为止可能有 2000 行。
我使用 SqlBulkCopy 将数据导入到临时表中，我们将该表命名为 IMPORT。
我编写了一个用于在 IMPORT 表上插入的触发器，该触发器获取插入的记录 并分割国家、州、城市、街道，然后将它们插入到相应的表中。
在此触发器中，我必须执行一些条件检查，例如，如果 COUNTRY 名称已存在，则返回 COUNTRY_ID，否则插入它并获取新的 COUNTRY_ID。
如果 Excel 文件只有一行，则上述方法有效。 一旦我将原始 Excel 导入，我发现触发器中的以下语句失败“从 INSERTED 选择国家/地区”，因为 sqlbulkcopy 使 INSERTED 具有多个记录。
表结构
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
那么我可以在触发器中使用将循环的循环语句吗INSERTED 中的所有记录？
或者如何以最好的方式解决这个问题？
注意：由于他们已经在使用它，我无法控制这些表结构及其关系。
提前致谢。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您的第一个问题是您永远不应该考虑将循环记录集作为首选。在这里，它几乎总是错误的选择。您的下一个问题是触发器处理整组记录，而不是一次处理一个记录，根据您的描述，我敢打赌您编写它时假设它一次会处理一个记录。您需要一个基于集合的流程。
可能您在触发器中需要类似的东西，它将插入尚未在国家/地区表中的所有国家/地区（这假设country_Id是一个整数标识列）：
您也可以使用存储过程而不是触发器来插入暂存表中的真实表。
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):
You also could use a stored proc instead of a trigger to insert into the real tables from the staging table.
我绝不会将任何此类处理密集型任务放入用于批量加载的表上的触发器中！并且永远永远不要开始将像游标之类的循环和类似的东西放入触发器中 - 触发器必须小，精益且简单 - 只是快速插入到审计表或其他东西中 - 但它应该不要做繁重的工作！
您应该做的是：
SqlBulkLoad尽快将数据放入该临时表中，没有触发器或任何否则，您将完全消除
SqlBulkLoad所具有的任何好处..并进行此后处理（例如确定
Country_ID 对于给定的国家/地区），您不需要游标或任何这些邪恶的位 - 只需在您的系统上使用标准的、普通的UPDATE语句即可表 - 这就是您所需要的。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:
SqlBulkLoadto get your data into that staging table as quickly as possible, no triggers or anythingOtherwise, you're totally killing off any benefit that
SqlBulkLoadhas..And to do this post processing (like determining
Country_IDfor a givenCountry), you don't need no cursors or any of those evil bits - just use standard, run-of-the-millUPDATEstatements on your table - that's all you need.