Maintaining rows in a SQL Server junction table when rows in other table are inserted and updated
Can you show sample coding to create a trigger or stored procedure that maintains rows a SQL Server junction table when changes are made to the Authors and BookTitles tables such as inserting and updating rows in those tables?
We have the following tables:
Authors: ID NAME ZIP AND SOME MORE COLUMNS BookTitles: ID TITLE ISBN AND SOME MORE COLUMNS This is the table we will use as our junction table: AuthorTitles: ID AUTHOR_ID BOOK_TITLE_ID
We would like to do this in a trigger instead of doing the coding in our VB.Net form.
All help will be appreciated.
The above table structures were simplified to show what we are trying to do.
We are implementing a junction table for teachers and programs.
Here is a photo of the actual system:
One Solution collect form web for “Maintaining rows in a SQL Server junction table when rows in other table are inserted and updated”
Unless you have Foreign Key constraints that require at least one Book per Author and/or vice-versa, then the only cases that you should need special handling are for the Deletes to BookTitles or Authors. They can be done like this:
CREATE PROC BookTitle_Delete(@Book_ID As INT) As -- First remove any children in the junction table DELETE FROM AuthorTitles WHERE BOOK_TITLE_ID = @Book_ID -- Now, remove the parent record on BookTitles DELETE FROM BookTitles WHERE ID = @Book_ID go
In general, you want to resist the temptation to do Table Maintenance and other things like this in Triggers. As triggers are invisible, add additional overhead, can cause maintenance problems for the DBA’s, and can lead to many subtle transactional/locking complexities and performance issues. Triggers should be reserved for simple things that really should be hidden from the client application (like auditing) and that cannot be practically implemented in some other way. This is not one of those cases.
If you really want an “invisible” way of doing this then just implement a Cascading Foreign-Key. I do not recommend this either, but it is still preferable to a trigger.