bear valley bear valley - 6 months ago 9
SQL Question

MS SQL Trigger on Insert alter input

I am fairly new with MS SQL and am struggling with creating a trigger that does what I need.

We have a table that before a record is inserted we want to manipulate the input value.

Example Table:

MyTable ( [RUID] INT IDENTITY(1,1)
,[CustomerName] nvarchar(200)
,[CustomerStatus] INT
,[CustomerType] char(1)
,[OtherFields] nvarchar(100)
)


An insert may come in like:

INSERT INTO MyTable ([CustomerName], [CustomerStatus], [CustomerType], [OtherFields])
'LName~FName',2,'A','Other Info'


We don't have control of the source system doing the insert (a vendor product that is on its way out in a couple years so management doesn't want to spend the sums of money to have them alter it) but we need something like this to happen.

CustomerStatus that is inserted to be 2x Inserted value

CustomerType - Regardless of the value sent we want to be overridden with a value of 'B'

All other fields left as-is

So with an insert sent with the values in the example above, we would actually want this to end up in the table:

'LName~FName',4,'B','Other Info'

any help you could provide would be greatly appreciated.

Specs:
MS SQL Server 2008r2 Standard Edition (Database is in SQL 2000 Compatibility mode though)

Answer

You basically need an Instead of trigger. The code would look something like...

CREATE TRIGGER tr_Insert_MyTable
ON MyTable
INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;

INSERT INTO MyTable ([CustomerName], [CustomerStatus], [CustomerType], [OtherFields])
SELECT [CustomerName]
     , [CustomerStatus] * 2 AS [CustomerStatus]
     ,'B' [CustomerType]
     , [OtherFields]
FROM inserted


END