vb.net 触发器与存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18694711/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trigger vs. stored Procedure
提问by User7291
I have a stored procedure that updates a flag in a table and a trigger that deletes the row updated and insert it in a new table. So the same functionality of the trigger can be added in the stored procedure. So I just wanna know:
我有一个存储过程，用于更新表中的标志和一个触发器，用于删除更新的行并将其插入新表中。因此可以在存储过程中添加触发器的相同功能。所以我只想知道：
Which is better to use: stored procedure or trigger? And in what cases? In other words, can you give me the advantages and disadvantages of each one?
哪个更好用：存储过程还是触发器？在什么情况下？换句话说，你能不能给我每个人的优点和缺点？
Note that I'm using SQL server 2008 and I'm connecting VB.NET to my database.
请注意，我使用的是 SQL Server 2008 并且我将 VB.NET 连接到我的数据库。
回答by OkieOth
The trigger is called automatically by your database if a special event occurs (insert, update, delete). The stored procedure is simply a user wrote database function. This function can extend the database functionality our simply group complex operations. The user or a external program is responsible to trigger the call of this extra function.
如果发生特殊事件（插入、更新、删除），数据库会自动调用触发器。存储过程只是用户编写的数据库函数。这个函数可以扩展我们简单地组合复杂操作的数据库功能。用户或外部程序负责触发此额外功能的调用。
Trigger can call stored procedures.
触发器可以调用存储过程。
My advice ... if you want a automated reaction to events from your database then use a trigger.
我的建议...如果您想对数据库中的事件进行自动反应，请使用触发器。
Use procedures to avoid code redundances in your database code.
使用过程避免数据库代码中的代码冗余。
回答by Ashutosh Arya
It totally depends on your requirement,suppose you have an insert trigger & your table expects frequent inserts. then it is going to slow down the insert process. Sp on the other hand will be executed only when you are going to command.
这完全取决于您的要求，假设您有一个插入触发器并且您的表需要频繁插入。那么它会减慢插入过程。另一方面，Sp 只会在您要执行命令时执行。
Both as SQL objects hence no difference in the terms of execution plan etc.
两者都作为 SQL 对象，因此在执行计划等方面没有区别。
But again it depends on your requirement, if you want your table to be updated in real time then go for trigger, else if you can afford to wait for an hour or two to bring the data in consistent state then go for SP.
但这又取决于您的要求，如果您希望实时更新表，请使用触发器，否则，如果您能等待一两个小时以使数据处于一致状态，请使用 SP。
Good luck
祝你好运
