Ryan Whitaker

Dishes of Ryan

Modify data before it's inserted in SQL Server 2000/2005

I had a scenario where I needed to massage some data in this one particular column before it got inserted into the database.

I usually like to implement this kind of logic alongside its business brethren, leaving as much business brouhaha out of the raw database as possible, but in this case I was straddling two apps.  One old one, one new one, both using the same database.  Problem was, I didn't want to pull down the old code from the Vault, recompile, and post a newish-old version.  Long story short, I implemented an INSTEAD OF trigger on the table to massage the data before it got inserted.  Boom, data massage for both apps.

/* This trigger makes sure that the data being inserted into
   the BarColumn column starts with a dash.  If it doesn't, it
   will prepend one. */

CREATE TRIGGER FooTable_Insert ON dbo.FooTable
INSTEAD OF INSERT
AS
DECLARE @BarColumn varchar(50)
SET @BarColumn = (SELECT BarColumn FROM INSERTED)
IF (LEFT(@BarColumn, 1) <> '-')
BEGIN
     SET @BarColumn = '-' + @BarColumn
     SELECT * INTO #Inserted FROM Inserted
     UPDATE #Inserted SET BarColumn = @BarColumn
     INSERT INTO FooTable SELECT * FROM #Inserted
END
ELSE
     INSERT INTO FooTable SELECT * FROM Inserted

Posted: Feb 02 2006, 11:48 PM by RyanW | with 4 comment(s)
Filed under:

Comments

Thomas said:

Exactly what I was looking for, but I have some small adjustments:

USE [RADIUS]

GO

/****** Objekt:  Trigger [dbo].[CheckDateTimes]    Skriptdatum: 03/29/2008 10:32:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE TRIGGER [dbo].[BusinessEntity_CheckDateTimes]

  ON  [dbo].[BusinessEntity]

  FOR INSERT,UPDATE

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @CreateDate DateTime

DECLARE @LastModified DateTime

DECLARE @INDEX Int

SET @CreateDate = (SELECT CreateDate from INSERTED)

SET @LastModified = (SELECT LastModify from INSERTED)

SET @INDEX = (SELECT BusinessEntityIndex from INSERTED)

-- SELECT * INTO #Inserted FROM Inserted

IF (@CreateDate = {ts '1753-01-01 00:00:00.000'})

UPDATE BusinessEntity SET CreateDate = NULL WHERE BusinessEntityIndex = @INDEX

IF (@LastModified = {ts '1753-01-01 00:00:00.000'})

UPDATE BusinessEntity SET LastModify = NULL WHERE BusinessEntityIndex = @INDEX

   -- INSERT INTO BusinessEntity SELECT * FROM #Inserted

   -- Insert statements for trigger here

END

GO

CREATE TRIGGER [dbo].[Groups_CheckDateTimes]

  ON  [dbo].[Groups]

  FOR INSERT,UPDATE

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @Date DateTime

DECLARE @INDEX Int

SET @INDEX = (SELECT GroupIndex from INSERTED)

SET @Date = (SELECT AcctExpireDate from INSERTED)

IF (@Date = '1753-01-01 00:00:00.000')

UPDATE Groups SET AcctExpireDate = NULL WHERE (GroupIndex = @INDEX)

SET @Date = (SELECT AcctStartDate from INSERTED)

IF (@Date = '1753-01-01 00:00:00.000')

UPDATE Groups SET AcctStartDate = NULL WHERE (GroupIndex = @INDEX)

END

GO

CREATE TRIGGER [dbo].[Users_CheckDateTimes]

  ON  [dbo].[users]

  FOR INSERT,UPDATE

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @Date DateTime

DECLARE @INDEX Int

SET @INDEX = (SELECT UserIndex from INSERTED)

SET @Date = (SELECT PasswordDate from INSERTED)

IF (@Date = '1753-01-01 00:00:00.000')

UPDATE users SET PasswordDate = NULL WHERE (UserIndex = @INDEX)

SET @Date = (SELECT StartDate from INSERTED)

IF (@Date = '1753-01-01 00:00:00.000')

UPDATE users SET StartDate = NULL WHERE (UserIndex = @INDEX)

SET @Date = (SELECT UserExpiryDate from INSERTED)

IF (@Date = '1753-01-01 00:00:00.000')

UPDATE users SET UserExpiryDate = NULL WHERE (UserIndex = @INDEX)

SET @Date = (SELECT LockoutTime from INSERTED)

IF (@Date = '1753-01-01 00:00:00.000')

UPDATE users SET LockoutTime = NULL WHERE (UserIndex = @INDEX)

END

GO

CREATE TRIGGER [dbo].[UserDetails_CheckDateTimes]

  ON  [dbo].[userDetails]

  FOR INSERT,UPDATE

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @Date DateTime

DECLARE @INDEX Int

SET @INDEX = (SELECT UserIndex from INSERTED)

SET @Date = (SELECT CreateDate from INSERTED)

IF (@Date = '1753-01-01 00:00:00.000')

UPDATE UserDetails SET CreateDate = NULL WHERE (UserIndex = @INDEX)

SET @Date = (SELECT LastModify from INSERTED)

IF (@Date = '1753-01-01 00:00:00.000')

UPDATE UserDetails SET LastModify = NULL WHERE (UserIndex = @INDEX)

SET @Date = (SELECT LastTotalOnlineUpdate from INSERTED)

IF (@Date = '1753-01-01 00:00:00.000')

UPDATE UserDetails SET LastTotalOnlineUpdate = NULL WHERE (UserIndex = @INDEX)

END

This makes it easier to maintain for my purpose

# March 29, 2008 8:10 AM

Waylon said:

Have you guys tried a batch update on the table(s) with your proposed triggers active?

# July 16, 2008 2:59 PM

Hernan said:

you can use table variables... @table

# February 17, 2009 5:09 PM

ilias said:

Waylon is right, the trigger will fail when more than one records are inserted / updated... The problem is the

SET @x = (SELECT x FROM INSERTED)

INSERTED will contain multiple records when the insert/update statement affects more than just one record

# July 14, 2009 3:13 AM
Leave a Comment

(required) 

(required) 

(optional)

(required)