Write triggers not to truncate table

Question

Write triggers not to truncate table

Max-255026

Valued Member

Points: 53
More actions
August 1, 2006 at 2:12 pm

#113914

Hi Folks:
I need some guru experties.
I want to write a trigger not to truncate table.
Does anyone knows how to do that?
Thanks,

Max

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

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

A trigger will not truncate a table unless it is explicity coded to do so. A trigger is basically a glorified stored procedure. If you want help writing a trigger, you will need to detail exactly what you want it to do and what you want to trigger it to fire. Can you explain a little more on what you are trying to accomplish?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Max-255026 Valued Member Points: 53 More actions · Answer 2

I have a table with millions of records without any foreign key.

I want to prevent an accidental truncate on this table.

I was hoping there is a way I can write a trigger that if someone run a truncate statement on that table,

it will not allow the table to be truncate.

I don't think there is a way to write a trigger for truncate after searching online for awhile.

I thought someone out there might have a solution for it.

Thank you,

Max

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Ah, thanks for the clarification. I don't believe that you can do what you are after with a trigger. Use permissions to handle this. If you do not grant a user permissions at the table level, they can't truncate the table. Instead, create strored procedures for data access and grant the users execute permissions to the stored proceudures.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Max-255026 Valued Member Points: 53 More actions · Answer 4

My concern was, that I, (the administrator) do not accidentally truncate the table.

I almost did that, thinking I was on my test database, but I caught myself on time.

Im thinking that I need to create an index view on that table so to prevent truncation.

But I was hoping for a better solution.

Thank you for responding.

Max

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 5

Create a shadow table with the PK value, and create a Foreign Key on that table to the PK, now add a trigger that Inserts new PKs in the shadow table. TRUNCATE TABLE will now error due to the existance of the FK.

Not pretty, but it works if you are that worried about your sysadmins.

Andy

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

And now would be a good time to learn about backup and restore... Cause no matter what precautions you take, you're gonna screw up one day. And you must be prepared to repair the error(s).

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 7

Basically if you are the administrator you can do what you want (this why the administrator id for )

Otherwise Andy's solution is a valid on. If you have a forign key defined on a table you cannot truncate it.

Or you have to create for yourself an another user ID (not sysadim, not dbo) you will work with so you wont be able to do such kind of errors

Bye
Gabor