CODEWITHSUNDEEP
sqlsql-servertriggers
bokkie
bokkie

Reputation: 1590

SQL Triggers for Delete and Insert too

I have a trigger that looks like this:

ALTER TRIGGER [dbo].[trSeasonHotelsSupplierOfferUpdate]
  ON  [dbo].[SeasonHotelsSupplierOffers]
  AFTER UPDATE
AS
BEGIN
  IF ((SELECT COUNT(*) FROM inserted) = 1 AND (SELECT COUNT(*) FROM deleted) = 1)
  BEGIN
       INSERT INTO SeasonHotelsSupplierOffersHistory (SeasonHotelSupplierOfferID, ColumnEN, OldValue, NewValue, UpdateDate, UpdatedByID)
         SELECT 
            i.SeasonHotelSupplierOfferID, 'Name', d.Name, i.Name, i.UpdateDate, i.UpdatedByID 
         FROM 
            inserted i, deleted d       
         WHERE 
            ISNULL(i.Name,'')<>ISNULL(d.Name,'')
    END
END

I need to make this work for inserted data and deleted data too. This all saves into a table called SeasonHotelsSupplierOffersHistory that has these columns:

SeasonHotelSupplierOfferID, ColumnEN, OldValue, NewValue, UpdateDate, UpdatedByID

I know that I have to say After Updated, Delete, Insert`, but that's about it.

Thanks

Upvotes: 0

Views: 289

Answers (1)

GarethD
GarethD

Reputation: 69749

I am not 100% sure what you are asking as the only actual question appears to be how to get the trigger to work for INSERT and DELETE, but you have already answered this within your question. I can only assume you are struggling with getting the insert within the trigger to work for inserts and deletes to. I would imagine something like this would work.

CREATE TRIGGER trSeasonHotelsSupplierOfferUpdate 
ON dbo.SeasonHotelsSupplierOffers
AFTER UPDATE, INSERT, DELETE
AS
BEGIN
    INSERT  SeasonHotelsSupplierOffersHistory (SeasonHotelSupplierOfferID, ColumnEN, OldValue, NewValue, UpdateDate, UpdatedByID)
    SELECT  COALESCE(inserted.SeasonHotelSupplierOfferID, deleted.SeasonHotelSupplierOfferID),
            'Name', 
            deleted.Name, 
            inserted.Name, 
            COALESCE(inserted.UpdateDate, deleted.UpdateDate),
            COALESCE(inserted.UpdatedByID, deleted.UpdatedByID)
    FROM    inserted
            FULL JOIN deleted
                ON inserted.SeasonHotelSupplierOfferID = deleted.SeasonHotelSupplierOfferID
    WHERE   COALESCE(inserted.Name, '') != COALESCE(deleted.Name, '')   -- ONLY INSERT WHERE NAME HAS CHANGED
END

You will be able to identify inserts as OldValue will be null, and deletes as NewValue will be null.

Upvotes: 1

Related Questions