SQL Server Performance

Preventing TRUNCATE TABLE

Discussion in 'SQL Server 2008 General DBA Questions' started by Righteousman, Sep 25, 2009.

  1. Righteousman New Member

    Hi Guys,
    Is there a way to prevent TRUNCATE TABLE in a DLL or DML trigger?
    Basically I want to prevent all TRUNCATE TABLE operations unless the trigger is explicitly disabled first.
  2. Righteousman New Member

    Also, is it possible to prevent a DELETE with no where clause and UPDATE with no where clause?
  3. Adriaan New Member

    Write a trigger that compares the record counts to prevent ALL rows to be affected by update or delete:
    CREATE TRIGGER test ON dbo.MyTable
    FOR UPDATE, DELETE
    AS
    DECLARE @TableCount INT, @DeleteCount INT

    SELECT @DeleteCount = COUNT(*) FROM deleted
    IF @DeleteCount > 0
    BEGIN
    SELECT @TableCount = COUNT(*) FROM dbo.MyTable
    IF @DeleteCount = @TableCount
    BEGIN
    RAISERROR ..........................
    ROLLBACK TRANS
    END
    END
    GO
  4. Adriaan New Member

    If a table has a foreign key, that usually blocks all TRUNCATE operations.
    Other than that, you need pretty elevated permissions to truncate a table, so how likely is it that this will happen in your database?
  5. Righteousman New Member

    hmmm, DDL triggers can't detect a TRUNCATE TABLE? Basically it's a safeguard in case the administrator (me and one other guy) accidentally run a TRUNCATE TABLE in production when we think we're connected to QA or DEV. It's not likely that it'll happen and it's never happened before, but the more safeguards in our system the better.
  6. FrankKalis Moderator

    It's just a small thing, but I find this connection colouring feature built into SSMS 2008 pretty useful. All connections to a production server are RED , QA is YELLOW, and dev is GREEN. Doesn't stop you from issuing a TRUNCATE TABLE though.
  7. chumeniuk New Member

    [quote user="Righteousman"]
    hmmm, DDL triggers can't detect a TRUNCATE TABLE? Basically it's a safeguard in case the administrator (me and one other guy) accidentally run a TRUNCATE TABLE in production when we think we're connected to QA or DEV. It's not likely that it'll happen and it's never happened before, but the more safeguards in our system the better.
    [/quote]
    Do you have to use a special login in production to gain elevated permissions? I would suggest not giving your main login any elevated permissions in production to avoid these situations. You would have to explicitly login to a production server to perform a TRUNCATE or DELETE.

Share This Page