触发V / S存储过程 [英] Trigger V/S stored Procedure

问题描述

我有一个更新表中标志的存储过程和一个删除更新行的触发器并将其插入新表中..因此可以在存储过程中添加相同的触发器功能..所以我只是想知道：



什么是使用存储过程或触发器更好？在什么情况下？换句话说，你能告诉我每个人的优点和缺点



请注意我正在使用SQL Server 2008而我正在将vb.net连接到我的数据库



提前谢谢

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:

what 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

thank you in advance

推荐答案

触发器是一种特殊类型的存储过程，不是由用户直接调用的。它将在一些数据库操作上调用（插入，更新，删除）



参考此链接



存储过程与触发器之间的差异 [< a href =http://www.codeproject.com/Tips/624566/Differences-between-a-Stored-Procedure-and-a-Triggtarget =_ blanktitle =New Window> ^ ]



它声明：

A trigger is a special type of stored procedure that is not called directly by a user. It will be invoked on some database operations (insert,update,delete)

Refer this link

Differences between a Stored Procedure and a Trigger[^]

It states that:

引用：

- 我们可以在exec命令的帮助下随时执行存储过程，但只有在定义了触发器的表上触发事件（插入，删除和更新）时才能执行触发器。



- 我们可以从另一个存储过程内部调用存储过程但是我们不能直接调用触发器中的另一个触发器。我们只能实现触发器的嵌套，其中触发器中定义的操作（插入，删除和更新）可以启动在同一个表或不同表上定义的另一个触发器的执行。



- 存储过程可以通过作业安排在预定义的时间执行，但是我们无法安排触发器。



- 存储过程可以获取输入参数，但是我们不能将参数作为输入传递给触发器。



- 存储过程可以返回值，但触发器不能返回值。



- 我们可以在存储过程中使用Print命令进行调试，但我们不能在触发器中使用print命令。



- 我们可以在存储过程中使用诸如begin transaction，commit transaction和rollback之类的事务语句，但我们不能在触发器中使用事务语句。



- 我们可以从前端调用存储过程（.as p文件，.aspx文件，.ascx文件等）但我们不能从这些文件中调用触发器。



- 存储过程用于执行任务。存储过程通常用于执行用户指定的任务。他们可以拥有参数并返回多个结果集。



- 审计工作的触发器：触发器通常用于审计工作。它们可用于跟踪表事件的活动。

- We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete, and update) is fired on the table on which the trigger is defined.

- We can call a stored procedure from inside another stored procedure but we can't directly call another trigger within a trigger. We can only achieve nesting of triggers in which the action (insert, delete, and update) defined within a trigger can initiate execution of another trigger defined on the same table or a different table.

- Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.

- Stored procedure can take input parameters, but we can't pass parameters as input to a trigger.

- Stored procedures can return values but a trigger cannot return a value.

- We can use Print commands inside a stored procedure for debugging purposes but we can't use print commands inside a trigger.

- We can use transaction statements like begin transaction, commit transaction, and rollback inside a stored procedure but we can't use transaction statements inside a trigger.

- We can call a stored procedure from the front end (.asp files, .aspx files, .ascx files, etc.) but we can't call a trigger from these files.

- Stored procedures are used for performing tasks. Stored procedures are normally used for performing user specified tasks. They can have parameters and return multiple results sets.

- The Triggers for auditing work: Triggers normally are used for auditing work. They can be used to trace the activities of table events.

此外，请参阅这些链接



使用存储过程有什么好处在sql server [ ^ ]



在SQL Server中使用触发器有哪些优点和缺点？ [ ^ ]



问候.. :)

Further,refer to these links

what are the advantages of using stored procedures in sql server[^]

What are advantages and disadvantages of using trigger in SQL Server?[^]

Regards..:)

我在上面添加了一些东西



当你想要自动操作时应该执行数据完整性你需要使用Trigger的时间。触发器在DML操作上被触发。



或



您还可以使用系统级触发器来监控任何更改任何数据库的数据库架构，并在发生任何更改时向管理员发送电子邮件警报。



或



你听说过Magic（逻辑）表，Trigger在很多方面使用这些表。



SQL Server中插入和删除的逻辑表，这些表是自动创建的并在内部由SQL Server管理，以便在数据库表上的DML操作（插入，更新，删除）期间保存最近插入，删除和更新的值。



使用逻辑表



基本上，触发器使用逻辑表用于以下目的：

1.

测试数据操作错误并根据错误采取适当的措施。



2.

查找表状态之间的差异在数据修改之前和之后，根据该差异采取行动





存储过程可以在.Net代码中使用，无法在DML操作上执行，或者您可以在SQL Agent中使用它来按计划执行，或者您需要手动执行它...使用SSMS。



问候，

Mubin

Hi,

I add few more thing to above

When ever you want automatic action should be perform for data integrity at that time you need to use Trigger. Trigger gets fired on DML Operations.

or

You can also use System level Trigger to Monitor any change in DB schema of any database and send email alert to admin if any change takes place.

or

Have you Heard about Magic(Logical) tables ,Trigger utilizes these tables in many ways .

Inserted and Deleted logical tables in SQL Server, These tables are automatically created and managed by SQL Server internally to hold recently inserted, deleted and updated values during DML operations (Insert,Update,Delete) on a database table.

Use of logical tables

Basically, logical tables are used by triggers for the following purpose:
1.
To test data manipulation errors and take suitable actions based on the errors.

2.
To find the difference between the state of a table before and after the data modification and take actions based on that difference


Where stored procedures can be used within your code of .Net,can not execute on DML operation, or you can use it in SQL Agent for regular execution on schedule, or you need to execute it manually..using SSMS.

Regards,
Mubin

这篇关于触发V / S存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！