Instead of Triggers

A weird feature in SQL Server is the INSTEAD OF trigger.  With this trigger you can divert the database to do something other than what the user/application wanted.  You can have INSTEAD OF triggers for insert/update/delete.

Where would this be useful?  I’ve had application that have to be changed, but the source code is no longer around (had a client with this problem).  The business wanted change what data was written to the database, but they could change the application.  So I added an INSTEAT OF trigger to check the data before it was inserted/updated and have the corrected values inserted/updated instead.

Here is a real simple example.  A test table is created and loaded with one record.  An insert INSTEAD OF trigger is added.  This trigger just returns, so the insert never happens.  No matter what INSERT statement the user/application tries it just gets ignored.

CREATE TABLE Test (
      TestID            int IDENTITY(1,1) NOT NULL
,     Name        varchar(64)             NULL
,   CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([TestID] ASC) 
)
GO

INSERT INTO Test (Name)
VALUES ('Hello World')
GO

SELECT *
  FROM dbo.Test
GO

CREATE TRIGGER tiTest ON Test INSTEAD OF INSERT
AS
BEGIN
      RETURN
END
GO

INSERT INTO Test (Name)
VALUES ('Good Bye World')
GO

SELECT *
  FROM dbo.Test
GO

So you can see here the before and after of the Insert statement.  It just gets ignored.

image

 

Here are a couple of msdn articles (using and designing)  about INSTEAD OF triggers if you want more info.

[tweetmeme only_single=”false”]
Advertisements

Got something to add?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s