Sql-server – How to create an insert trigger

sql servertrigger

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.

Best Answer

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.

Related Solutions

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

What you presented is essentially the correct way for interrupting triggers in MySQL 5.1

I wrote about this before

Dec 23, 2011 : check constraint does not work?
Apr 25, 2011 : Trigger in MySQL to prevent insertion

You cannot do Dynamic SQL in triggers.

You may have to resort to coding the my_signal like this:

CREATE PROCEDURE `my_signal`(error_type INT)
BEGIN
    IF error_type = 1 THEN
        UPDATE `Error: can not insert new OWNER !` set x=1;
    END IF;
    IF error_type = 2 THEN
        UPDATE `Error: Row can not reference itself!` set x=1;
    END IF;
    .
    .
END//

then change the trigger to look like this:

DELIMITER $$
CREATE
  TRIGGER `employee_before_insert` BEFORE INSERT
    ON `Employee`
    FOR EACH ROW BEGIN
      CASE
       WHEN NEW.designation = 'OWNER'  THEN
          CALL my_signal(1);
       WHEN NEW.SSN = NEW.MSSN THEN
          CALL my_signal(2);
      END CASE; 
  END$$   
DELIMITER ;

Sorry for the clumsy programming but Signal Processing is MySQL 5.1 is horrible.

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Best Answer

Related Solutions

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

Related Question