Difference between Triggers and Stored Procedures

Question

What are the differences between Triggers and Stored Procedures?

Answer 1

Stored Procedures

They are like functions to be called by various circumstances. In thesis can do anything, even not related so directly to the tables, although rare.

Don’t think there’s anything much more complex than that.

Of course they are stored inside the database, not in your application. You can even use SQL to invoke them, but it is something indirect, the database takes care of it.

When you program, you basically create two things: data structure and algorithms. In codes considered modern (though it is questionable to say so) it is normal to do so in classes that have both the structure (the members with state) and the related algorithms (the methods). In more procedural code this is isolated. It’s a shame that many programmers today have no idea that this is possible and the advantages of doing so.

In the database the structure is the table. The stored procedures are the methods. Note that there is no direct link between them and the tables.

Definition of Wikipedia:

Stored Procedure or Stored Procedure is a collection of SQL commands for "database dispensing". Encapsulates repetitive tasks, accepts input parameters, and returns a status value (to indicate acceptance or failure of execution). The stored procedure can reduce network traffic, since commands are executed directly on the server. In addition to improving performance, create security mechanisms between the manipulation of data from the Database.

So, roughly speaking, it’s like you have an executable or scripts within the database. Whenever you need that to be executed it can be called.

How to use

To call a stored Procedure usually has a command EXECUTE or CALL, or DO or RUN, depending on the database. It’s an SQL command like any other. Called, executes what you need.

Just as you can create, modify, and delete tables, you can do the same with sprocs, they are database objects. You have SQL command for this. Example:

CREATE PROCEDURE HumanResources.uspGetEmployeesTest2   
    @LastName nvarchar(50),   
    @FirstName nvarchar(50)   
AS   

    SET NOCOUNT ON;  
    SELECT FirstName, LastName, Department  
    FROM HumanResources.vEmployeeDepartmentHistory  
    WHERE FirstName = @FirstName AND LastName = @LastName  
    AND EndDate IS NULL;  
GO 

Note that what you have @ are the parameters that can be passed to it. The rest are language commands, which of course can execute any SQL command, but not only. It’s a programming language as any other (unlike SQL which is not a programming language).

The language has variables, conditional or unconditional deviations, makes calculations of all kinds, in short, is complete. Example with IF and variables:

DECLARE @maxWeight float, @productKey integer  
SET @maxWeight = 100.00  
SET @productKey = 424  
IF @maxWeight <= (SELECT Weight from DimProduct 
                  WHERE ProductKey = @productKey)   
    (SELECT @productKey AS ProductKey, EnglishDescription, Weight, 
    'This product is too heavy to ship and is only available for pickup.' 
        AS ShippingStatus
    FROM DimProduct WHERE ProductKey = @productKey);  
ELSE  
    (SELECT @productKey AS ProductKey, EnglishDescription, Weight, 
    'This product is available for shipping or pickup.' 
        AS ShippingStatus
    FROM DimProduct WHERE ProductKey = @productKey);  

T-SQL Reference (the language of SQL Server). Each database has its own language, another example is the PL/SQL.

Where it is used

When you need to create a validation, an operation that needs to be performed in certain circumstances for maintenance or adaptation of data may be useful.

It has an advantage that is all run in the database without leaving it. But there are techniques to avoid this without using the sprocs. It is usually useful to have a canonical way of doing something in the database. It is like making a DRY. But you can do the same in the app if you know what you’re doing.

I’ve lived without them for a long time. There are those who like it a lot, but they are less useful than it seems in most cases where the database is used with applications.

Useful reading to understand what was asked in the duplicate.

Trigger

As the name (trigger) says, it is a notification mechanism that something happened (an insertion, update or removal) and therefore some action must be performed.

The trigger itself is just that mechanism. Of course it has to perform an action, and it can be even call a stored Procedure. If it is very simple the action can be declared right there next to the trigger declaration, as a sproc.

Roughly speaking it is the implementation of the Observer standard in the database.

It is also a database object and can be created, changed or removed. Example:

CREATE TRIGGER reminder2  
ON Sales.Customer  
AFTER INSERT, UPDATE, DELETE   
AS  
   EXEC msdb.dbo.sp_send_dbmail  
        @profile_name = 'AdventureWorks2012 Administrator',  
        @recipients = '[email protected]',  
        @body = 'Don''t forget to print a report for the sales force.',  
        @subject = 'Reminder';  
GO  

In this case it will be used in the table Sales.Customer , whenever a INSERT, UPDATE or DELETE it will execute the following procedure, right after the SQL command of table manipulation is executed (there are cases that are preferred to run before). In case you’re calling a sproc stored in DB.

Another example where the procedure is already next to the trigger:

CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader  
AFTER INSERT  
AS  
IF EXISTS (SELECT *  
           FROM Purchasing.PurchaseOrderHeader AS p   
           JOIN inserted AS i   
           ON p.PurchaseOrderID = i.PurchaseOrderID   
           JOIN Purchasing.Vendor AS v   
           ON v.BusinessEntityID = p.VendorID  
           WHERE v.CreditRating = 5  
          )  
BEGIN  
RAISERROR ('A vendor''s credit rating is too low to accept new  
purchase orders.', 16, 1);  
ROLLBACK TRANSACTION;  
RETURN   
END;  
GO

This executes a code described just after an interaction occurred in the table Purchasing.PurchaseOrderHeader.

The events it deals with are not limited to the commands using in tables:

CREATE TRIGGER ddl_trig_database   
ON ALL SERVER   
FOR CREATE_DATABASE   
AS   
    PRINT 'Database Created.'  
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  
GO

I put in the Github for future reference.

Usefulness

Think of a client that is erased. What happens when all the information generated for it? It is orphaned. So the correct thing is to do the DELETE in the client, delete everything that refers to it. Usually this delete is actually carry to a dead file.

That’s not even the best example because you have reasons not to, but it’s simple to understand. How do you transport to the dead file guaranteed if the customer is removed? Through a trigger.

Another example: here on the site you get a vote, this updates the table of the post who received the vote. Your reputation should rise or fall. How to ensure that an operation that records a vote in a post updates the user’s reputation? Creates a trigger that triggers this update for you.

It can also be used for validation in the data entry in the table. You may think that this can already be done with other mechanisms in the database. Yes, but in a standardized and simple way. When you need something more complex you need to create an algorithm in the programming language of the database, and most importantly, you need to ensure that it is executed every time the data is entered or changed to ensure that it is in accordance with the intended.

A classic example is to create an audit table whenever a data is inserted, modified or deleted from the table. There you can put the ones that were modified, who did, when, and other useful information.

It is useful to ensure the referential integrity of the model, no matter what happens in the database, it is guaranteed that all consequences of this are reflected correctly in the entire database at the same time.

It’s much more useful than a simple sproc. But we must be careful not to abuse (I see too much abuse). If you keep firing trigger without a great reason to do it can compromise performance and start doing lambança.