Journal Journal: SQL Trigger that turns insert into update
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])
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