Avoid hardcoding when changing values in SQL Server trigger

Question

I have a sql server "instead of insert" trigger that populates a single column (PromoCode). It all works perfectly, but I don't like the fact I have had to hardcode the columns in actual INSERT statement:

CREATE TRIGGER PopulateOrderPromoCode ON Order
INSTEAD OF INSERT
AS BEGIN
    --// Get the Promo Code
    DECLARE @PromoCode int; 
    EXEC GetPromoCode @PromoCode OUTPUT;  

    --// Insert the order with the new Promo Code
    INSERT INTO Order (Id, CustomerId, PromoCode)
      SELECT Id, CustomerId, @PromoCode FROM inserted;
END

I would prefer to simply replace the value inside inserted.PromoCode with @PromoCode and then could use:

INSERT INTO Order 
  SELECT * FROM inserted;

Can this be done?

Answer 1

INSERTED is a read only temporary table which can be accessed in any trigger. You can not modify it.

And the way you are using to perform an INSERT is the best way. So, there's nothing wrong with it. Its good to specify the columns while performing an INSERT (according to me).

Answer 2

Dynamic SQL would be your only other option. Try this:

CREATE TRIGGER PopulateOrderPromoCode 
ON  Order
INSTEAD OF INSERT
AS 
BEGIN    
    --// Get the Promo Code    
    DECLARE @PromoCode int;         
    EXEC GetPromoCode @PromoCode OUTPUT;      

    DECLARE @InsertSQL nvarchar(2000), @SelectSQL nvarchar(2000)
    SET @InsertSQL = 'INSERT INTO Order ('
    SET @SelectSQL = 'SELECT '

    DECLARE @CurrentCol sysname
    SET @CurrentCol = ''



    WHILE EXISTS ( SELECT TOP 1 QUOTENAME(name)
        FROM sys.syscolumns 
        WHERE object_name(id) = 'Order'
        AND  name <> 'PromoCode'
        AND  name > @CurrentCol)
    BEGIN
     SET @CurrentCol = ( SELECT TOP 1 QUOTENAME(name)
          FROM sys.syscolumns 
          WHERE object_name(id) = 'Order'
          AND  name <> 'PromoCode'
          AND  QUOTENAME(name) > @CurrentCol
          ORDER BY name)
     IF @CurrentCol IS NULL Break;

     SET @InsertSQL = @InsertSQL + @CurrentCol + ', '
     SET @SelectSQL = @SelectSQL + @CurrentCol + ', '
    END

    --Finish and concatenate the strings
    SET @InsertSQL = @InsertSQL + 'PromoCode) '
    SET @SelectSQL = @SelectSQL + '''' + @PromoCode + '''' + ' FROM INSERTED'

    DECLARE @MasterSQL nvarchar(2000)
    SET @MasterSQL = @InsertSQL + @SelectSQL

    EXEC (@MasterSQL)
END

BTW - "order" is a poor choice for a table name - it's also a reserved word in SQL. Try Orders or OrderHeader.

Answer 3

Don't use an INSTEAD OF INSERT trigger (in which you have to take over the insert logic)

Use a normal INSERT trigger (which allows you to do stuff in addition to the insert)

This assumes you can insert without a promo code (allows nulls) or the promo code defaults to something.

CREATE TRIGGER PopulateOrderPromoCode ON Order
FOR INSERT
AS 
BEGIN
    --// Get the Promo Code
    DECLARE @PromoCode int;     
    EXEC GetPromoCode @PromoCode OUTPUT;        

    --// update the order with the new Promo Code
    UPDATE Order SET PromoCode = @PromoCode
      WHERE ID IN (SELECT ID FROM inserted)
END