0

This is 1/4 of an overall assignment and I need help with this part.

The assignment question:

Create a trigger on the Rental table that will check when a new rental is to be inserted, that the member (MemberId) that is renting a book:

a. Does not have more than 2 books rented out: the books that are rented out are identified by having ReturnDate as NULL.

b. That the member does not owe more than 50 dollars to the library (the BalanceOwing attribute of the Member table).

What I have done so far :

CREATE TRIGGER tr_tblRental_ForInsert 
ON dbo.Rental
FOR INSERT
AS
BEGIN
    declare @MemberId int
    select  @MemberId int = MemberId from inserted

I would appreciate tips on how to go about this.

  • 1
    Could you post your schema for the two tables? That will help us point you in the right direction. – Aaron Apr 5 '16 at 4:10
  • Without answering an assignment question for you, the 'tip' you're looking for is to make use of the logical 'inserted' table within your trigger. Grab the member ID from the table, then perform your checking logic on it before either committing or rolling back the transaction... – Molenpad Apr 5 '16 at 10:20
2

Don't assume that the trigger will always handle a single row inserted in the table. If you're inserting more than one row in Rental, the code you attempted to write will fail.

The logical tables inserted and deleted contain all the rows modified by the DML operation and they must be joined or correlated to the base table (or other tables) in order to take all the rows modified into consideration.

As far as the business logic required is concerned, this is a possible implementation:

CREATE TRIGGER tr_tblRental_ForInsert 
ON dbo.Rental
FOR INSERT
AS
BEGIN
    -- Always add this to your triggers: some libraries don't like
    -- triggers altering the number of rows affected by the DML operation
    SET NOCOUNT ON;

    -- a. Does not have more than 2 books rented out: 
    -- the books that are rented out are identified by having ReturnDate as NULL
    IF EXISTS (
        SELECT COUNT(*)
        FROM dbo.Rental
        WHERE MemberId IN (
                SELECT MemberId 
                FROM inserted
            )
            AND ReturnDate IS NULL
        GROUP BY MemberId
        HAVING COUNT(*) > 2
    )
    BEGIN
        ROLLBACK;
        THROW 51000, 'Members are not allowed to rent more than 2 books at a time.', 1;
    END


    -- b. That the member does not owe more than 50 dollars 
    -- to the library (the BalanceOwing attribute of the Member table).
    IF EXISTS (
        SELECT BalanceOwing 
        FROM dbo.Member 
        WHERE MemberId IN (
                SELECT MemberId 
                FROM inserted
            )
            AND BalanceOwing > 50
    )
    BEGIN
        ROLLBACK;
        THROW 51000, 'Members are not allowed to rent more books if they owe more than $50 to the library.', 1;
    END

END

Please do not use this code in your project/homework if you don't understand what it does: your teacher will ask questions and you'd better have answers. If you need help understanding the code, place a comment here.

| improve this answer | |
0

My fundamental tip on how to go about this would be split this into several smaller problems. Assignment questions are certainly meant to be complex, because the intention is to test your knowledge on a number of issues. As a learner, however, you may find them much easier to tackle if you take on each issue separately.

And I suggest you consider the following sub-issues in your complex problem.

Inserted is a row set

Some database products allow you to define per-row and per-statement triggers. SQL Server supports only per-statement ones. The implication is that the inserted pseudo-table can have more than one row (because it is possible to insert more than one row with a single INSERT statement).

So, normally you will not use a scalar variable like @MemberId to pull a single value out of inserted and, probably, use it further in another query. That is iterative thinking. Instead, you should think in sets – whatever you meant to do with that single member ID should be done to every row at once, in a single statement.

FOR INSERT = rows are already in the table

…but the transaction is not yet committed. The Transact-SQL FOR INSERT syntax is a synonym for the standard SQL AFTER INSERT (which is accepted in Transact-SQL as well). The words "after insert" imply that at the time of the trigger's execution the inserted rows are already in the table on which the trigger is defined (in your case, dbo.Rental), even though the transaction is not committed yet.

That is an important point to consider given the fact that one of your trigger's tasks is to check the number of books already being rented – and it is also a crucial point to keep in mind with regard to cancelling the insert.

Acting on finding matches in a trigger

Now that you know that inserted is a set and needs to be processed as such and that the new rows are already in the table as well as in inserted, how to put the two facts together? How to apply conditions to all rows at once and then use the results?

One of the most common patterns in triggers is this:

IF EXISTS  -- or IF NOT EXISTS
  (
    subquery
  )
BEGIN
  do something
END;

The subquery would commonly be a query involving inserted and, often, the target table, sometimes other tables as well – all depending on what you want to check.

The do something could be logging the just established fact of presence/absence of rows (i.e. inserting a row with a text message into a custom log table of yours) or just rolling back the pending transaction (thus discarding the effect of the INSERT statement that caused the trigger execution). Or it could be both. Or it could be that and raising a custom exception for the client to catch. The way spaghettidba's answer is suggesting it, the trigger will roll back the transaction and raise a custom exception, which, again, is common enough – and may well be what your assignment question actually requires the trigger to do (only it is not stated explicitly).

You can take your time with the issues above – or you can move on to...

Implementing the actual tests

I would argue that this should have been asked separately, but since I have decided to answer the question anyway, here goes.

Test A

Forget about inserted for a moment and just think about dbo.Rental. Assuming the table already has some rows, including pending rentals, how would you go about finding the pending rentals?

That should be easy, of course:

SELECT
  *
FROM
  dbo.Rental
WHERE
  ReturnDate IS NULL

Next step, show distinct member IDs of those who have pending rentals:

SELECT DISTINCT
  MemberID
FROM
  dbo.Rental
WHERE
  ReturnDate IS NULL

or

SELECT
  MemberID
FROM
  dbo.Rental
WHERE
  ReturnDate IS NULL
GROUP BY
  MemberID

The former way seems cleaner, the latter, however, is more flexible, as it will be easier to expand when we want to add a filter on the number of rented books per member. You would just need to add a HAVING clause:

SELECT
  MemberID
FROM
  dbo.Rental
WHERE
  ReturnDate IS NULL
GROUP BY
  MemberID
HAVING
  COUNT(*) > 2

So, the last query will give you all the members that are renting more than two books at the moment. How to have your trigger prevent any of them from renting another book? By simply adding a condition to the WHERE clause:

SELECT
  MemberID
FROM
  dbo.Rental
WHERE
  ReturnDate IS NULL
  AND MemberID IN (SELECT MemberID FROM inserted)
GROUP BY
  MemberID
HAVING
  COUNT(*) > 2

Use that as the subquery in the IF EXISTS pattern mentioned earlier and specify the appropriate action to do when the predicate evaluates to true (see above).

One last point I would like to make regarding this test depends very much on how you interpret the wording in your assignment question. My basic interpretation is this: a member is allowed to have two pending rentals before renting again. Assuming that the person will be allowed to rent no more than one book at this point and keeping in mind that the new rental is already in the table when the trigger is executed, the total number of rented books allowed in the table for any given member should in fact be three. Therefore, the last query's HAVING should actually read:

HAVING
  COUNT(*) > 3

in order for the rollback to work as expected.

And that would be it for Test A.

Test B

This is easier to implement, because this test is not affected by the fact that the new rentals are already in the dbo.Rental table – it is enough that they are in inserted.

But again, do not think about inserted so far and think about how to get all the members that owe more than $50. This is a simple and straightforward query:

SELECT
  *
FROM
  dbo.Member
WHERE
  BalanceOwing > 50

The second, and last, step is to at the filter on inserted, just as in the previous test:

SELECT
  *
FROM
  dbo.Member
WHERE
  BalanceOwing > 50
  AND MemberID IN (SELECT MemberID FROM inserted)

So much for Test B. Add another IF EXISTS, use the above query as the EXISTS subquery and customise the do something action appropriately.

In conclusion

It does not matter much whether you are implementing a trigger or just writing a complex query – most often you will need to be able to think in sets to come up with an efficient solution. That might not seem easy, particularly at first, and that is where splitting the problem into smaller parts helps immensely, especially when you are still learning.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.