bear valley bear valley - 6 months ago 11
SQL Question

SQL Server : trigger on Insert alter input

I am fairly new to SQL Server and I 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])
VALUES ('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 columns are 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:


  • SQL Server 2008 R2 Standard Edition (Database is in SQL Server 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