Unable to understand Trigger statement

Question

Hi all,

can anybody explain me the below code for trigger?Please note that the above code was written by one of my class mate and I cannot understand anything in it.

I am not able to understand it.

Also if there is any other way to accomplish the same task then please let me know.

CREATE trigger [dbo].[trg_InsertInBookIssuedDetails]
on [dbo].[BOOKISSUEDDETAILS]
for insert
as 
begin

  begin tran

  update nur 
  set nur.NumberOfBooksIssued = nur.NumberOfBooksIssued+1 
  FROM NEWMEMBER nur
  INNER JOIN INSERTED i
  ON i.IssuedTo = nur.MEMBERName

  if exists(
    select 1 
    from LIBRARYBOOKDETAILS lbd
    INNER JOIN INSERTED i
    ON i.BookID = lbd.BookID
    WHERE lbd.Inventory<=1
  )
  begin
    raiserror('Books UnAvailable Or Cannot Issue More Than 3 Books',16,-1)
    rollback tran   
  end

  else
  begin
    update lbd 
    set lbd.Inventory = lbd.Inventory - 1 
    FROM LIBRARYBOOKDETAILS lbd
    INNER JOIN INSERTED i
    ON lbd.BookID= i.BookID

    commit tran
  end

end

Please help me out in understanding the above trigger statement.

Thanks in advance!

Answer 1

It is a trigger activated when someone inserts a row into the BookIssuedDetails table. It doesn't mention 'for each row' or anything similar, so I think it is executed once, after the insert has occurred for a number of rows (probably including just 1 row. It is interesting to discuss what happens for INSERT INTO BookIssuedDetails SELECT * FROM Somewhere WHERE 1 = 0 because no rows are inserted for/by the statement.

It updates the NewMember table to record the books issued; the 'INSERTED' keyword is an alias approximately equivalent to 'the record(s) inserted into the BookIssuedDetails table'.

It also does some checking on LibraryBookDetails, generating a misleading error message (traditional in IT) - it does not check for the number of books issued to this library member. If the LibraryBookDetails indicate that there is a book available, it updates the table to reduce the number of the particular book(s) available.

All educated guess-work.

Answer 2

Here is a quick breakdown

In your database there exists a table called [BOOKISSUEDDETAILS] owned by [dbo]

This table has a trigger on it called [trg_InsertInBookIssuedDetails] owned by [dbo]. This trigger fires whenever a row is inserted into the table.

It start a transaction (simply put, a Transaction is the ability to execute multiple rows as a unit - all or none)

When a row is inserted it means that a books is issued, so this trigger updates a row in the NEWMEMBER tables.

If the entry for this book does not exist in the LIBRARYBOOKDETAILS, then the transaction is rolled back meaning the NEWMEMBER table remains unchanged.

If the book exists, then it decrements the inventory count by 1 in the LIBRARY BOOK DETAILS

It looks like you have only pasted part of the code. There should be a END and a COMMIT statement at this opint.

Having said all that, I must say that SQL is pretty easy to read and understand - if this is homework, and you have to do this type of stuff often, you should go through T-SQL primers to get up to speed.

Answer 3

It's buggy to start with.

The INSERTED table is a pseudo table of all the rows being inserted (into BOOKISSUEDETAILS)

It starts a transaction because it's going to do two things: increment the number of books issued to the member (I think this is buggy when INSERTED contains more than one row because it's going to UPDATE multiple times - not sure this is reliable) and decrement the inventory of the book. It first checks to see that there is a book with 2 or more copies on the shelf (I think this is a bug), because it raises an error if there is 1 or fewer books in inventory.

In any case, it's not a realistic implementation for a library system, since each book is unique, your inventory will always be 1 or 0, even if you have multiple copies of a book, you need to know who has each one.

Answer 4

Triggers are used to maintain data integrity. Yes the same thing could be done in a stored proc or even through(shudder) dynamic sql. However, by placing the code in a trigger, you ensure that the rules will be followed no matter how the data gets into the database. Since in this case you affecting the inventory of books available to be checked out a trigger is the best place for this.

It appears to me as it the trigger is adding the number of books issued to the person in this instance to a running total in another table.

Then it check to see if the books are in inventory and if they are not rollbacks the whole transaction. (Personally I'd do the check first). If they are in inventory it then decrements the inventory by one because the book has been checked out.

One thing you will see is that the trigger references a table called INSERTED which you don't see in your database structure. This (and a simliar table called DELETED) is a table of data only available in a trigger. It contains the data that was just inserted.

Triggers fire once for each batch inserted (or updated or delted depending onthe trigger). That means if 1 rrecord is inserted, the inserted table will have one record, if 10,000 records were inserted in a batch, then the inserted table willhave 10,000 records. It is important to keep this in mind in designing a trigger.

Your design seems to be somewhat flawed. You should never use member name to join on as people do not have unique names. You don't want the records for the first John Smith to mingle with the records for the second John Smith. Names should always have a surrogate id that is used to ensure uniqueness.