Introduction to Triggers

A trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs. You specify the modification action(s) that fire the trigger when it is created.
The following shows how to create a trigger that displays the current system time when a row is inserted into the table to which it is attached.


SET NOCOUNT ON
CREATE TABLE Source (Sou_ID int IDENTITY, Sou_Desc varchar(10))
go
CREATE TRIGGER tr_Source_INSERT
ON Source
FOR INSERT
AS
PRINT GETDATE()
go
INSERT Source (Sou_Desc) VALUES ('Test 1')
-- Results --Apr 28 2001 9:56AM

When to Use Triggers
There are more than a handful of developers who are not real clear when triggers should be used. I only use them when I need to perform a certain action as a result of an INSERT, UPDATE or DELETE and ad hoc SQL (aka SQL Passthrough) is used. I implement most of my data manipulation code via stored procedures and when you do this the trigger functionality can be moved into the procedure. For example, let's say you want to send an email to the Sales Manager when an order is entered whose priority is high. When ad hoc SQL is used to insert the Orders row, a trigger is used to determine the OrderPriority and send the email when the criteria is met. The following shows a partial code listing of what this looks like.

SQL SERVER – Insert Values of Stored Procedure in Table – Use Table Valued Function

/* Create Stored Procedure */
CREATE PROCEDURE TestSP
AS
SELECT GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
GO
Traditional Method:
/* Create TempTable */
CREATE TABLE #tempTable (MyDate SMALLDATETIME, IntValue INT)
GO
/* Run SP and Insert Value in TempTable */
INSERT INTO #tempTable (MyDate, IntValue)
EXEC TestSPGO
/* SELECT from TempTable */
SELECT *FROM #tempTable
GO
/* Clean up */
DROP TABLE #tempTable
GO
Alternate Method: Table Valued Function
/* Create table valued function*/
CREATE FUNCTION dbo.TestFn()
RETURNS @retTestFn TABLE
(
MyDate SMALLDATETIME,IntValue INT
)
AS
BEGIN
DECLARE @MyDate SMALLDATETIME
DECLARE @IntValue
INTINSERT INTO @retTestFnSELECT GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
RETURN;
END
GO
/* Select data from Table Valued Function */
SELECT *FROM dbo.TestFn()
GO