How to Create Stored Procedure & Trigger in SQL Server

Difference between stored procedures and triggers

	Stored Procedure	Trigger
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.

Introduction

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.

Read: All About SQL Joins and Subqueries

What is Stored Procedure?

A stored procedure is a group of t-SQL statements that are stored as compiled form in the database to perform a specific task.

How to create a Stored Procedure?

Syntax : CREATE PROCEDURE Procedure_name

 (
 Inputparameter datatype,
 Outputparameter datatype OUT,   
 )
 AS 
 BEGIN
 SQL -Statements
 END

How to execute Stored procedure?

Syntax: EXEC Procedure_name OR EXECUTE Procedure_name

Different types of Stored procedures

Stored procedures can be classified into following categories:

1. System Stored procedures
2. User Defined Stored procedures

System Stored procedures

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

Read: What are Data Types and Their Usage in SQL Server Tables?

User-defined Stored procedure

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.

Stored procedure without input parameter

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

Stored procedure with an input parameter

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:

Read: SAS Tutorial Guide for Beginners

CREATE PROCEDURE usp_SalesbyCountry
(
@Country  varchar(20)
)
AS 
BEGIN
SELECT Country, SUM(SalesAmount) AS TotalSalesAmount
FROM Sales_record 
WHERE Country=@Country
GROUP BY Country
END

EXEC usp_SalesbyCountry 'USA'

Stored procedure with input and output parameters

We can also return the TotalSalesAmount of each country using the output parameter. The following example explains how to achieve so using output parameters:

Read More: How to Clear SQL Server Transaction Log File with DBCC

CREATE PROCEDURE usp_SalesbyCountry_with_out
(
@Country  varchar(20),
@sum_amount int OUT
)
AS 
BEGIN
SELECT @sum_amount=SUM(SalesAmount) 
FROM tbl_Sales2 
WHERE Country=@Country
GROUP BY Country
END

DECLARE @sum_amount int
EXEC usp_SalesbyCountry_with_out 'USA',@sum_amount=@sum_amount OUT
SELECT @sum_amount AS Total_saleamount_USA

What is a trigger?

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.

How to create triggers?

Syntax: CREATE TRIGGER trigger_name ON {FOR|AFTER|INSTEADOF} {INSERT|DELETE|UPDATE} AS SQL statements

Different types of triggers

Triggers are classified into following categories:

1. DML Triggers
2. DDL Triggers

DML triggers

DML triggers are fired in response to DML events such as insert, update, delete operations. DML triggers are further subdivided into the following subcategories:

1. AFTER triggers or FOR triggers
2. INSTEAD OF triggers

Read: Normalization-How to Normalize Database in a World of Abnormalities

INSTEAD OF triggers

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.

Read More: How to Increase the Speed of SQL Query Execution

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

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

AFTER triggers

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'

What’s Next?

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.

Read: SQL‌ ‌Server‌ ‌DBA‌ ‌Roles‌ and‌ ‌Responsibilities:‌ ‌What‌ ‌Should‌ ‌You‌ ‌Know?‌ ‌

FaceBook Twitter LinkedIn Pinterest Email