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:

  • I need to merge data from 2 columns to another column. And then merge all rows of that added column into one cell in another table
  • SQL: How to make table name in stored procedure dynamic
  • Table column split to two columns in sql?
  • How to check that given time slot is already exists in SQL Server 2012
  • Very slow insert process using Linq to Sql
  • Delete all stored procedures in a specific SQL Server schema
  • 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:

  • MS SQL creating many-to-many relation with a junction table
  • How can I create foreign keys for junction table
  • 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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.