joke_dst's Journal: SQL Trigger that turns insert into update

-- When something is INSERTed into this table and the post already exists, does an UPDATE instead

Create TRIGGER PriceManager.Trigger_PriceList_Insert
   ON  TheDB.PriceList
   INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    --Update if exists
    UPDATE [PriceManager].[PriceList]
       SET [SalesPrice] = isnull(inserted.[SalesPrice],PriceList.[SalesPrice])
          ,[OnRequest] = isnull(inserted.[OnRequest],PriceList.[OnRequest])
          ,[ChangedTime] = isnull(inserted.[ChangedTime],PriceList.[ChangedTime])
          ,[ChangedBy] = isnull(inserted.[ChangedBy],PriceList.[ChangedBy])
    from inserted
     WHERE inserted.[Company] = PriceList.[Company]
          and inserted.[ItemNumber] = PriceList.[ItemNumber]
          and inserted.[Date] = PriceList.[Date]
          and inserted.[Type] = PriceList.[Type]
          and inserted.[PriceListId] = PriceList.[PriceListId]

    --insert if don't
    insert into [PriceManager].[PriceList]
    select * from inserted
    where not exists (
        select 1 from [PriceManager].[PriceList] p2
        WHERE inserted.[Company] = p2.[Company]
          and inserted.[ItemNumber] = p2.[ItemNumber]
          and inserted.[Date] = p2.[Date]
          and inserted.[Type] = p2.[Type]
          and inserted.[PriceListId] = p2.[PriceListId]
        )
END