TRUNCATE

  • Comments posted to this topic are about the item TRUNCATE

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

  • 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

  • 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

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

  • 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]

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

  • 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]

  • 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.

  • 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

  • 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.

  • "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!

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

  • 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 40 total)

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