You can avoid inadvertant DELETEs or UPDATEs that mark the record as deleted (without a reason) like this:
CREATE TABLE MyTable
(MyPK INT PRIMARY KEY
,deleterecord CHAR(1) DEFAULT('0') CHECK (deleterecord IN ('0', '1'))
,deletereason VARCHAR(200) DEFAULT(NULL))
CREATE TRIGGER MyTable_delete ON MyTable
INSTEAD OF UPDATE, DELETE
SET deleterecord = i.deleterecord
,deletereason = i.deletereason
,othercolumn = i.othercolumn
FROM MyTable t
INNER JOIN INSERTED i ON t.MyPK = i.MyPK
WHERE (i.deleterecord = '1' AND i.deletereason IS NOT NULL) or i.deleterecord = '0'
INSERT INTO MyTable (MyPK)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
SELECT * FROM MyTable
DELETE FROM MyTable WHERE MyPK = 1
UPDATE MyTable SET othercolumn = 'EXAMPLE' WHERE MyPK = 1
UPDATE MyTable SET deleterecord = '1' WHERE MyPK = 2
UPDATE MyTable SET deleterecord = '1', deletereason = 'MY CHOICE' WHERE MyPK = 3
SELECT * FROM MyTable
DROP TRIGGER MyTable_delete
DROP TABLE MyTable
However you will have to check in your application that a delete action (mark deleted) includes a reason because it is not easy in a trigger to return an error back to the client telling you when the mark deleted action doesn't include a reason.
I suppose you could raise an error and then check for it in the client but I've never tried that.
Also, you'll need to add additional columns to the SET within the UPDATE of the trigger, to also apply any other columns' new values (as obtained from the INSERTED psuedotable) as I have done with the othercolumn column.
I was also a bit weirded out by your suggestion that your assigning a FK check constraint to the deleterecord column when all you need is a simple CHECK constraint like the one I did above.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!
My thought question: Have you ever been told that your query runs too fast?
My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables
, an Easter SQL
, Time Slots
and Self-maintaining, Contiguous Effective Dates in Temporal Tables