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

253 Comments

Comments have been disabled for this content.