SQL 防止表的DROP和DELETE

最新推荐文章于 2022-01-25 15:31:19 发布

sh1618

最新推荐文章于 2022-01-25 15:31:19 发布

阅读量797

点赞数

文章标签： delete sql database table command go

    USE NewVeg
    --指定某些表不能被drop
    IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class = 0 AND name = 'tri_avoidDrop')
        DROP TRIGGER tri_avoidDrop ON DATABASE
    GO
    CREATE TRIGGER tri_avoidDrop
    ON DATABASE
    FOR DROP_TABLE
    AS
    --指定某些表不能被drop
        if eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)') in (select Table_name from dbo.NO_DROP_TABLE)
        begin
            PRINT '此表内有数据嘿，别jb乱删！'
            ROLLBACK
        end
    GO
    --测试：
    /*
    表内有数据嘿，别jb乱删！
    Msg 3609, Level 16, State 2, Line 1
    The transaction ended in the trigger. The batch has been aborted.
    */
    drop table
    /*
    Command(s) completed successfully.
    */
    DROP TRIGGER tri_avoidDrop ON DATABASE
    GO
    ---
      USE NewVeg
       --指定某些表不能被delete
alter TRIGGER on_delete_t_card
ON dbo.t_card
FOR DELETE
AS
BEGIN
     rollback;
END