Trigger for truncate
-
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 -
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! -
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
-
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 -
Luzi62,
you mean C2 level auditing or?
Regards,
MShenel -
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:
-
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
-
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.
-
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.
-
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.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply