SQL Server : trigger on Insert alter input

Question

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)

If CustomerType is always 'B', why not just make that column a computed column? Or, are there other cases where you might want to insert a value other than 'B' (which means a trigger will overwrite those too)? — hatchet - done with SOverflow
– hatchet - done with SOverflow, Commented May 26, 2016 at 22:48
Good point, but in this case I had simplified the actual issue greatly for example. Basically the table is a Queue for automation to process out of we want all requests that enter the queue to be brought into a single status that puts them in waiting until another process comes and checks them for before processing of the requests continues. Once that batch process has finished then the status will be changed so the automation servers know to process the requests, then the status is yet changed again to mark them as completed. — bear valley
– bear valley, Commented May 27, 2016 at 18:54

M.Ali · Accepted Answer · 2016-05-26 22:49:06Z

1

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

answered May 26, 2016 at 22:49

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bear valley Over a year ago

Thank you, I think this is exactly what I'm looking for.

Collectives™ on Stack Overflow

SQL Server : trigger on Insert alter input

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related