August 17, 2008 at 6:22 pm
I have some views that need to be refresh every time any of the tables changes. That work beauryfully using DDL triggers for CREATE_TABLE, ALTER_TABLE and DROP_TABLE So, I call a store procedure that do the job. "refreshViews"
Now, you may think that renaming a column will trigger a ALTER_TABLE event. No. If you use Enterprose Manager and change a column name with "design" tools and save the changes, the system doesn't react.
So, maybe SQL Server is internally using sp_rename. I don't like the idea of adding lines to that store procedure. I will rather detect when the stored procedure have been used and then run my "refreshViews" procedure.
Bottom line question: How can I trigger an event when a column is renamed ?
Thank you all
August 18, 2008 at 12:18 am
Hi,
Actually DDl trigger use a function EVENTDATA. when ever DDL is get fired the script of that altered table(in your case ) is stored in its properties OBJECTTEXT..
Following are the steps you need to follow to do you task.
1.store the OBECTTEXT text into a string variable
2.insert that stored text into a table with insert statement
3.then set a INSERT DML trigger on that table .
4.store that tiggred text into a variable.
EXAMPLE : select @sqlcmd = text from inserted --this is magic table.
5 exec that variable.
EXAMPLE : exec(@sqlcmd)
i am sure it will work..it is simple but tricky......:):)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 18, 2008 at 7:43 pm
Thank you for your kind response.
Actually, I already have an audit table that stores that event object when any DDL is triggered.
The problem is that renaming a column doesn't trigger any DDL action.
Give it a try. It is incredible.
August 18, 2008 at 9:16 pm
holy cow! sp_rename doesn't raise a trigger! you are right, i would have thought it would/should, of course.
for anyone wanting to try it yourself,
I modified the great example found here:
http://www.sqlservercentral.com/Forums/Topic540625-146-1.aspx
to do add triggers for procs/functions/views and table 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
) ON [PRIMARY]
--
GO
CREATE TRIGGER [ReturnProcEventData]
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
SET @eventData = eventdata()
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)')
IF @uname IS NOT NULL
BEGIN
INSERT dbo.DDLEventLog (EventDate, UserName, objectName, CommandText,EventType) VALUES
(@edate,@uname,@oname,@otext,@etype)
END
GO
ENABLE TRIGGER [ReturnProcEventData] ON DATABASE
GO
CREATE TRIGGER [ReturnFuncEventData]
ON DATABASE
FOR
CREATE_FUNCTION, DROP_FUNCTION, ALTER_FUNCTION
AS
DECLARE @eventData XML,
@uname NVARCHAR(50),
@oname NVARCHAR(100),
@otext VARCHAR(MAX),
@etype NVARCHAR(100),
@edate DATETIME
SET @eventData = eventdata()
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)')
IF @uname IS NOT NULL
BEGIN
INSERT dbo.DDLEventLog (EventDate, UserName, objectName, CommandText,EventType) VALUES
(@edate,@uname,@oname,@otext,@etype)
END
GO
ENABLE TRIGGER [ReturnFuncEventData] ON DATABASE
GO
CREATE TRIGGER [ReturnTableEventData]
ON DATABASE
FOR
CREATE_TABLE, DROP_TABLE, ALTER_TABLE
AS
DECLARE @eventData XML,
@uname NVARCHAR(50),
@oname NVARCHAR(100),
@otext VARCHAR(MAX),
@etype NVARCHAR(100),
@edate DATETIME
SET @eventData = eventdata()
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)')
IF @uname IS NOT NULL
BEGIN
INSERT dbo.DDLEventLog (EventDate, UserName, objectName, CommandText,EventType) VALUES
(@edate,@uname,@oname,@otext,@etype)
END
GO
ENABLE TRIGGER [ReturnTableEventData] ON DATABASE
GO
CREATE TRIGGER [ReturnViewEventData]
ON DATABASE
FOR
CREATE_VIEW, DROP_VIEW, ALTER_VIEW
AS
DECLARE @eventData XML,
@uname NVARCHAR(50),
@oname NVARCHAR(100),
@otext VARCHAR(MAX),
@etype NVARCHAR(100),
@edate DATETIME
SET @eventData = eventdata()
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)')
IF @uname IS NOT NULL
BEGIN
INSERT dbo.DDLEventLog (EventDate, UserName, objectName, CommandText,EventType) VALUES
(@edate,@uname,@oname,@otext,@etype)
END
GO
ENABLE TRIGGER [ReturnViewEventData] ON DATABASE
GO
CREATE TABLE tmp(tmpid INT IDENTITY(1,1) NOT NULL PRIMARY KEY,tmptext VARCHAR(30) )
SELECT * FROM [DDLEventLog]
sp_rename 'tmp.tmptext','tmptxt','column'
SELECT * FROM [DDLEventLog][/font]
Lowell
August 18, 2008 at 9:24 pm
found some more info here:
Known issues of DDL Trigger in SQL Server 2005
• Truncate statement is not tracked by any event
• SP_Rename event is not tracked
*For more information, please refer to: *
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124493
For an issue reported when you alter a Replicated Table, Please refer to:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=331367
SQL Server 2008
In SQL Server 2008, there are few more events added like sp_Rename is tracked using RENAME Event.
Lowell
August 20, 2008 at 3:43 am
good information..........thanks a lot......
Mukul
August 21, 2008 at 5:25 am
- another reason why it is not a good idea to apply changes without proper inventory and preparation.
You can off course aways generate and execute the sp_refreshview yourself ...
e.g.
Declare @ColName sysname
Select @ColName = 'colname'
--Select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
Select distinct 'exec sp_refreshview ''[' + C.TABLE_SCHEMA + '].[' + C.TABLE_NAME + ']'' '
from INFORMATION_SCHEMA.COLUMNS C
INNER JOIN INFORMATION_SCHEMA.TABLES T
on T.TABLE_CATALOG = C.TABLE_CATALOG
AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
AND T.TABLE_NAME = C.TABLE_NAME
AND T.TABLE_TYPE = 'VIEW'
Where C.COLUMN_NAME = @ColName
order by 1;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 21, 2008 at 8:22 pm
Thank you for the links and the interesting feedback.
Should we conclude that it is not possible to audit a column renamed in SQL Server 2005 ? Incredible.
Yes, the proc_refreshView can be manually run or be triggered by another event... I just don't want to loop an evaluation against INFORMATION_SCHEMA to acomplish that, it sound terribly inefficient. :unsure:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply