Stop deletes on a table

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  January 14, 2016 at 9:52 am

  #1851989

  Eirikur Eiriksson (1/14/2016)

  ---

  krypto69 (1/14/2016)

  ---

  Thanks everyone - very intersting input.

  I ended up using:

  CREATE TRIGGER NoDeleteMedicalGroup ON dbo.Medical_Group

  INSTEAD OF DELETE

  AS

  BEGIN

  ROLLBACK;

  END;

  No items in the Medical group table should ever be deleted. So, this worked for our needs.

  Thanks again everyone - most helpful.

  Quick advice, don't do a rollback in the trigger, no relevant transaction to undo. You are risking unpredictable behavior with this!

  😎

  it wont prevent TRUNCATE either

  using Eirikurs demo code

  USE tempdb;

  GO

  SET NOCOUNT ON;

  IF OBJECT_ID(N'dbo.TBL_TEST_DELETE_TRIGGER') IS NOT NULL DROP TABLE dbo.TBL_TEST_DELETE_TRIGGER;

  CREATE TABLE dbo.TBL_TEST_DELETE_TRIGGER

  (

  TDT_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_DELETE_TRIGGER_TDT_ID PRIMARY KEY CLUSTERED

  ,TDT_XVAL INT NOT NULL

  );

  GO

  CREATE TRIGGER dbo.TRG_TEST_DELETE_TRIGGER_NO_DELETE

  ON dbo.TBL_TEST_DELETE_TRIGGER

  INSTEAD OF DELETE

  AS

  BEGIN

  DECLARE @X INT = 0;

  --RAISERROR ('NO DELETES ALLOWED!',0,0) WITH NOWAIT; --optional

  END

  GO

  INSERT INTO dbo.TBL_TEST_DELETE_TRIGGER(TDT_XVAL)

  VALUES (0),(1),(2),(3),(4),(5);

  SELECT

  TDT.TDT_ID

  ,TDT.TDT_XVAL

  FROM dbo.TBL_TEST_DELETE_TRIGGER TDT;

  --DELETE FROM dbo.TBL_TEST_DELETE_TRIGGER;

  TRUNCATE TABLE dbo.TBL_TEST_DELETE_TRIGGER;

  SELECT

  TDT.TDT_ID

  ,TDT.TDT_XVAL

  FROM dbo.TBL_TEST_DELETE_TRIGGER TDT;

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day