Is there a DDL trigger for prior state visibility?
There was a question about another question regarding DDL triggers. If I have a DDL trigger like the one below that is designed to warn or roll back the 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 is already referencing the new parameter list, but is there a way to compare it with the parameter list that 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
source to share
I don't think this is possible because the Trigger Fires after the event happened (INSTEAD OF TRIGGER would be desirable, but not available for DDL triggers) and so the change has already been implemented.
Perhaps the information is contained in the EVENTDATA () XML object. Here is a link to the complete schematic link.
source to share