DDL Trigger to prevent delete all for at-least 20 table in a database

Question

Post reply

DDL Trigger to prevent delete all for at-least 20 table in a database

saroj9958

Mr or Mrs. 500

Points: 598
More actions
May 17, 2016 at 12:28 pm

#310765

Hi,
Is there any way to prevent delete all statement for some table (at-least 20 table). I need to set this for my database for some specific table only although I can create trigger for particular table but but our DBA does not allow us to create lot's of trigger he wants a dynamic on only which can be manage for all the tabls. Please suggest.
Thanks
Saroj

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

DDL triggers aren't going to stop deleted. DDL (data definition language) is CREATE, ALTER, DROP.

If you want to prevent deletes on 20 tables (as in DELETE FROM ...), you need triggers on each of the 20 tables.

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

saroj9958 Mr or Mrs. 500 Points: 598 More actions · Answer 2

Is there any disadvantage in case I create 20 trigger in a database for 20 table?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Only if you write them badly, but that goes for one trigger or one hundred triggers.

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

saroj9958 Mr or Mrs. 500 Points: 598 More actions · Answer 4

I have this code..will it perform good or need to change anything..Please suggest.

CREATE TRIGGER SLA_delete

ON SLA

FOR UPDATE AS

BEGIN

DECLARE @Count int

SET @Count = @@ROWCOUNT;

IF @Count >= (SELECT rowcnt

FROM sysindexes

WHERE ID = OBJECT_ID('SLA' ))

BEGIN

RAISERROR('Cannot update all rows',16,1)

ROLLBACK TRANSACTION

RETURN;

END

GO

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Why are you using a system view that's been deprecated for ~11 years?

Bear in mind that if someone does update all rows, that really is a rollback of the update after it's happened, with all the locking and log usage implied.

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

saroj9958 Mr or Mrs. 500 Points: 598 More actions · Answer 6

Hi Gail,

Could you please provide me a new code , because that is what I knew, As I am a student so I think I have to learn more.. Please suggest me a new code so that I can use it for me database.

Thanks in Advance,

Saroj