DDL Trigger for DROP Objects

  • I need create a DDL Trigger to control DROP objects. I know it's easy. But before the object is dropped, I want to capture the code for object. Ex: When I DROP a Procedure, I want to capture its code and insert this code into audit table.

  • I took one of the examples I'd gotten here and modified it to get teh sp_helptext of the procedure being affected.

    my audit trigger is called AFTER the CREATE/ALTER DROP event...so it works fine for capturing the proc definition for create and alter, but not for DROP.

    the procedure is dropped BEFORE my audit trigger can get the sp_helptext. i get an error saying object

    Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54

    The object 'sp_find' does not exist in database 'GEO55' or is invalid for this operation.

    maybe there is a different audit event, like BEFORE DROP_PROCEDURE, i'm not sure, and it's late

    here's my example code...after putting it in place in a database, create/alter then drop a procedure, then select * from DDLEventLog to see the results

    [font="Courier New"]

    CREATE TABLE [dbo].[DDLEventLog](

            [EventDate] [datetime] NOT NULL,

            [UserName] [sysname] NOT NULL,

            [objectName] [sysname] NOT NULL,

            [CommandText] [varchar](MAX) NOT NULL,

            [EventType] [nvarchar](100) NULL

    )

    --

    GO

    CREATE TRIGGER [ReturnPREventData]

    ON DATABASE

    FOR

    CREATE_PROCEDURE, DROP_PROCEDURE, ALTER_PROCEDURE

    AS

    DECLARE @eventData XML,

            @uname NVARCHAR(50),

            @oname NVARCHAR(100),

            @otext VARCHAR(MAX),

            @etype NVARCHAR(100),

            @edate DATETIME,

            @OriginalProcDef VARCHAR(MAX)

    DECLARE @tmp TABLE (objectDef VARCHAR(MAX) )

    SET @eventData = eventdata()

    SET @OriginalProcDef=''

    SELECT

            @edate=GETDATE(),

            @uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),

            @oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),

            @otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

                    'VARCHAR(MAX)'),

            @etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

    --get the definition of the proc as it stands.

    INSERT INTO @tmp(objectDef)

      EXEC sp_helptext @oname

    SELECT @OriginalProcDef = @OriginalProcDef + ISNULL(objectDef,'')

    FROM @tmp

    IF @OriginalProcDef <> ''

      BEGIN

    INSERT dbo.DDLEventLog (EventDate, UserName, objectName, CommandText,EventType) VALUES

            (@edate,@uname,@oname,@OriginalProcDef,'Save of Original Proc Definition')

      END

    IF @uname IS NOT NULL

    BEGIN

    INSERT dbo.DDLEventLog (EventDate, UserName, objectName, CommandText,EventType) VALUES

            (@edate,@uname,@oname,@otext,@etype)

    END

    GO

    ENABLE TRIGGER [ReturnPREventData] ON DATABASE

    GO

    SELECT * FROM [DDLEventLog][/font]

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • From BOL:

    DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers.

    So I think if you simply do CREATE and ALTER, you will always have a copy of a proc before it gets dropped.

    hope that helps.

    http://msdn.microsoft.com/en-us/library/ms175941.aspx

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • this updated code that i posted previously does not raise any errors. it cannot get teh definition on drop procedure, but it does get creation and changes.

    [font="Courier New"]

    CREATE TABLE [dbo].[DDLEventLog](

            [EventDate] [datetime] NOT NULL,

            [UserName] [sysname] NOT NULL,

            [objectName] [sysname] NOT NULL,

            [CommandText] [varchar](MAX) NOT NULL,

            [EventType] [nvarchar](100) NULL

    )

    --

    GO

    ALTER TRIGGER [ReturnPREventData]

    ON DATABASE

    FOR

    CREATE_PROCEDURE, DROP_PROCEDURE, ALTER_PROCEDURE

    AS

    SET NOCOUNT ON

    DECLARE @eventData XML,

            @uname NVARCHAR(50),

            @oname NVARCHAR(100),

            @otext VARCHAR(MAX),

            @etype NVARCHAR(100),

            @edate DATETIME,

            @OriginalProcDef VARCHAR(MAX)

    DECLARE @tmp TABLE (objectDef VARCHAR(MAX) )

    SET @eventData = eventdata()

    SET @OriginalProcDef=''

    SELECT

            @edate=GETDATE(),

            @uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),

            @oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),

            @otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

                    'VARCHAR(MAX)'),

            @etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

    --get the definition of the proc as it stands.

    IF @etype = 'CREATE_PROCEDURE' OR @etype = 'ALTER_PROCEDURE'

      BEGIN

        INSERT INTO @tmp(objectDef)

          EXEC sp_helptext @oname

      END

    SELECT @OriginalProcDef = @OriginalProcDef + ISNULL(objectDef,'')

    FROM @tmp

        IF @OriginalProcDef <> ''

          BEGIN

            INSERT dbo.DDLEventLog (EventDate, UserName, objectName, CommandText,EventType)

              VALUES         (@edate,@uname,@oname,@OriginalProcDef,'Save of Original Proc Definition')

          END

    --regular audit continues.

    IF @uname IS NOT NULL

    BEGIN

    INSERT dbo.DDLEventLog (EventDate, UserName, objectName, CommandText,EventType) VALUES

            (@edate,@uname,@oname,@otext,@etype)

    END

    GO

    ENABLE TRIGGER [ReturnPREventData] ON DATABASE

    GO

    SELECT * FROM [DDLEventLog][/font]

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Note that even if the table were still around, it would still be extremely difficult to get exactly what the OP is asking for. This is because SQL Server has no built-in way to generate SQL CREATE scripts for its non-script based objects. Yes, SSMS & SMO can do it, but they are outside of SQL Server, and there is no supported way to call them for that purpose. And although most static objects are simple enough that a small sProc could recreate the script from the object attributes, the one enormous exception to that is Tables.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • i've got a stored proc i made that i named sp_getDDL, which trolls thru the table definition and it's columns and builds the script for the table; it's probably worthy of an article. I wrote it for sql2000 way back, and adapted it for 2005 as well.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (2/6/2009)


    i've got a stored proc i made that i named sp_getDDL, which trolls thru the table definition and it's columns and builds the script for the table; it's probably worthy of an article. I wrote it for sql2000 way back, and adapted it for 2005 as well.

    Or at least post it as a script. Writing a fairly complete "CREATE TABLE" proc is a herculean task, so I would love to have a copy.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung (2/6/2009)


    Lowell (2/6/2009)


    i've got a stored proc i made that i named sp_getDDL, which trolls thru the table definition and it's columns and builds the script for the table; it's probably worthy of an article. I wrote it for sql2000 way back, and adapted it for 2005 as well.

    Or at least post it as a script. Writing a fairly complete "CREATE TABLE" proc is a herculean task, so I would love to have a copy.

    Yup, god knows I tried it once.... everytime you think you're done something else pops up... then a new version comes around and you need to start right back over!

  • Ninja's_RGR'us (2/7/2009)


    RBarryYoung (2/6/2009)


    Lowell (2/6/2009)


    i've got a stored proc i made that i named sp_getDDL, which trolls thru the table definition and it's columns and builds the script for the table; it's probably worthy of an article. I wrote it for sql2000 way back, and adapted it for 2005 as well.

    Or at least post it as a script. Writing a fairly complete "CREATE TABLE" proc is a herculean task, so I would love to have a copy.

    Yup, god knows I tried it once.... everytime you think you're done something else pops up... then a new version comes around and you need to start right back over!

    Tell me about it. About 6 times in the past three years I have almost started to write a proc to do it, but then after I made an initial list of everything that I would have to do I would step back and say "No way! That's just crazy!" :w00t:

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • ... and then you probably still missed some :P.

  • Ninja's_RGR'us (2/7/2009)


    ... and then you probably still missed some :P.

    Definitely. Everytime I was at a different customer site and scripted a database, I would see something that I hadn't thought of: index filegroups, identity seed, fill factor, collations, partitions, etc., etc... The list of features that they have crammed into a single object type is incredible and seems endless.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 11 posts - 1 through 11 (of 11 total)

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