Instead Insert Trigger

Question

Can someone give me the skeleton body of an Instead of Insert Trigger for MSSQL. I am trying to test the largest value in an Purchase Order Column (which is an integer) and on insert it grabs the largest value increments that by one and inserts that as the Purchase Order ID. The column was not setup with an Auto Increment option so I am getting around that with a Trigger.

Thanks.

Answer 1

Here's how to change your table to include an identity column.

1. Create a new table with the same structure, but with an identity column on Purchase Order ID. You can use "script table as" and just change the line for Purchase Order Id, like:

   [Purchase Order Id] int identity primary key,

2. Turn on identity insert on the new table:

   SET IDENTITY INSERT NewTable ON

3. Copy over the data:

   INSERT INTO NewTable (Columns) SELECT * FROM CurrentTable

4. Turn identity insert off:

   SET IDENTITY INSERT NewTable OFF

5. Rename (or drop) the old table so it is no longer used:

   EXEC sp_rename 'CurrentTable', 'BackupTable';

6. Move the new table in:

   EXEC sp_rename 'NewTable', 'CurrentTable';

Now you have a nice identity column, which is much better than nasty triggers.

Answer 2

As described by Andomar, creating a new table to support your specifc requirements appropriately would be the ideal course of action in my view.

That said, should you however wish to go down the Instead of Insert Trigger route, then the following Microsoft reference provides a detailed example.

http://msdn.microsoft.com/en-us/library/ms175089.aspx