DDL Trigger question

• C.K.Shaiju

  SSCarpal Tunnel

  Points: 4228

  More actions

  November 25, 2008 at 12:44 am

  #103631

  Hi,

  I'd like to create a DDL Trigger after CREATE_PROCEDURE and

  ALTER_PROCEDURE.

  Qn.1) It should print different message for CREATE and ALTER.

  How do I do that? Can I give EventType in IF condition? If possible, how?

  Or is there any other way to do this?

  Qn.2) How can I get the object name which has been created or altered?

  Please Help

  Thanks in Advance

  Shaiju C.K.

  _____________________________________________
  One ounce of practice is more important than tonnes of dreams

• Girish Bhat

  Ten Centuries

  Points: 1323

  More actions

  November 25, 2008 at 12:58 am

  #902955

  When you say "print", where do you want to print? I believe you mean you want to record the DDL commands into some kind of an audit table, right?

  Check out this article. It will give you a good understanding of how to create DDL Triggers:

  http://www.sqlservercentral.com/articles/SQL+Server+2005+-+Security/2927/[/url]

  Thanks,

  Girish

• C.K.Shaiju

  SSCarpal Tunnel

  Points: 4228

  More actions

  November 25, 2008 at 2:27 am

  #902975

  Thanks Girish,

  I meant print a message or show an error to the creator of that object. Anyway, You directed me to the exact link. This is what I want. I can use EVENTDATA() function which mentioned in that link.

  Thanks a lot :),

  Shaiju C.K.

  _____________________________________________
  One ounce of practice is more important than tonnes of dreams