locked
Table Constraints RRS feed

  • Question

  • I have to create table with no provision of deletion.

    Is it possible?

    Wednesday, March 15, 2017 11:06 AM

Answers

  • Create a trigger to roll back deletes.
    create table test(PK int identity primary key, charcol char(20))
    insert into test(charcol) values('test')
    GO
    create trigger testtrigger on test for delete
    as
    rollback tran
    GO
    delete from test 
    /*
    Msg 3609, Level 16, State 1, Line 8
    The transaction ended in the trigger. The batch has been aborted.
    
    */

    • Marked as answer by VINOTH KS Friday, March 17, 2017 2:59 AM
    Wednesday, March 15, 2017 11:19 AM

All replies

  • A foreign key constraint will prevent deletion as long as related rows exist. To prevent deletes entirely, you can use an INSTEAD OF trigger.

    CREATE TRIGGER trd_Example_IgnoreDelete
    ON dbo.Example
    INSTEAD OF DELETE 
    AS
    --perform no action
    RETURN;
    GO


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, March 15, 2017 11:17 AM
    Answerer
  • Create a trigger to roll back deletes.
    create table test(PK int identity primary key, charcol char(20))
    insert into test(charcol) values('test')
    GO
    create trigger testtrigger on test for delete
    as
    rollback tran
    GO
    delete from test 
    /*
    Msg 3609, Level 16, State 1, Line 8
    The transaction ended in the trigger. The batch has been aborted.
    
    */

    • Marked as answer by VINOTH KS Friday, March 17, 2017 2:59 AM
    Wednesday, March 15, 2017 11:19 AM
  • The same what i have tried already, but when i use truncate operation the trigger would not help to prevent.

    Wednesday, March 15, 2017 12:03 PM
  • A delete trigger is not executed on truncate table. 

    Please see:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/edc11f17-a717-4268-b7b8-b6c6dff4bc91/disable-or-block-truncate-command?forum=transactsql

    Wednesday, March 15, 2017 12:14 PM
    Answerer
  • The same what i have tried already, but when i use truncate operation the trigger would not help to prevent.

    If you do not already have a foreign key referencing the table, you could add a dummy one referencing the table to prevent a truncate:

    CREATE TABLE dbo.PreventTruncate(
    	PreventTruncate int NOT NULL
    		CONSTRAINT FK_PreventTruncate FOREIGN KEY
    		REFERENCES dbo.Example(ExampleID)
    );
    GO


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, March 15, 2017 12:20 PM
    Answerer
  • it works!, it enough to avoid accidental delete in a particular table which has no reference with other table.

    Thanks Mr.Hilary Cotter 

    Friday, March 17, 2017 3:03 AM