April 21, 2009 at 2:11 pm
Let's say you are using some program and you click on a button to do something significant, and the programmer thought that you might be making a mistake when you do that. So he builds in a dialogue box to display in certain situations. For example, the dialogue box might say, "You are about to delete all of those records permanently. Are you sure that you want to do that? Click 'Yes' or 'No'."
I'd like to accomplish something like that with SQL Server columns names and maybe properties too. For example, let's say I have some C# code that I create and it depends on a particular table being named "MY_RECORDS" and a column name being "DESCRIPTION" with VARCHAR(50) data type. Then let's say that I come back to the SQL Server table a few months later and for whatever reason I think that I need to change the column name to "DESCR". Now my code will not work unless I change it and recompile.
So what I would like to do is have something that serves the function of the dialogue box described above. When I try to change the column name either with a script or SSMS, it would stop me and display whatever message I had previously decided to display. For example, "If you change that column name, you will also have to change the code in the 'ReallyIngeniousClass' in program 'MyCoolProgram'."
So I am trying to prevent the altering of table and column names or properties in some situations. What's the best way to accomplish that?
Is this a good way?
CREATE TRIGGER trgNoMonkeying ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
DECLARE @Message VARCHAR(255)
SELECT @message = 'You are forbiddent to alter or delete the '''
+ EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',
'nvarchar(100)') + ''' table'
RAISERROR ( @Message, 16, 1 )
ROLLBACK ;
GO
April 21, 2009 at 2:22 pm
Don't give people alter table permissions?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2009 at 5:44 pm
Yes, setting permissions would work to keep other people from altering. But frankly I'm more worried about myself right now. I sometimes forget stuff. And then when you do something like that it takes a while to figure out what you've done wrong.
Then when I have another programmer involved it gets even more difficult to keep from getting things fouled up.
So I'm just wanting to prevent my own errors, more than anything.
April 22, 2009 at 2:13 am
stankirk (4/21/2009)
Yes, setting permissions would work to keep other people from altering. But frankly I'm more worried about myself right now. I sometimes forget stuff. And then when you do something like that it takes a while to figure out what you've done wrong.
Maybe I'm missing something, but why would you be making ad-hoc random changes? I assume this is not a production system (at least I hope it's not)
You can use the DDL trigger, it will certainly work. I'm still wondering why it's necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2009 at 7:10 am
I agree with Gail, you should not be randomly making changes to your tables. But if you insist on doing that, you could create a view WITH SCHEMABINDING, check the BOL for more details.
April 23, 2009 at 10:31 am
I also agree with Gail. I would look at how you are making changes on a fundamental level. Create yourself a deployment document with steps and check lists. Create a development/test environment to test changes that you script. This will help you step back and evaluate all the changes and impacts when you go through your check list.
That being said I would look at DDL triggers for preventing changes like when related to an Admin. This will force you to disable the DDL trigger, which in turn should make you think about what you are doing in more detail.
April 24, 2009 at 7:57 am
Thanks everyone for responding.
I agree that I need to be careful in making the changes. But then I don't really know what I'm doing, do I? That's why I'm posting in the "Newbie" forum. :hehe:
Nevertheless, a lot of other people must have had a similar problem/request, since MS went to the trouble to build in the capability of DDL triggers for ALTER TABLE.
One more question. With DDL triggers, is it possible to just have it protecting (1) a particular table, (2) column, or even (3) property, rather than a whole database? If so, can you show me a script or provide a link.
April 24, 2009 at 9:02 am
You can limit it, but you'd need to shred the EVENTDATA XML to figure out which table it was and then limit things.
You might be better off just logging changes and then dealing with them offline.
The functionality is there for design and DBAs, not normal use, and not daily use. Any changes to tables/columns should be tested somewhere else. Once it's tested, you SCRIPT the changes and apply them to your system. You don't go clicking in SSMS. That's a newbie mistake, and one that comes back to bite you at times.
April 24, 2009 at 9:39 am
Steve Jones - Editor (4/24/2009)
The functionality is there for design and DBAs, not normal use, and not daily use.
And auditing. In fact, I'd almost go as far as saying especially auditing.
Steve's right. With the exception of a local test database that no one cares about at all, no changes should be done to a development or production server by clicking in SSMS. Write the changes as T-SQL scripts. Firstly it ensures that you can check before making the change, second it allows the use of source-control and controlled, scripted deployments through the environments.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2009 at 3:58 pm
OK. Well, that helps me to better understand that I don't know what I'm doing, at least.
Gail, you have a big picture in mind when you say, "Write the changes as T-SQL scripts. Firstly it ensures that you can check before making the change, second it allows the use of source-control and controlled, scripted deployments through the environments."
Can you help me understand that? I really don't know what you are talking about. Maybe some links to start me in the right direction, please?
April 25, 2009 at 3:53 am
stankirk (4/24/2009)
OK. Well, that helps me to better understand that I don't know what I'm doing, at least.
🙂 Not intentional.
Gail, you have a big picture in mind when you say, "Write the changes as T-SQL scripts. Firstly it ensures that you can check before making the change, second it allows the use of source-control and controlled, scripted deployments through the environments."
Can you help me understand that? I really don't know what you are talking about. Maybe some links to start me in the right direction, please?
Say you've been doing development for an in-house application. The development's done and you now need to get it to the production server. If it's a new database, that's easy, backup the db and restore on the new server.
But what if it's not a new database (which, for in house development is probably the norm), you can't just backup and restore, it will overwrite user data. Hence you need some other way to get changes from the development environment (which is where all the development should be done) to the testing environment (for user acceptance testing) and through to production
You could make the changes through the GUI, keeping track of the changes that you've make, but that's asking for trouble. Just one thing left out could result in the application breaking and lots of users yelling (not fun)
Better option is to require that all changes make in dev are made in script (and it's a discipline, nothing more). That way the scripts can be checked over before they're run, they can be saved in your source control system same as application code is and, when moving to test or production there's no risk of missing something as the entire stack of scripts can simply be run one by one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply