views:

34

answers:

1

A question came up in relation to another question regarding DDL triggers. If I have a DDL trigger such as the one below that is meant to warn or rollback a change if the parameter list changes, is there a way to get the previous state of (for example) the parameter list? As you can see, the trigger already references the new parameter list, but is there a way for it to compare to the parameter list as it existed before the ALTER statement?

ALTER TRIGGER DDL_PROC
ON DATABASE
FOR ALTER_PROCEDURE
AS
DECLARE @data XML, @ObjectName sysname, @ParamCount int
SET @data = EVENTDATA()
SET @ObjectName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')

PRINT 'You have changed procedure: ' + @ObjectName
SELECT @ParamCount = COUNT(*) FROM sys.parameters
WHERE object_id = OBJECT_ID(@ObjectName)
RAISERROR('This procedure now has %i parameter(s)',0,0,@ParamCount)
GO
+1  A: 

I do not believe that this is possible because the Trigger Fires after the Event has occured (An INSTEAD OF TRIGGER would be desireable but is not available for DDL Triggers) and so the change has already been implemented.

Perhaps the information is contained within the EVENTDATA() XML object. Here is a link to the full schema reference.

http://schemas.microsoft.com/sqlserver/2006/11/eventdata/

John Sansom
The schema definition is helpful, but it doesn't look like any previous state information is there. A DML trigger also takes place after the event (update) has occurred, but there is visibility to the previous state with the "deleted" structure - there's obviously nothing like that, but I'm thinking that there has got to be *some* way to reconstruct at least some elements of the previous state...
Mike DeFehr
Yes precisely becuase the change has already occured by this point you cannot lookup the original definition statement from say the INFORMATION_SCHEMA.ROUTINES view.
John Sansom