automatically update extended properties

[AccessSQLUser]

Is there a way to automatically add or update the extended properties of an object (specifically stored procedures and functions) when the object is created or modified?
What I would like to do is update my extended property "LastModifiedBy" to be the user who last modified the object and to increase my extended property "VersionNum" by 1.
Could this be done?

 

[AccessSQLUser]

any ideas?

 

[Cagliostro]

Maybe triggers will help. John Fill

ivfmd@mail.md

 

[AccessSQLUser]

But can a trigger be created for a stored procedure?

 

[tlbroadbent]

You could put the trigger on the sysobjects table. When it has an insert or update action, the trigger could run a stored procedure that executes sp_addextendedproperty or sp_updateextendedproperty. You could limit which objects are acted on by checking type and/or other attributes in the sysobjects table.

NOTE: This solution may not work if Microsoft changes the format of sysobjects. All documentation warns against accessing system tables directly. Terry

http://members.home.net/tlbroadbent/index.htm