Stored Procedures

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

Don't think there's something much more complex than that.

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

When programming we create basically two things: Data Structure and algorithms. In codes considered modern (although it is questionable to say this) it is normal to do this in classes that have both the structure (the stateful members) and the related algorithms (the methods). In more procedural code this is isolated. It is a pity that many programmers today have no idea that this is possible and the advantages of doing it this way.

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

Wikipedia definition :

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

So, roughly speaking, it's like having an executable or scripts inside the database. Whenever he needs that to be executed he 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 is a SQL command like any other. Called, performs what you need.

Just as you can create, modify, and delete tables, you can do the same with sprocs, they are database objects. It has 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 @ has are the parameters that can be passed to it. The rest are language command, which obviously can execute any SQL command, but not only. is a programming language like 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, it 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 SQL Server language). Each database has its own language, another example is PL / SQL .

Where is used

Whenever you need to create a validation, an operation that must be performed under certain circumstances for data maintenance or adaptation can be useful.

It has an advantage that it is all executed in the database without having to leave it. But there are techniques to avoid this without the use of the sprocs . It is usually useful to have a canonical way of doing something in the database. It's like making a DRY . But you can do the same in the application, if you know what you're doing.

I've lived without them for quite some time. There are those who like it very much, 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 has happened (an insert, update or removal) and therefore some action must be run.

The trigger itself is just that mechanism. Of course it has to perform an action, and it can even be called 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 is the implementation of the Observer pattern 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 Table Sales.Customer , whenever a INSERT, UPDATE or DELETE it will execute the following procedure, right after the SQL table manipulation command is executed(there are cases that one prefers to execute before). In the case you are calling a sproc stored in the 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 shortly after an interaction occurred in the table Purchasing.PurchaseOrderHeader.

The events it handles are not limited to 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 on GitHub for future reference .

Utility

Think of a client that gets deleted. What happens when all the information generated for him? It's all orphaned. So the correct thing is that if you do DELETE on the client, delete everything that refers to it. Usually this Delete is actually transport to a file dead.

This is not even the best example because it has reasons not to do this, but it is simple to understand. How does it transport to the archive guaranteed if the client is removed? Through a trigger.

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

Can also be used in 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 database programming language, and most importantly, you need to ensure that this is executed every time the data is entered or changed to ensure that it is in accordance with intended.

A classic example is to create an audit table whenever a data is entered, modified, or deleted from the table. There you can put the ones that was modified, who made it, 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 the consequences of this are reflected correctly in the entire database at the same time.

Has much more utility that a simple sproc . But care must be taken not to abuse (I see a lot of abuse). If you keep firing trigger without a great reason to do it can compromise performance and start doing lambança.