在 1 个表上使用多个触发器的效率
我计划在单个表上放置 10-15 个插入触发器。每个触发器将执行 3~ 次插入到另一个表。这会对我的数据库产生不利影响吗？我认为执行所有触发器所需的速度可能有点慢。
原因是第三方软件为我们将数据记录在数据库中。表方案如下：
DateTime EquipA_Speed EquipA_Power EquipB_Speed EquipB_Power EquipC_Speed EquipC_Power
然后我们要在我们自己的数据库中规范化这些数据。我们的数据库有一个设备表和一个信号类型表（信号是功率、速度等）。
我的计划是为每个设备配备一个触发器，以使其更简单且更易于维护。插入行时，我需要为信号表中每台设备的每个信号添加一行。
SignalID Value EquipmentID SignalTypeID
I am planning on putting 10-15 insert triggers on a single table. Each trigger will do 3~ inserts to another table. Will this have an adverse effect on my database? I'm thinking the speed it takes to execute all the triggers may be a little slow.
The reason for this is a 3rd party peice of software logs data in a database for us. The table scheme is like so:
DateTime EquipA_Speed EquipA_Power EquipB_Speed EquipB_Power EquipC_Speed EquipC_Power
We then want to normalize this data in our own database. Our database has an Equipment table and a SignalType table (signals being power, speed etc).
My plan was have a trigger for each piece of a equipment to make it simpler and more maintainable. When a row is inserted, I need to add a row for each signal for each piece of equipment in a Signal table.
SignalID Value EquipmentID SignalTypeID
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以轮询宽表，或者使用触发器中的服务代理来通知读取该表的解耦进程。
然后，您可以首先在临时表或表变量中准备/分解数据。 然后启动一个事务，进行写入、提交。
You could poll the wide table, or use service broker from the trigger to notify a decoupled process that reads the table.
Then you can have a stored that prepares/shreds the data first in temp tables or table variables first. Then starts a transaction, do your writes, commits.
这完全取决于触发器的作用。触发器不会影响性能，触发器包含的逻辑将在本文的倒数第二段中阅读以获取更多详细信息：
http://msdn.microsoft.com/en-gb/magazine/cc164047.aspx
您可以做的是设置一个测试用例。另一件需要注意的事情是日志文件 (.ldf) 的大小，因为它会根据插入的频率而变得相当大。
无论如何你为什么想要这个？
It depends exactly what the trigger does. The trigger won;t be the performance hit, the logic the trigger contains will have a read at the second last paragragh in this article for more details:
http://msdn.microsoft.com/en-gb/magazine/cc164047.aspx
What you could do is set up a test case. Another thing to watch is the size of the log file (.ldf) as it will grow quite large depending on how often an insert is done.
WHy would you want to this anyway?