0

I'm trying to create a trigger on my SQL Server Database that will change the username in a table if the username changes in the User Table. I know that this is normally done by creating a relationship between the two tables to cascade the changes, but I seem to be running into the same issue as the person in this question: (Foreign key constraint may cause cycles or multiple cascade paths?). So I need to create a trigger that will do that for me, but what is confusing me is how do I get the old username value so I know what record to update in the WHERE section of the trigger?

Hopefully this wasn't to vague. Thank you.

Community
  • 1
  • 1
Vandel212
  • 1,074
  • 1
  • 13
  • 28
  • [Using the inserted and deleted tables](http://msdn.microsoft.com/en-us/library/ms191300.aspx) and pay special heed to the fact that these tables can contain multiple rows. So now, you have an issue, if `username` is in fact the primary key of your users table. And one of the reasons why you'd normally want something unchangable as your primary key - so that you can relate `inserted` and `deleted`. If `Username` isn't the PK, then the normal thing to do would be to just not have it duplicated in other tables. – Damien_The_Unbeliever Jan 28 '14 at 07:17
  • This concept in relational databases confuses me. What is the point of contraints, primary keys, foreign keys, etc...? Do you really need the relationships if the database is normalized? – Vandel212 Jan 28 '14 at 08:01
  • 1
    Become acquainted with those constraints et al, it's not nonsense. If properly implemented it guards your database against invalid data coming from the application / the import file etc. E.g. on the hypothetical "Month" column I can create constraint to only allow values between 1 and 12. Database's job is to not accept any other data from any source. You may have validations in your app's GUI but you may not have in batch import process. This is where it benefits. There are also performance benefits involved (e.g. query WHERE month=13 will not scan rows at all). – Endrju Jan 28 '14 at 10:30
  • Thank you for the explanation. That makes sense. I suppose I should keep the relationship between the two tables. – Vandel212 Jan 28 '14 at 19:46

0 Answers0