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

,othercolumn VARCHAR(10)

,deleterecord CHAR(1) DEFAULT('0') CHECK (deleterecord IN ('0', '1'))

,deletereason VARCHAR(200) DEFAULT(NULL))

GO

CREATE TRIGGER MyTable_delete ON MyTable

INSTEAD OF UPDATE, DELETE

AS BEGIN

UPDATE t

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'

END

GO

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.