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