Trigger for truncate

Question

Trigger for truncate

shen-dest

SSCrazy

Points: 2498
More actions
April 21, 2010 at 7:18 am

#218144

Hi,
Can we simulate a truncate trigger? what i need is from which host, who,when truncated a table even if he/she is a DBA.
Regards,
MShenel

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

well, you can't get TRUNCATE to activate a trigger on a table;from BOL:

TRUNCATE TABLE cannot activate a trigger because the operation does not log ... the db_owner and db_ddladmin fixed database roles, and are not transferable. ...
msdn.microsoft.com/en-us/library/ms177570.aspx - Cached - Similar

one way to prevent the TRUNCATE command is to simply create a view WITH SCHEMABINDING of the table to protect; simple and easy to create.

other than that, you can create a DML trace to track who did it, but not prevent it; there's no scope for server or database triggers to intterupt a truncate command that i could find.

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!

Luzi62 Ten Centuries Points: 1218 More actions · Answer 2

Hi,

if you only want to log the truncate and not preventing from doing so then take a closer look at the auditing options in SQl Server 2008 🙂

shen-dest SSCrazy Points: 2498 More actions · Answer 3

within application truncating a table is not possible. but somehow tables are being truncated.

before truncating i want to check if tablename exists in truncate_table.if not table name and user info must be entered to truncate_table so to be able to truncate.

Regards,
MShenel

shen-dest SSCrazy Points: 2498 More actions · Answer 4

Luzi62,

you mean C2 level auditing or?

Regards,
MShenel

Luzi62 Ten Centuries Points: 1218 More actions · Answer 5

shen-dest (4/21/2010)
Luzi62,
you mean C2 level auditing or?

No, there is a much easier way in SQL Server 2008.

Take a look at this:

http://msdn.microsoft.com/en-us/library/dd392015.aspx

jholladay 10886 SSC Enthusiast Points: 106 More actions · Answer 6

I'm looking for a structural solution to prohibit truncation. I just tried this method of preventing TRUNCATE TABLE and it did not appear to work (SQL Server 2005). Am I missing something?

------------------------------------------------------------------------------------

PRINT 'Creating Table'

CREATE TABLE TestTruncate(I INT IDENTITY(1,1), x CHAR(1), PRIMARY KEY(I));

GO

PRINT 'Creating View'

GO

CREATE VIEW vw_TestTruncate WITH SCHEMABINDING AS (SELECT i,x FROM dbo.TestTruncate);

GO

PRINT 'Inserting'

INSERT INTO TestTruncate(x)

SELECT 'x'

GO

PRINT 'Truncating'

TRUNCATE TABLE TestTruncate

PRINT 'Done'

GO

------------------------------------------------------------------------------------

Creating Table

Creating View

Inserting

(1 row(s) affected)

Truncating

Done

jholladay 10886 SSC Enthusiast Points: 106 More actions · Answer 7

The presence of a dependent foreign key will prevent truncation of the table. I think I'll use that to my advantage.

-----------------------------------------------------------------------------

PRINT 'Creating Tables'

CREATE TABLE TestTruncate(I INT IDENTITY(1,1), x CHAR(1), PRIMARY KEY(I));

CREATE TABLE TestTruncateProtect (

x INT,

i AS CONVERT(INT,NULL) PERSISTED,

CHECK(i IS NOT NULL),

UNIQUE (i),

FOREIGN KEY (i) REFERENCES TestTruncate(I)

)

GO

PRINT 'Inserting'

INSERT INTO TestTruncate(x)

SELECT 'x'

GO

PRINT 'Truncating'

TRUNCATE TABLE TestTruncate

PRINT 'Done'

GO

-----------------------------------------------------------------------------

Creating Tables

Inserting

(1 row(s) affected)

Truncating

Msg 4712, Level 16, State 1, Line 2

Cannot truncate table 'TestTruncate' because it is being referenced by a FOREIGN KEY constraint.

Golfer22 SSCrazy Points: 2256 More actions · Answer 8

One thing to remember is that truncating a table is not a DML operation (like DELETE). Truncating is a DDL operation. So transaction logging doesn't occur when a TRUNCATE is performed. Certain security privileges and roles can be configured to disallow users from having the ability to truncate. But it looks like you have figured out a way to do what you want.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Golfer22 (5/26/2013)
One thing to remember is that truncating a table is not a DML operation (like DELETE). Truncating is a DDL operation. So transaction logging doesn't occur when a TRUNCATE is performed. Certain security privileges and roles can be configured to disallow users from having the ability to truncate. But it looks like you have figured out a way to do what you want.

The TRUNCATE is logged, but it is the deallocation of the pages holding the data in the table.