Follow Slashdot blog updates by subscribing to our blog RSS feed

 



Forgot your password?
typodupeerror
Check out the new SourceForge HTML5 internet speed test! No Flash necessary and runs on all devices. ×
User Journal

Journal 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
This discussion has been archived. No new comments can be posted.

SQL Trigger that turns insert into update

Comments Filter:

Whom computers would destroy, they must first drive mad.

Working...