SQL Differences between stored procedure and triggers

Question

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.

[stored procedure](http://en.m.wikipedia.org/wiki/Stored_procedure). [trigger](http://en.m.wikipedia.org/wiki/Database_trigger). — Mikael Eriksson, May 18 '13 at 20:12

score 54 · Accepted Answer · edited May 23 '17 at 12:18

54

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.

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.

edited May 23 '17 at 12:18

Community

1
1

answered May 18 '13 at 20:10

Bohemian

412,405
93
575
722

So lets say for example we have to notify user when a product is not returned would you use a trigger or a stored procedure? – Dynamiite May 18 '13 at 20:13
No. Data not being returned is not an "event". Also, doing things outside the database like notifying users is difficult from a stored procedure. You should use application code for this. – Bohemian May 18 '13 at 20:16
10

***WHY*** would you recommend to avoid stored procedures at all costs?? Seems like a rather unfounded and too broad statement, in my opinion, stored procedures **do have** valid and very legitimate use cases! – marc_s May 18 '13 at 20:23
@marc_s read the linked answer for why. Ok, I'll tone it down a little. – Bohemian May 18 '13 at 20:24
Not sure if this really is the case for MySQL or Oracle - but when I need to do some kind of a mass operation, I'd **much rather** do that on the server (SQL Server in my case) in a well crafted and well written stored procedure and just return a result, rather than pulling gigabytes of data to the client, computing something and then getting just a tiny little result out of this.... – marc_s May 18 '13 at 20:26
7

I would much rather say to avoid **triggers** if ever possible, since they (a) don't scale well, (b) you can't control if and when and how often they fire, and (c) their functionality is often "hidden" and forgotten by developers and supporters, making them an endless source of trouble and nasty surprises.... – marc_s May 18 '13 at 20:28
@marc_s I used to think that too. I actually wrote the entire production server business logic using stored procedures. I though it was "more efficient". But now I realise they are the work of the devil. Read the link - it's all there. "Efficiency" isn't everything. There are much broader concepts that are more important than raw efficiency. – Bohemian May 18 '13 at 20:33
@marc_s in a recent project, I created a trigger that fired on data changes to numerous tables to populate a table that was a queue for an ESB that was being added to a legacy system. Using a trigger was the only, and best, way to intercept changes. I'm not totally anti SP/trigger, but you use them only where you *have* to – Bohemian May 18 '13 at 20:37

score 36 · Answer 2 · answered May 18 '13 at 20:11

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.

score 11 · Answer 3 · edited Oct 31 '17 at 08:01

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.

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

We can create trigger like this:

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

A stored procedure is 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.

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

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.

score 7 · Answer 4 · answered May 02 '16 at 08:46

Some differences between triggers and procedures:

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.
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 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.

score 5 · Answer 5 · answered May 18 '13 at 20:09

5

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

answered May 18 '13 at 20:09

Tim

8,669
31
105
183

1

So can you call a stored procedure from within a trigger? – Dynamiite May 18 '13 at 20:16

score 3 · Answer 6 · answered May 18 '13 at 20:12

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.

What is the relevance of "Transact SQL"? Do you think that SQL Server is the only database in the world? — Bohemian, May 18 '13 at 20:13

score 1 · Answer 7 · answered May 18 '13 at 20:16

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.

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.

score 1 · Answer 8 · answered Jul 19 '13 at 09:09

1

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.

answered Jul 19 '13 at 09:09

sql learner

11
1

score 1 · Answer 9 · answered Sep 24 '13 at 13:28

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.

score 1 · Answer 10 · edited Oct 31 '17 at 09:45

1

                    ***TRIGGERS***

Action on specific time.
Triggers is a special type of stored procedure that is not called directly by user.
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

edited Oct 31 '17 at 09:45

Chirag

994
2
11
26

answered May 07 '14 at 16:49

user3613136

11
1

Consider improving both the format and content of this answer. This is a trigger - great. What's a stored procedure? Also learn how to use the markdown to create a nice-looking list. – random_user_name May 07 '14 at 18:16

score 1 · Answer 11 · answered Dec 08 '18 at 07:13

1

If you are familiar with JavaScript, a trigger is an addEventListener and Stored Procedure is a callback.

answered Dec 08 '18 at 07:13

vighnesh153

4,354
2
13
27

score 0 · Answer 12 · edited Apr 29 '14 at 12:28

Both are database objects containing blocks lof code that can be used for implementing business logic

The differences are:

1) Triggers fire automatically but they need events for that. (Example: create,alter,drop,insert,delete,update) .

2) Procedures have to be explicitly called and then executed. They do not need create,alter,drop,insert,delete,update. we can also execute procedures automatically using the sp_procoption.

3) we cannot pass parameters inside the triggers,

but we can pass parameters inside stored procedures

example: if we want to display a message "error"

using a trigger: we need some DDL/DML Statement using a procedure: NO DDL/DML is needed

Code · Answer 13 · 2019-05-28T08:01:39.093

0

Difference Between a Stored Procedure and a Trigger

We can define a trigger as a database object just like a stored procedure, or we can say it is a special kind of stored procedure which fires when an event occurs in a database. We can execute a SQL query that will "do something" in a database when an event is fired.

    Triggers are fired implicitly while stored procedures are fired explicitly.

edited May 28 '19 at 08:01

answered May 28 '19 at 07:38

Code

679
5
9

SQL Differences between stored procedure and triggers

13 Answers13

but we can pass parameters inside stored procedures

Linked