Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
M4tRiX83
Starting Member
4 Posts |
 Posted - 2011-06-23 : 16:57:21
|
| Hi,I need an advice to manage this situation:I've a table with some officers data. An officer can be sub-officer of another officer and in my table there are the follow columns:ID (uniqueidentifier)NameSurname...etc...and there is a column called "mainOfficerId" that rapresent the main officier of a sub-officier and reference his id.My problem is to control, when I'm deleting an officer if he have some sub-officiers and if so, he can't be delete.There is a method in Sql Server to manage this situation or I need to manage this with programming (vb.net)?Thank you for the helpFabio |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-23 : 18:11:40
|
| You could set up a trigger for delete that would check if there are sub-officers, if there are any, roll-back the transaction. There is documentation and some examples here: http://msdn.microsoft.com/en-us/library/ms189799.aspxIf you run into difficulties, post the table DDL and some sample data, and I am sure people on this forum will be able to help you with useful suggestions. Brett's blog has info on how to post the DDL: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
 |
|
|
M4tRiX83
Starting Member
4 Posts |
Posted - 2011-06-26 : 08:58:13
|
| I preferred solve the problem by vb coding.Thank's a lot for the explanation. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-26 : 09:15:46
|
| In most cases, enforcing data integrity rules from client code (such as VB/C# code) is not a good idea. The reason for this is that every client - even those that have not yet been written - will need to be aware of and program to take into account the data integrity constraint. In many cases the "client" may not even be a C# or VB program - it may be someone trying to insert/delete data into/from the tables using the SQL Server Management Studio.Enforcing this type of data integrity rules is where SQL Server and databases excel at. |
|
|
|
jyoti walia
Starting Member
2 Posts |
Posted - 2011-07-30 : 08:53:00
|
 |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-07-30 : 11:18:14
|
| Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you? Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL. >> I have a table with some officers data. An officer can be sub-officer of another officer and in my table there are the follow columns <<The vague narrative you did post is awful. The table has no name. The use of a UNIQUEIDENTIFIER is completely non-relational. You have no idea what ISO-11179 or basic data modeling would require. It further describes an adjacency list model of an organizational hierarchy; the worst way to model this. Google “Nested Sets model” and use it.You are still writing VB or some other procedural language, but you are doing it in SQL. This is not the place to get an education; you need. You need to change your mindset when you are in SQL. NEVER, NEVER, NEVER do data integrity in the front end. It is a time bomb that will explode when anyone touches the database. SQL will enforce integrity, one way, one place, one time. That is why we have databases in the first place. --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
|
|
Reference two columns in same table