DDL Triggers don't catch calls to sp_rename

Discussion in 'microsoft.public.sqlserver.programming' started by AW, Aug 9, 2007.

  1. AW

    AW Guest

    Hello

    Ich was about to use DDL Triggers to extract all DDL-Level Events to a table
    for later Scripting.
    But when testing I found out that if a developer just right-clicks a Table
    and renames it inside Management Studio, that event doesn't get caught.
    Same when renaming a Column inside a table.
    Reason seems that Management Studio calls sp_rename behind. And that does
    not get caught.
    I can not take the risk that Developers don't use standars SQL like "ALTER
    TABLE DROP/ADD COLUMN"

    Any best Practices already?
    Can't imagine nobody stumbled over this.. but did not find anything on that
    anywhere yet.

    Thanks for reading,

    Andreas
     
    AW, Aug 9, 2007
    #1
    1. Advertisements

  2. AW,

    This is documented in the Book Online, at least the May 2007 version that I
    am currently using. Under the topics "Event Groups For Use With DDL
    Triggers" and "DDL Events for Use with DDL Triggers" it has this note:

    Certain system stored procedures that perform DDL-like operations can also
    fire DDL triggers. Test your DDL triggers to determine their responses to
    system stored procedures that are run. For example, the CREATE TYPE
    statement and sp_addtype stored procedure will both fire a DDL trigger that
    is created on a CREATE_TYPE event. However, the sp_rename stored procedure
    does not fire any DDL triggers.

    Yes, I also consider this a problem that should be fixed, and so do many
    others.
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124493

    RLF
     
    Russell Fields, Aug 10, 2007
    #2
    1. Advertisements

  3. AW

    AW Guest

    Thank you Russel for that response.
    Also for the link.
    As I do not think altering those sp_'s is a good idea I guess I have to wait
    and hope for Katmai. :-/

    Andreas
     
    AW, Aug 11, 2007
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.