Have Geek Brain

Avoid hardcoding when changing values ​​in a SQL Server trigger

I have a sql server instead of an insert trigger that populates one column (PromoCode). Everything works fine, but I don't like the fact that I had to hardcode the columns in the 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 rather just replace the value inside the inserted .PromoCode with @PromoCode and then use:

INSERT INTO Order 
  SELECT * FROM inserted;

Can this be done?

+1


a source to share


3 answers


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

Use a regular INSERT trigger (which allows you to do things in addition to inserting)



This assumes that you can insert no promo code (allows null) or default promo code.

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
+2


a source


INSERTED is a read-only temporary table that can be accessed in any trigger. You cannot change it.



And the way you use INSERT is the best way. So there is nothing wrong with that. It's good to specify columns when doing INSERT (for me).

0


a source


Dynamic SQL will be your only other option. Try the following:

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 bad choice for a table name - it is also a reserved word in SQL. Try Orders or OrderHeader.

-2


a source






More articles:


All Articles