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