Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to create a Trigger to pass parameter to stored procedure in SQL Server

Author: Richard Wand

This article will explain how we can create a Trigger to pass parameter to stored procedure.

 

 
In my previous article on DevASP.NET, I have explained about Triggers, there benefits and uses and I have explained some of disadvantages of Triggers. Triggers can be used inside stored procedures and you can also get values in a Trigger to pass as a parameter to stored procedure. In this article, I will show you that how we can create a trigger to pass a parameter value to stored procedure. This Trigger will be an INSERT Trigger and it will select last inserted identity value from the table and this value will be passed to stored procedure as parameter. This stored procedure simply executes a select statement and returns a values using WHERE clause and we will get this WHERE clause value from Trigger as parameter. I have used simple stored procedure and a simple Trigger just to show the functionality of passing value as parameter from Trigger.
 
I have used a Categories table of famous NORTHWND sample database for this example.
 
First we need to write a stored procedure that will be executed by Trigger. This stored procedure takes a parameter category ID and returns category name for that category ID.
 
CREATE PROCEDURE dbo.SelectProductsSP
(
      @CatID int
 )
AS
BEGIN
BEGIN TRANSACTION
 
SELECT CategoryName FROM Categories WHERE CategoryID = @CatID
 
IF(@@error = 0)
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END
RETURN

Next we need to write a Trigger. Following INSERT Trigger gets the last insert category id using MAX function and at the end it executes above mentioned stored procedure providing category ID as parameter.
 
CREATE TRIGGER TriggerInsertCategory
ON
Categories
FOR INSERT
AS
 
DECLARE @CatID int
DECLARE @LastInserted int
 
SET @LastInserted = 0
 
SET @LastInserted =
(
      SELECT MAX(CategoryID) FROM Categories
)
 
EXECUTE SelectProducts @CatID = @LastInserted
 
As the above Trigger is an INSERT Trigger on Categories table so it will execute only when we insert record into Categories table.  Write an INSERT statement to insert record into Categories table.
 
INSERT INTO dbo.Categories(CategoryName, Description) VALUES('New Category', 'New Category with new Description')

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< What is trigger in SQL Server?

Disclaimer - Privacy
© 2002-2017 DevASP.net