Trigger on COLUMN rename

Question

Trigger on COLUMN rename

byrontylor

Old Hand

Points: 359
More actions
August 17, 2008 at 6:22 pm

#96518

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

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

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

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;-)

byrontylor Old Hand Points: 359 More actions · Answer 2

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.

Lowell SSC Guru Points: 323527 More actions · Answer 3

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

--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 SSC Guru Points: 323527 More actions · Answer 4

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

--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!

mrajgure SSC Journeyman Points: 77 More actions · Answer 5

mrajgure

SSC Journeyman

Points: 77

August 20, 2008 at 3:43 am

#860500

good information..........thanks a lot......

Mukul

Johan Bijnens SSC Guru Points: 135536 More actions · Answer 6

- 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

byrontylor Old Hand Points: 359 More actions · Answer 7

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:

Trigger on COLUMN rename

Cookies on SQLServerCentral