TRUNCATE

Question

Post reply

TRUNCATE

agrawal.prakriti

Mr or Mrs. 500

Points: 572
More actions
February 4, 2010 at 10:03 pm

#220268

Comments posted to this topic are about the item TRUNCATE

Viewing 15 posts - 1 through 15 (of 40 total)

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

ziangij SSCrazy Eights Points: 8066 More actions · Answer 1

ziangij

SSCrazy Eights

Points: 8066

February 4, 2010 at 10:41 pm

#1114187

this was a very easy question...:-D

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 3

I wonder why there is no DDL trigger available to capture this of event...

Best Regards,

Chris Büttner

Henry_Lee SSCrazy Points: 2714 More actions · Answer 4

Hmmm...I would have thought TRUNCATE could raise a DDL trigger.

Dugi SSCoach Points: 17998 More actions · Answer 5

eeeeeeeeeeeeeeeeeeee typing wrong answer this happened that I misunderstand in opposite way :w00t::w00t::w00t:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

BarbW SSCarpal Tunnel Points: 4759 More actions · Answer 6

yikes - I knew the answer and selected the wrong button -need more coffee

Dugi SSCoach Points: 17998 More actions · Answer 7

barb.wendling (2/5/2010)
yikes - I knew the answer and selected the wrong button -need more coffee

aaaaaaaaaaaaaaaaaah we are in same team buddy, same happened to me too!!! - :w00t::hehe::w00t::hehe::w00t::hehe::cool:;-):-P:-P:-D:-)

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

SanjayAttray SSChampion Points: 13157 More actions · Answer 8

Back to basics. Most of the time I select wrong answer for basic and simple question. Triggers are for INSERT, UPDATE, or DELETE only.

SQL DBA.

Lonely Rogue SSCrazy Points: 2579 More actions · Answer 9

Then, is there any way to fire the Trigger upon this Truncate action, as well?

In ' Thoughts ',

[font="Comic Sans MS"]Lonely Vampire[/font]

--In 'thoughts'...
Lonely Rogue

Fal Hall of Fame Points: 3143 More actions · Answer 10

Triggers are for INSERT, UPDATE, or DELETE only.

That is true, but not (I think) the purpose of today's QotD.

DELETE operates on the individual rows of a table, whereas TRUNCATE operates on the data pages. As each is logged differently there is no information about individual rows for the trigger to reference upon a TRUNCATE statement.

Clive Chinery SSCrazy Points: 2563 More actions · Answer 11

"TRUE or FASLE" - good question but in need of a spell check!

A simple experiment with "fasle" shows that the spell check ignores words in capitals!

jshailendra SSCrazy Points: 2260 More actions · Answer 12

jshailendra

SSCrazy

Points: 2260

February 8, 2010 at 11:00 pm

#1115932

hey Clive...good catch..:blink::cool:

vk-kirov SSCertifiable Points: 7686 More actions · Answer 13

SanjayAttray (2/6/2010)
Triggers are for INSERT, UPDATE, or DELETE only.

Correction:

DML triggers are for INSERT, UPDATE, or DELETE only.

There are also DDL triggers for a wide variety of DDL events. I answered wrong because I thought that TRUNCATE could generate a DDL event

Actually TRUNCATE looks like a DDL statement, because it requests a schema modification lock:

CREATE TABLE test (a INT)

BEGIN TRANSACTION

TRUNCATE TABLE test

SELECT *

FROM sys.dm_tran_locks

WHERE request_session_id = @@SPID

AND resource_type = 'OBJECT'

AND resource_associated_entity_id = OBJECT_ID('test')

ROLLBACK

DROP TABLE test

In the example above, we will see request_mode = Sch-M (schema modification). This is specific to DDL events.

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 14

Christian Buettner-167247 (2/5/2010)
I wonder why there is no DDL trigger available to capture this of event...

Because it is a DML event, the structure of the table is not changing, just the data in it? Sound right experts?

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems

TRUNCATE

Cookies on SQLServerCentral