LearnItFirst User Forum

/*
    Author: Scott Whigham from http://www.LearnSqlServer.com/

    Description: This script demos how to perform a logical delete/ghost delete/virtual delete with an INSTEAD OF trigger. The INSTEAD OF trigger intercepts the user's request
      to perform a deletion and, instead of deleting the row(s), it marks a "Status" bit column.
   
    Versions: SQL Server 2005, 2000
   
    Creation Date: August 28, 2006

    For more scripts like this one, visit http://forums.learnsqlserver.com/codesamples.aspx
*/
CREATE TABLE Products (
    ProductID INT NOT NULL PRIMARY KEY
    , ProductName VARCHAR(50)
    , Status BIT )
GO
CREATE TRIGGER tr_DELETE_Products
ON Products INSTEAD OF DELETE
AS
UPDATE Products
    SET Status = 0
    FROM Products P JOIN deleted D
    ON P.ProductID = D.ProductID
GO
INSERT Products VALUES (1, 'Muffler', 1)
INSERT Products VALUES (2, 'Carb', 1)
INSERT Products VALUES (3, 'Exhaust pipe', 1)
INSERT Products VALUES (4, 'Tire', 1)

SELECT * FROM Products

--============================================
DELETE FROM Products WHERE ProductID = 4

-- Did it delete? No!
SELECT * FROM Products WHERE ProductId = 4