存储过程和触发器之间的 SQL 差异

SQL Differences between stored procedure and triggers

提问by Dynamiite

I'm having trouble understanding the difference between a stored procedure and a trigger in sql. If someone could be kind enough to explain it to me that would be great.

我无法理解 sql 中存储过程和触发器之间的区别。如果有人能善意地向我解释，那就太好了。

Thanks in advance

提前致谢

回答by Bohemian

A stored procedure is a user defined piece of code written in the local version of PL/SQL, which may return a value (making it a function) that is invoked by calling it explicitly.

存储过程是用本地 PL/SQL 版本编写的用户定义的一段代码，它可以返回一个值（使其成为一个函数），该值通过显式调用来调用。

A trigger is a stored procedure that runs automatically when various events happen (eg update, insert, delete).

触发器是在发生各种事件（例如更新、插入、删除）时自动运行的存储过程。

IMHO stored procedures are to be avoided unless absolutely required.

恕我直言，除非绝对需要，否则应避免使用存储过程。

回答by mgw854

Think of a stored procedure like a method in an object-oriented programming language. You pass in some parameters, it does work, and it can return something.

将存储过程视为面向对象编程语言中的方法。你传入一些参数，它确实有效，并且可以返回一些东西。

Triggers are more like event handlers in an object-oriented programming language. Upon a certain condition, it can either (a) handle the event itself, or (b) do some processing and allow for the event to continue to bubble up.

触发器更像是面向对象编程语言中的事件处理程序。在特定条件下，它可以 (a) 自己处理事件，或者 (b) 进行一些处理并允许事件继续冒泡。

回答by Avadhesh Kumar Verma

In respect to triggers in SQL Server: a trigger is a special piece of code that automatically gets executed when an event occurs in the database server.

关于 SQL Server 中的触发器：触发器是一段特殊的代码，当数据库服务器中发生事件时，它会自动执行。

DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected

DML 触发器在用户尝试通过数据操作语言 (DML) 事件修改数据时执行。DML 事件是表或视图上的 INSERT、UPDATE 或 DELETE 语句。这些触发器在任何有效事件被触发时触发，无论是否有任何表行受到影响

We can create trigger like this:

我们可以像这样创建触发器：

CREATE TRIGGER TriggerName
ON [dbo].[TableName]
FOR DELETE, INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON
END

A stored procedureis nothing more than prepared SQL code that you save so you can reuse the code over and over again. So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure.

一个存储过程无非是准备SQL代码更您保存以便您可以通过重用代码一遍又一遍。因此，如果您考虑一遍又一遍编写的查询，而不必每次将其保存为存储过程时都编写该查询，然后只需调用该存储过程来执行您保存为其中一部分的 SQL 代码存储过程。

• We can do lot of programming stuff in a stored procedure and execute again and again.
• We can create procedure which take the input process and give the output
• We can handle the error through try catch
• Stored procedures can be nest and call again and again with nested calling
• It's more secure

• 我们可以在一个存储过程中进行大量的编程工作并一次又一次地执行。
• 我们可以创建接受输入过程并给出输出的过程
• 我们可以通过 try catch 处理错误
• 存储过程可以嵌套并通过嵌套调用一次又一次地调用
• 更安全

We can create a stored procedure like this:

我们可以创建一个这样的存储过程：

CREATE PROCEDURE dbo.Sample_Procedure 
    @param1 int = 0,
    @param2 int  
AS
    SELECT @param1,@param2 
    RETURN 0;

Differences in both of then

两者的区别

• Trigger can not be called manually where stored procedure can be called manually.

• Trigger executes automatically when event happens and can be use for reporting and data protection from deleting or dropping the table and data from database. We can prevent from trigger. On the other hand, a stored procedure has to be called by somebody.

• A stored procedure can be called from front end (client application) but trigger can not be called from client application.

• 在可以手动调用存储过程的地方不能手动调用触发器。

• 触发器在事件发生时自动执行，可用于报告和数据保护，防止从数据库中删除或删除表和数据。我们可以防止触发。另一方面，存储过程必须由某人调用。

• 可以从前端（客户端应用程序）调用存储过程，但不能从客户端应用程序调用触发器。

回答by Aboli Ogale

Some differences between triggers and procedures:

触发器和过程之间的一些区别：

1. 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.
2. Stored procedure can take input parameters, but we can't pass parameters as input to a trigger.
3. Stored procedures can return values but a trigger cannot return a value.
4. 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
5. 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.

1. 我们可以在 exec 命令的帮助下随时执行存储过程，但只有在定义触发器的表上触发事件（插入、删除和更新）时才能执行触发器。
2. 存储过程可以接受输入参数，但我们不能将参数作为输入传递给触发器。
3. 存储过程可以返回值，但触发器不能返回值。
4. 我们可以在存储过程中使用诸如开始事务、提交事务和回滚之类的事务语句，但不能在触发器中使用事务语句
5. 我们可以从前端（.asp 文件、.aspx 文件、.ascx 文件等）调用存储过程，但不能从这些文件调用触发器。

回答by Tim

A trigger fires after an insert, update, or delete. A stored procedure is a server-side program that is run when you invoke it.

在插入、更新或删除后触发触发器。存储过程是在您调用它时运行的服务器端程序。

回答by user3613136

                    ***TRIGGERS*** 

1. Action on specific time.

2. Triggers is a special type of stored procedure that is not called directly by user.

3. When the trigger is created, it is defined to fire when a specific type of data modification is made against a specific table or column

1. 在特定时间采取行动。

2. 触发器是一种特殊类型的存储过程，不会由用户直接调用。

3. 创建触发器时，它被定义为在针对特定表或列进行特定类型的数据修改时触发

回答by Deepu

A stored procedure is a group of SQL statements that is compiled one time, and then can be executed many times. Triggers are named database objects that are implicitly fired when a triggering event occurs. The trigger action can be run before or after the triggering event. Triggers are similar to stored procedures but differ in the way that they are invoked. A trigger is not called directly by a user, where as a stored procedure is directly called by a user.

存储过程是一组被编译一次，然后可以执行多次的 SQL 语句。触发器是在发生触发事件时隐式触发的命名数据库对象。触发动作可以在触发事件之前或之后运行。触发器与存储过程类似，但调用方式不同。触发器不是由用户直接调用的，而存储过程是由用户直接调用的。

回答by Branko Dimitrijevic

A stored procedure is a piece of code that resides in and is executed by the DBMS and can be called explicitly by the client or by other stored procedures. It is usually written in a procedural extension of SQL, such as PL/SQL under Oracle or T-SQL under MS SQL Server, but some DBMSes support more general languages such as Java or .NET as well.

存储过程是一段代码，它驻留在 DBMS 中并由 DBMS 执行，可以由客户端或其他存储过程显式调用。它通常是用 SQL 的过程扩展编写的，例如 Oracle 下的 PL/SQL 或 MS SQL Server 下的 T-SQL，但一些 DBMS 也支持更通用的语言，例如 Java 或 .NET。

A trigger is a (sort of) stored procedure that cannot be called explicitly, and instead executes automatically in response to events such as insertion, update or deletion or rows in a table.

触发器是一种（某种）存储过程，不能显式调用，而是自动执行以响应诸如插入、更新或删除或表中的行之类的事件。

回答by sql learner

A trigger is a special kind of stored procedure. It is attached to a table and only triggers when an insert, update or delete occurs. Stored procedures are essential functions that you can create and reuse in the table.

触发器是一种特殊的存储过程。它附加到一个表并且仅在发生插入、更新或删除时触发。存储过程是您可以在表中创建和重用的基本函数。

回答by Sayani

A stored procedure can be called form another stored procedure but not ab trigger. A stored procedure can be executed whenever a user wants but not a trigger.A trigger is fired only when events occur. A stored procedure can have a print statement,multiple parameters and return values but not a trigger. A stored procedure can be called from front end but not trigger.

一个存储过程可以从另一个存储过程中调用，但不能从 ab 触发器中调用。存储过程可以在用户需要时执行，但不是触发器。触发器仅在事件发生时触发。一个存储过程可以有一个打印语句、多个参数和返回值，但不能有一个触发器。可以从前端调用存储过程，但不能从触发器调用。