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
February 5, 2010 at 4:09 am

This was removed by the editor as SPAM

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.