有效的 SQL Server 触发器 - 仅触发一次
我想在表数据更改时进行一些计算。但是,我手动更新表并一次性复制粘贴约 3000 行。这使得我的触发器工作了 3000 次,但我希望它只触发一次。
有办法做到这一点吗?
谢谢。
I want to do some calculations when my table data is changed. However, I am updating my table manually and copy pasting about 3000 rows in once. That makes my trigger work 3000 times, but I want it to do the trigger only once.
Is there a way to do that ?
Thanks.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
你的说法是什么?
如果您有多个插入,那么它将针对您正在运行的每个插入触发。如果您希望它对多次插入只执行一次,您必须:
insert into foo select * from bar
其他可能性可能是:
What's your statement?
If you have multiple inserts then it will fire for each insert you are running. If you want it to execute only once for multiple inserts you must:
insert into foo select * from bar
Other possibility might be:
如果“手动”意味着您正在复制并粘贴到某些用户界面工具(例如 Access dataGrid)或类似工具中,那么该工具可能会每行发出一个插入语句,在这种情况下,您就不走运了每个插入语句将执行一次数据库触发器。正如其他答案所提到的,如果您可以使用单个插入语句将行直接插入数据库,那么触发器只会触发一次。
If by 'manually' you mean you are copying and pasting into some User Interface tool (like an Access dataGrid) or something like that, then the tool may be issuing one insert statement per row, and in that case you are out of luck the database trigger will be executed once per insert statement. As other answers have mentioned, if you can insert the rows directly into the database, using a single insert statement, then the trigger will only fire once.
该问题是由于您手动粘贴 3000 行而引起的。你确实有两个解决方案。您可以通过执行以下操作来关闭触发器:
然后在最后运行触发器的内容,或者您可以将 3000 列放入临时表中,然后一次将它们全部插入。这只会设置 1 个触发器。如果这还不够,请向我们提供有关您正在尝试执行的操作的更多信息。
The issue is caused because you are manually pasting the 3000 rows. You really have 2 solutions. You can turn off the trigger by doing this:
and then run the contents of your trigger at then end or you can put your 3000 columns into a temp table and then insert them all at once. This will only setup 1 trigger. If this isn't enough please give us more info on what you are trying to do.
如果您在单个语句中修改 3000 行,则触发器不会触发 3000 次。您的触发器将触发一次,并且您的虚拟“已删除”表中将有 3000 行。
Your trigger will not fire 3000 times if you are modifying 3000 rows in a single statement. Your trigger will fire once and there will be 3000 rows in your virtual 'deleted' table.
如果您在将数据导入到每行执行一次插入的系统中的方式受到限制,我建议您将数据导入到中间表中,然后从中间表插入到最终表中。
If you are limited in how you can import the data into the system that does a single insert per row, I would suggest that you import data into an intermediate table then do the insert into the final table from the intermediate one.
有一个更好的方法。
重新链接触发器创建或更改的表,将它们与预期更改或添加的表进行比较,并使用简单的 WHERE 子句避免触发器。
例如-我用这个触发器来插入一条记录,但仅基于现有的列值(@ack)一次。
There is a better way.
Re-link the tables that your trigger created or altered, compare them against what's expected to be changed or added and avoid the trigger with a simple WHERE clause.
eg - this trigger I use to INSERT a record, but only once based on a column value existing (@ack).