TRUNCATE

  • agrawal.prakriti

    Mr or Mrs. 500

    Points: 572

    Comments posted to this topic are about the item TRUNCATE

  • ziangij

    SSCertifiable

    Points: 7262

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

  • SQLRNNR

    SSC Guru

    Points: 281252

    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

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

    Best Regards,

    Chris Bรผttner

  • This was removed by the editor as SPAM

  • Henry_Lee

    SSCrazy

    Points: 2714

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

  • Dugi

    SSCoach

    Points: 17998

    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

    yikes - I knew the answer and selected the wrong button -need more coffee ๐Ÿ˜€

  • Dugi

    SSCoach

    Points: 17998

    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

    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: 2578

    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

    SSCrazy

    Points: 2984

    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

    "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

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

  • vk-kirov

    SSCertifiable

    Points: 7686

    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.

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

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