|Input and output parameters||Can have input and output parameters in stored procedures.||Cannot have input and output parameters in triggers.|
|Execution||Stored Procedures can be executed manually.||Triggers cannot be executed manually ,they are fired in response to events.|
|Call||Cannot call triggers in stored procedures.||Can call stored procedures in triggers.|
|Inserted and deleted table||Inserted and deleted tables are not created.||Inserted and deleted tables are created automatically when trigger is fired.|
|Return values||Stored procedures can return values.||Triggers cannot return values.|
Stored Procedures and Triggers are considered as a vital part of the SQL database. Stored Procedure is used to perform a specific task within a database app. A stored procedure can be used anywhere as per the convenience and saves coding effort and the overall programming time.
A trigger is a special kind of stored procedure-one that cannot be executed explicitly, instead of attached to an event. Whenever the event takes place, the trigger fires and the trigger's code runs. The objective of this blog is to discuss stored procedures and triggers in SQL Server.
Also, we will discuss how they are different from each other. If you are planning to become a SQL Server pro then don’t forget to master these two database concepts in 2020 before you start applying for jobs.
A stored procedure is a group of t-SQL statements that are stored as compiled form in the database to perform a specific task.
Syntax : CREATE PROCEDURE Procedure_name
( Inputparameter datatype, Outputparameter datatype OUT, ) AS BEGIN SQL -Statements END
EXEC Procedure_name OR EXECUTE Procedure_name
Learn SQL Server in the Easiest Way
Stored procedures can be classified into following categories:
System Stored procedures are inbuilt stored procedures created by Microsoft to perform various administrative tasks. Examples of system stored procedures: sp_who, sp_lock, sp_depends, etc. The system stored procedure has a prefix of sp_.
User-defined Stored procedures are the procedures created by the user to implement specific business logic. The prefix of user-defined stored procedures varies from organization to organization. We can create user-defined stored procedures with or without input and output parameters. In this blog, we will cover various examples illustrating the stored procedures with or without parameters.
Suppose there is a table named Sales_record whose structure is given below :
CREATE TABLE Sales_record ( Country varchar(20), Product varchar(20), Quantity INT, SalesAmount INT )
Insert the following data into the table Sales_record we created previously:
INSERT INTO Sales_record VALUES ('USA','Computer',100,50000), ('USA','HardDisk',10,20000), ('USA','FloppyDrive',50,1000), ('INDIA','Computer',1000,500000), ('INDIA','HardDisk',5,10000), ('INDIA','FloppyDrive',100,2000)
Now we will create stored procedure without parameter which will return the TotalSalesAmount for each country.
CREATE PROCEDURE usp_SalesRecord AS BEGIN SELECT Country, SUM(SalesAmount) AS TotalSalesAmount FROM Sales_record GROUP BY Country END EXECUTE usp_SalesRecord
In this example we will query the Sales_record table, but instead of getting back all records we will limit it to just a particular country:
CREATE PROCEDURE usp_SalesbyCountry ( @Country varchar(20) ) AS BEGIN SELECT Country, SUM(SalesAmount) AS TotalSalesAmount FROM Sales_record WHERE [email protected] GROUP BY Country END EXEC usp_SalesbyCountry 'USA'
We can also return the TotalSalesAmount of each country using the output parameter. The following example explains how to achieve so using output parameters:
CREATE PROCEDURE usp_SalesbyCountry_with_out ( @Country varchar(20), @sum_amount int OUT ) AS BEGIN SELECT @sum_amount=SUM(SalesAmount) FROM tbl_Sales2 WHERE [email protected] GROUP BY Country END DECLARE @sum_amount int EXEC usp_SalesbyCountry_with_out 'USA',@[email protected]_amount OUT SELECT @sum_amount AS Total_saleamount_USA
A trigger is a special kind of stored procedure-one that cannot be executed explicitly, instead of attached to an event. Whenever the event takes place, the trigger fires and the trigger's code runs.
To know more about Triggers and Stored Procedures in SQL Server, avail our self-learning courses at an attractive discount and learn everything about SQL Server in detail.
CREATE TRIGGER trigger_name ON
SQL Server Training & Certification
Triggers are classified into following categories:
DML triggers are fired in response to DML events such as insert, update, delete operations. DML triggers are further subdivided into the following subcategories:
In instead of trigger , the statements that was issued to cause the trigger to fire will never runs only the code in the trigger runs. In the following example instead of deleting the row containing the product 'Pendrive' record from Sales_record table, the message in the trigger will be displayed.
Example of Instead of trigger:
CREATE TRIGGER trg_delSales_record ON Sales_record INSTEAD OF DELETE AS BEGIN Print 'DONT have permission to delete from that table' END DELETE from Sales_Record Where Product='Pendrive'
DDL triggers are fire in response to DDL events such as create, alter and drop. In the following example we created a table named test_table in the test_database .When we try to drop any table in the test_database , DDL trigger will fire and will run the code in the trigger.
Read More: How to Restore a Database Backup from SQL
USE [test_database] CREATE TABLE [dbo].[test_table] ( [ID] [int] NULL, [Name] [varchar](50) NULL ) --creating DDL trigger on dropping of the table ALTER TRIGGER trg_create ON DATABASE FOR DROP_TABLE AS BEGIN Print 'Table dropped from test_database' END DROP TABLE test_table
These triggers are fired after SQL Server insert, update and delete statements completes the execution of the action successfully that fired it. The two special tables: the deleted table and the inserted tables are created when DML trigger statements are fired. Inserted table stores the updated new value after the update on the table and inserted value after the insertion on the table for which that trigger is created. Deleted table stores the old value after the update on the table and deleted value after the deletion on the table for which that trigger is created.
Read More: How to Create Database in SQL Server?
Example of After insert trigger:
CREATE TRIGGER trigger_insert ON Sales_record AFTER INSERT AS BEGIN SELECT * from inserted END INSERT INTO Sales_record VALUES ('USA','Pendrive',1000,5000)
Example of After update trigger:
CREATE TRIGGER trigger_update ON Sales_record FOR UPDATE AS BEGIN SELECT * from inserted SELECT * from deleted END UPDATE Sales_record SET SalesAmount=10000 WHERE Product= 'Pendrive'
The blog “Stored Procedures and Triggers in SQL” gives you a depth idea of two important database concepts that are frequently used by programmers. As a database expert, you should know how to use stored procedures, functions, and databases in database apps. In case of any confusion, take online SQL Server training first before you start applying for jobs.
Register for a Demo class first to get a clear idea of the online learning environment and our certified mentors.
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
MS SQL Server
Receive Latest Materials and Offers on SQL Server Course