Post reply

Trigger Delete

  • December 23, 2010 at 6:24 am

    #229089

    Is it possible to have a trigger that can catch delete of an entire table and roll back

    Ie if someone was to delete * from table would the tigger be able to catch this but still allow single entry deletes

    Thanks

    Gary

  • December 23, 2010 at 6:49 am

    #1265895

    Answer to my own question

    ALTER TRIGGER [trgPreventDeletion]

    ON [dbo].[AUDITACCESS]

    AFTER DELETE

    AS

    IF (SELECT COUNT(*) FROM DELETED) > 1

    BEGIN

    RAISERROR ('Audit access entries cannot be deleted', 16, 10) WITH LOG

    ROLLBACK TRANSACTION;

    END

    Is this ok seems to work

  • December 23, 2010 at 7:04 am

    #1265913

    So users are allowed to delete 1 row, but not 2?

    To check for entire table, similar thing, but check and see if count(*) from deleted = count(*) from base table

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 23, 2010 at 7:10 am

    #1265919

    this seems to wrok for me; if i put it on a table that has FK's pointing to it, the constraints for the FK are enforced before the trigger fires.

    CREATE TRIGGER TR_SINGLE_ROW_DELETES_ONLY ON YOURTABLE

    FOR DELETE

    AS

    BEGIN

    DECLARE @ROWSAFFECTED INT

    SELECT @ROWSAFFECTED = COUNT(*) FROM DELETED

    IF @ROWSAFFECTED > 1

    BEGIN

    RAISERROR('Multi Row Deletes are not permitted.',16,1)

    ROLLBACK TRAN

    END

    END

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 23, 2010 at 7:11 am

    #1265920

    GilaMonster (12/23/2010)

    To check for entire table, similar thing, but check and see if count(*) from deleted = count(*) from base table

    Sounds a bit risky to me... what if another row has been inserted or deleted by another user concurrently?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply