By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,767 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,767 IT Pros & Developers. It's quick & easy.

When to use trigger, constraint and when one cant

BeemerBiker
P: 87
I do not want a value changed in a table unless something else is done first. I can easily prevent this in my GUI with a few lines of C# code. However, would it be possible to set a trigger to prevent it from occuring when someone was using a DB tool or studio and not my program?

For example, I have a field "EmployeeStatus" that is not a key, index, foreign key, etc. If the value of the field is "Active" then that employee will show up in most queries. If "Retired" for example, then generally not much shows up.

When changing from active to retired I need to ask who the new supervisor is so I can update the staff members records and have their supervisor key field point to the new supervisor. This is easy to do.

I inadvertantly changed one member to Retired while using VS2008 server explorer. This was easily corrected, but not until I realized that all of his staff members were missing from my organization treeview. I am totally new to ms sql and have some experience in postgresql. I was wondering how this problem could be handled using a constraint or trigger.

thanks for looking
Apr 11 '09 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
For example, I have a field "EmployeeStatus" that is not a key, index, foreign key, etc. If the value of the field is "Active" then that employee will show up in most queries. If "Retired" for example, then generally not much shows up.
I don't know if you can't stop the record from showing up in a query, specially if it's executed in a Management Studio. If you want to stop the row from showing up, try creating a function or a view and let everyone access the view or function, not the table.

When changing from active to retired I need to ask who the new supervisor is so I can update the staff members records and have their supervisor key field point to the new supervisor. This is easy to do.
If the update is done on the table directly, you won't be able to update the supervisor field automatically. You can, however, check if the supervisor is still active and reject the update if the supervisor is already retired. You can do that via trigger.

I didn't get the last part of your question.


--- CK
Apr 11 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.