1

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:

Daniel Widdis
  • 8,424
  • 13
  • 41
  • 63
Emad-ud-deen
  • 4,734
  • 21
  • 87
  • 152
  • You want to use a trigger not a stored procedure. A stored procedure has to be called a trigger on update fires automatically. – Grixxly Jul 14 '12 at 14:11
  • 2
    What would change in `AuthorTitles` when a row in either of the other 2 tables is updated? – ypercubeᵀᴹ Jul 14 '12 at 14:15
  • We may have a situation where more than 1 author will work on the same book and also 1 author may have multiple books published as well. So if we add a new author we want the junction table to reflect that as well as the same if we insert another book that author worked on. – Emad-ud-deen Jul 14 '12 at 14:18
  • 2
    No, you want to do it in a stored procedure. You have stored procedures that implement the interface to the table operations (Insert, Delete, Update). Doing table maintenance in a Trigger is a bad idea. If you really want to do it "invisibly" then just add a cascading Foreign Key, that's what they are for. – RBarryYoung Jul 14 '12 at 14:19
  • 1
    As far as I can tell, you only need to do this for your Deletes to Authors or Titles. Everything else can be done piecemeal. – RBarryYoung Jul 14 '12 at 14:21
  • If you add a new author how do you know automatically what books they have worked on? – Martin Smith Jul 14 '12 at 14:22
  • Martin, we though the junction table would link the other tables together so we could get that information in queries. Can you show us the best way to do this and which approach (trigger, stored procesure, etc) is best? We also plan to delete as well. – Emad-ud-deen Jul 14 '12 at 14:26
  • 1
    I just don't understand your requirement TBH. What does "if we add a new author we want the junction table to reflect that" mean? I don't see how anything can happen automatically in that regard. – Martin Smith Jul 14 '12 at 14:29
  • 1
    @Emad-ud-deen - Your design is valid enough but consider this: if below set of statements arrive at your server, how are you going to know at your server *which* books to attach to *which* authors? `INSERT author1; INSERT author2; INSERT book1`. This is something that's only known to the user adding information and can not be done automatically. What you **can** do is add appropriate procedures to call from your client where this distinction is clear. – Lieven Keersmaekers Jul 14 '12 at 14:30
  • I have not done any triggers yet but I think maybe that is what will handle the automatic side of things. We would like the junction table to have the rows inserted for a particular author and book title when it is also inserted into the author table. The book information will come from the VB.Net form and will be passed as a parameter. – Emad-ud-deen Jul 14 '12 at 14:32
  • Lieven, we plan to pass parameters from within the VB.Net form out the the stored procedure or triggers since that information will be known at that time. – Emad-ud-deen Jul 14 '12 at 14:37
  • @Emad-ud-deen - Triggers are not a solution for this. Either the application inserts the required rows in all three tables or you compose several stored procedures to handle the inserts/deletes. You might have stored procedures like `Author_Insert, Author_Delete, Book_Insert, Book_Delete, AuthorBook_Insert`. – Lieven Keersmaekers Jul 14 '12 at 14:42
  • @RBarry a trigger in this case is not a bad idea. He wants it handled automatically and invisibly. That would be considered a good side-effect and calls for a trigger. Using a stored procedure that may or may not be called is asking for issues. Oh and normally I am completely against triggers... – Grixxly Jul 14 '12 at 14:42
  • @TonyHarmon - All that triggers could do is handle automatic deletes but new authors or books can't be handled with a trigger.' – Lieven Keersmaekers Jul 14 '12 at 14:43
  • @Tony Harmon: No, please see my answer where I address this. Triggers in SQL are not a preferred solution method, they should only be used when there is nothing better and there are at least two things better than triggers in this case. – RBarryYoung Jul 14 '12 at 14:45
  • @Lieven I don't like triggers, at all, but they can handle more then deletes. They handle on insert on update on delete instead of... they are dangerously powerful. That's why, in most cases they shouldn't be used. Last resort mentality if you will. But in this case, especially since this looks like homework and not a production environment; they fit the bill. But reasonable people can agree to disagree :) – Grixxly Jul 14 '12 at 15:01
  • Lieven, Not homework. I just simplified the table structures so you can understand what we are trying to do because we are doing a system for the Islamic Knowledge Academy of Lawrence, MA and the actual tables involved will be for Teachers and Programs so the junction table will handle all the teachers that may teach more than 1 program and each program can be taught by more than 1 teacher. – Emad-ud-deen Jul 14 '12 at 15:40
  • @TonyHarmon - That was not what I was trying to say. You can't create a trigger that knows what author to link with what books and you certainly don't *need* one for a new author or a new book. – Lieven Keersmaekers Jul 14 '12 at 16:00
  • @Emad-ud-deen - You probably meant to adress Tony instead of me. I never mentioned homework. – Lieven Keersmaekers Jul 14 '12 at 16:01
  • ooops. Sorry. Anyway, I placed a link to the actual system we are doing in the posting at the top of this page. :-) – Emad-ud-deen Jul 14 '12 at 16:02

1 Answers1

2

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.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • Thanks for the coding. Would a trigger be needed for inserts and updates to the Authors and BookTitles tables? – Emad-ud-deen Jul 14 '12 at 14:34
  • 1
    No, Triggers are not needed at all for any of this. – RBarryYoung Jul 14 '12 at 14:40
  • So it's best to handle this from inside the VB.Net form? – Emad-ud-deen Jul 14 '12 at 14:40
  • To handle what? Its not clear what your concern is. Maybe give a small example? – RBarryYoung Jul 14 '12 at 14:42
  • RBarryYoung, To handle maintenance of the junction table. – Emad-ud-deen Jul 14 '12 at 14:43
  • Thanks everyone for all the help and ideas. From what I have read we will handle the maintenance of the junction table from inside the VB.Net form. – Emad-ud-deen Jul 14 '12 at 14:50
  • ??? I have the same confusions as Martin Smith does in the Question-comments above. How do you know what to do with the Junction table if you only have an Author to add or a Title to add? It's not clear what data is available to your client code when doing the Inserts. Please supply an example or a more explicit description of the client code/state in your Question. Then we can address that. – RBarryYoung Jul 14 '12 at 14:52