none
Instead Of Insert Trigger check 2 table before insert RRS feed

  • Question

  • Dear all,

    I need to create a trigger to perform the following action:

    Allow insert of values into dbo.Rental if following is fulfilled or print error not successful:

    • StaffID in dbo.Rental must exists in dbo.staff
    • Existing CarNo in dbo.Car is not found in dbo.Rental

    CREATE TABLE Staff( StaffNum Char(5) NOT NULL, StaffID Char(9) NOT NULL, name Char(128) NOT NULL, CONSTRAINT Staff_PK PRIMARY KEY(StaffNum)); CREATE TABLE CAR( CarNo Char(8) NOT NULL, brand Char(64) NOT NULL, PurhasePrice NUMERIC(10,2) NOT NULL, CONSTRAINT Car_PK PRIMARY KEY(CarNo)); CREATE TABLE RENTAL( loanID Int IDENTITY(1000,1) NOT NULL, CarNo Char(8) NOT NULL, StaffNum Char(5) NOT NULL, dateLoan DATE NOT NULL, receiveDate DATE NULL, remark Char(20) NULL,

        CONSTRAINT RENTAL_PK       PRIMARY KEY(loanID),
        CONSTRAINT RENTAL_CarNo_FK      FOREIGN KEY(CarNo) REFERENCES CAR(CarNo)
                                        ON UPDATE CASCADE
                                        ON DELETE NO ACTION,
    CONSTRAINT RENTAL_StaffNum_FK FOREIGN KEY(StaffNum) REFERENCES Staff(StaffNum)
                                        ON UPDATE CASCADE
                                        ON DELETE NO ACTION,


    -- Create view for Instead of insert Trigger
    CREATE VIEW LoanView AS
    SELECT R.CarNo, R.StaffNum, R.dateLoan, R.receiveDate, R.remark         
    FROM RENTAL AS R, CAR AS C
    WHERE R.CarNo = C.CarNo; 

    CREATE TRIGGER [Insert] ON [dbo].[LoanView] INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; DECLARE @LoanID AS Int, @CarNo AS Char(8), @StaffNum AS Char(5), @dateLoan AS DATE, @receiveDate AS DATE, @Remark AS Char(20), @RowCount AS Int, @CarNoCheck AS Int SELECT @LoanID, @CarNo, @StaffNum, @dateLoan, @receiveDate, @Remark FROM INSERTED; SELECT * FROM Staff WHERE StaffNum = @StaffNum SET @RowCount = @@ROWCOUNT IF @RowCount = 0 BEGIN PRINT '***********************************************' PRINT Staff is not registered yet.' PRINT '' PRINT '***********************************************' PRINT '' END ELSE BEGIN SELECT @CarNoCheck = COUNT(*) FROM RENTAL AS R WHERE R.LoanID = @LoanID AND R.dateLoan = @dateLoan AND R.CarNo = @CarNo; IF @CarNoCheck > 0 BEGIN PRINT '***********************************************' PRINT '' PRINT ' The CAR is already rented to another staff.' PRINT 'Staff name =' +name PRINT ' Vehicle Number = ' +@CarNo PRINT '' PRINT '***********************************************' END ELSE BEGIN INSERT INTO RENTAL VALUES (@CarNo, @StaffNum, @dateLoan, @receiveDate, @Remark); SET @LoanID = @@IDENTITY; COMMIT TRANSACTION; PRINT '***********************************************' PRINT '' PRINT ' Car Number = '+@CarNo PRINT ' has been rented out ' PRINT ' to staff number' +@StaffNum PRINT '' PRINT '***********************************************' END END END


    -- SQL to insert Loan record that is valid:

    INSERT INTO LoanView (CarNo, StaffNum, dateLoan, receiveDate, remark)VALUES

    ('SBA1291W','T05', '2/11/2013','2/12/2013','Received in good condition');


    After running the SQL insert, there is an error:

    Cannot insert the value NULL into column 'CarNo', table 'dbo.RENTAL'; column does not allow nulls. INSERT fails.
    The statement has been terminated.


    Thank you.


    • Edited by yummymelon Saturday, October 11, 2014 5:16 PM
    Saturday, October 11, 2014 5:13 PM

Answers

  • I think you are not assigning the values...try the below:

    Alter TRIGGER [Insert] ON [dbo].[LoanView]
    
    INSTEAD OF INSERT
    
    AS
    
    BEGIN
    
           SET NOCOUNT ON;
    
    DECLARE @LoanID  AS Int,
    @CarNo  AS Char(8),
    @StaffNum  AS Char(5), @dateLoan   AS DATE,
    @receiveDate  AS DATE, @Remark AS Char(20), @RowCount AS Int,
    @CarNoCheck AS Int
    
           SELECT  @CarNo=CarNo, @StaffNum=StaffNum, @dateLoan=dateLoan, @receiveDate=receiveDate,
    
           @Remark=remark
    
           FROM INSERTED;
    
    
           SELECT * FROM Staff
    
           WHERE StaffNum = @StaffNum
    
    SET @RowCount = @@ROWCOUNT 
    
           IF @RowCount = 0
    
                  BEGIN     
                  PRINT '***********************************************' 
                  PRINT 'Staff is not registered yet.'
                  PRINT ''
                  PRINT '***********************************************'
                  PRINT ''             
           END
     
    
           ELSE
                  BEGIN
                        SELECT @CarNoCheck = COUNT(*)
                         FROM RENTAL AS R
                         WHERE R.LoanID = @LoanID
                         AND R.dateLoan = @dateLoan
                         AND R.CarNo = @CarNo;
    
            IF @CarNoCheck > 0        
    
                         BEGIN
    
                         PRINT '***********************************************'
                         PRINT ''
                         PRINT '       The CAR is already rented to another staff.'
                         
                         PRINT '       Vehicle Number = ' +@CarNo
    
                         PRINT ''
    
                         PRINT '***********************************************'
                         END
           ELSE
                  BEGIN       
                         INSERT INTO RENTAL VALUES 
    
                         (@CarNo, @StaffNum, @dateLoan, @receiveDate, @Remark);
                         SET @LoanID = @@IDENTITY;
    
                  COMMIT TRANSACTION;               
                  PRINT '***********************************************'
                  PRINT ''
                  PRINT '       Car Number = '+@CarNo
                  PRINT ' has been rented out '
                  PRINT '       to staff number' +@StaffNum
                  PRINT ''
                  PRINT '***********************************************'
                  END
           END
    END
    

    • Marked as answer by yummymelon Saturday, October 11, 2014 8:16 PM
    Saturday, October 11, 2014 5:54 PM
  • Allow insert of values into dbo.Rental if following is fulfilled or print error not successful:

    • StaffID in dbo.Rental must exists in dbo.staff
    • Existing CarNo in dbo.Car is not found in dbo.Rental

    If this is your scenario why do you need trigger for this

    This would just require

    1. Creating foreign key constraintin dbo.Rental StaffID field to point to dbo.Staff,StaffID

    2. Creating a check constraint in Rental table to be based on UDF as below

    CREATE FUNCTION CarAvailability
    (
    @CarNo Char(8),
    @dateLoan Date
    )
    RETURNS bit 
    AS
    BEGIN 
    DECLARE @CarAvailable bit = 1
    
    IF EXISTS (
    SELECT 1
    FROM dbo.Rental
    WHERE CarNo = @CarNo
    AND (@dateLoan BETWEEN dateLoan AND receiveDate
    OR (@dateLoan > dateLoan AND receiveDate IS NULL))
    )
    BEGIN
    SET  @CarAvailable = 0
    END
    RETURN (@CarAvailable)
    END

    And create check constraint as

    ALTER TABLE dbo.Rental ADD CONSTRAINT Chk_NoOverlapCarRental CHECK(dbo.CarAvailability(CarNo,dateLoan)=1)


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by yummymelon Saturday, October 11, 2014 8:17 PM
    Saturday, October 11, 2014 6:35 PM
  • I will post the solution that you are looking for, but this is very bad solution and you don't need any trigger as other mentioned, for this issue. In any case you have to learn basic SQL since your issue dont have anything to do with triggers but with simple queries as I wrote above! you have very basic mistakes which in 1 week learning of basic SQL you can learn not to do! please read my above response. I did not write it or my use :-)

    First in order to test the trigger you must have some data.

    insert Staff (StaffNum,StaffID,name) values (444,1,'a'),(555,2,'b')
    insert CAR (CarNo,brand,PurhasePrice) values (1,'',3), (2,'',3)
    GO

    Now the trigger:

    ALTER TRIGGER [Ariely_InsertLoanView_Trig] ON [dbo].[LoanView]
    INSTEAD OF INSERT
    AS BEGIN
    	
    	PRINT '************ Starting ************'
    	SET NOCOUNT ON;
    
    	-->> I use 2 queries, one for good records and one for bad records.
    	--   This can be done in one query but it will be easier for you to understand like this.
    
    	-->> First we will deal with bad records
    	Declare @MyMessage NVARCHAR(MAX) = ''
    	select @MyMessage = @MyMessage + CONVERT(NVARCHAR(MAX),MyMessage) + char(10) + Char(13)
    	from (
    		select 
    			MyMessage = case
    				when StaffNum not in (select StaffNum from Staff) then N'Staff ' + CONVERT(nvarchar(MAX),StaffNum) + N' is not registered yet'
    				when CarNo in (select CarNo from RENTAL) then N'The CAR ' + CONVERT(nvarchar(MAX),CarNo) + N' is already rented to another staff'
    			end
    		from inserted
    		where 
    			StaffNum not in (select StaffNum from Staff) -- we can use JOIN instead of sub-query
    			or CarNo in (select CarNo from RENTAL)
    	) T
    	PRINT @MyMessage
    
    	-->> Now we will insert all good records from our SET (all rows from the inserted table which fit the filter)
    	INSERT INTO RENTAL (CarNo, StaffNum, dateLoan, receiveDate, Remark) 
    	select CarNo, StaffNum, dateLoan, receiveDate, Remark
    	from inserted
    	where 
    		StaffNum in (select StaffNum from Staff) -- we can use JOIN instead of sub-query
    		and CarNo not in (select CarNo from RENTAL)
    
    END

    and here is the testing:

    -- Good insert:
    INSERT INTO LoanView (CarNo, StaffNum, dateLoan, receiveDate, remark)
    VALUES ('1','444', '2013-11-02','2013-12-02','Received in good');
    
    -- Bad insert: same as before become bad since the CAR is already rented to another staff
    INSERT INTO LoanView (CarNo, StaffNum, dateLoan, receiveDate, remark)
    VALUES ('1','444', '2013-11-02','2013-12-02','Received in good');
    
    -- bad insert: Staff is not registered yet
    INSERT INTO LoanView (CarNo, StaffNum, dateLoan, receiveDate, remark)
    VALUES ('1','666', '2013-11-02','2013-12-02','Received in good');



    [Personal Site]  [Blog]  [Facebook]
    signature

    Saturday, October 11, 2014 9:58 PM
    Moderator

All replies

  • I think you are not assigning the values...try the below:

    Alter TRIGGER [Insert] ON [dbo].[LoanView]
    
    INSTEAD OF INSERT
    
    AS
    
    BEGIN
    
           SET NOCOUNT ON;
    
    DECLARE @LoanID  AS Int,
    @CarNo  AS Char(8),
    @StaffNum  AS Char(5), @dateLoan   AS DATE,
    @receiveDate  AS DATE, @Remark AS Char(20), @RowCount AS Int,
    @CarNoCheck AS Int
    
           SELECT  @CarNo=CarNo, @StaffNum=StaffNum, @dateLoan=dateLoan, @receiveDate=receiveDate,
    
           @Remark=remark
    
           FROM INSERTED;
    
    
           SELECT * FROM Staff
    
           WHERE StaffNum = @StaffNum
    
    SET @RowCount = @@ROWCOUNT 
    
           IF @RowCount = 0
    
                  BEGIN     
                  PRINT '***********************************************' 
                  PRINT 'Staff is not registered yet.'
                  PRINT ''
                  PRINT '***********************************************'
                  PRINT ''             
           END
     
    
           ELSE
                  BEGIN
                        SELECT @CarNoCheck = COUNT(*)
                         FROM RENTAL AS R
                         WHERE R.LoanID = @LoanID
                         AND R.dateLoan = @dateLoan
                         AND R.CarNo = @CarNo;
    
            IF @CarNoCheck > 0        
    
                         BEGIN
    
                         PRINT '***********************************************'
                         PRINT ''
                         PRINT '       The CAR is already rented to another staff.'
                         
                         PRINT '       Vehicle Number = ' +@CarNo
    
                         PRINT ''
    
                         PRINT '***********************************************'
                         END
           ELSE
                  BEGIN       
                         INSERT INTO RENTAL VALUES 
    
                         (@CarNo, @StaffNum, @dateLoan, @receiveDate, @Remark);
                         SET @LoanID = @@IDENTITY;
    
                  COMMIT TRANSACTION;               
                  PRINT '***********************************************'
                  PRINT ''
                  PRINT '       Car Number = '+@CarNo
                  PRINT ' has been rented out '
                  PRINT '       to staff number' +@StaffNum
                  PRINT ''
                  PRINT '***********************************************'
                  END
           END
    END
    

    • Marked as answer by yummymelon Saturday, October 11, 2014 8:16 PM
    Saturday, October 11, 2014 5:54 PM
  • >SELECT  @CarNo=CarNo, @StaffNum=StaffNum, @dateLoan=dateLoan, @receiveDate=receiveDate,

          
    @Remark=remark

           FROM INSERTED
    ;

    What happens with multiple rows INSERT?




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014




    Saturday, October 11, 2014 6:04 PM
    Moderator
  • Hi,

    There are several problems with your code, which have nothing to do with the trigger. for example this part:

    	SELECT @LoanID, @CarNo, @StaffNum, @dateLoan, @receiveDate,	@Remark
    	FROM INSERTED;
    	SELECT * FROM Staff
    	WHERE StaffNum = @StaffNum

    I am guessing that you want to SET the values of the variables but this is not the way to do it.

    another example is this line: 

    PRINT 'Staff name =' + name

    You can not use column name like this in a print

    ** In any case, please check the code and make sure that it reproduce you error, since as I mentioned this code will produce several errors probably.


    [Personal Site]  [Blog]  [Facebook]
    signature

    Saturday, October 11, 2014 6:07 PM
    Moderator
  • Hi Kalman,

    I assume that the insert is only intended for a single row as lots of other queries within the Trigger support only for a single row insert. If that is not the case, then OP needs to change the entire code.

    I agree, Your point is very valid.

    Saturday, October 11, 2014 6:12 PM
  • Moreover, You try to insert more then 20 char into CAHR(20)

    INSERT INTO LoanView (CarNo, StaffNum, dateLoan, receiveDate, remark)
    VALUES ('SBA1291W','T05', '2013-11-02','2013-12-02','Received in good condition');

    while the column is remark is  Char(20)


    [Personal Site]  [Blog]  [Facebook]
    signature

    Saturday, October 11, 2014 6:18 PM
    Moderator
  • Allow insert of values into dbo.Rental if following is fulfilled or print error not successful:

    • StaffID in dbo.Rental must exists in dbo.staff
    • Existing CarNo in dbo.Car is not found in dbo.Rental

    If this is your scenario why do you need trigger for this

    This would just require

    1. Creating foreign key constraintin dbo.Rental StaffID field to point to dbo.Staff,StaffID

    2. Creating a check constraint in Rental table to be based on UDF as below

    CREATE FUNCTION CarAvailability
    (
    @CarNo Char(8),
    @dateLoan Date
    )
    RETURNS bit 
    AS
    BEGIN 
    DECLARE @CarAvailable bit = 1
    
    IF EXISTS (
    SELECT 1
    FROM dbo.Rental
    WHERE CarNo = @CarNo
    AND (@dateLoan BETWEEN dateLoan AND receiveDate
    OR (@dateLoan > dateLoan AND receiveDate IS NULL))
    )
    BEGIN
    SET  @CarAvailable = 0
    END
    RETURN (@CarAvailable)
    END

    And create check constraint as

    ALTER TABLE dbo.Rental ADD CONSTRAINT Chk_NoOverlapCarRental CHECK(dbo.CarAvailability(CarNo,dateLoan)=1)


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by yummymelon Saturday, October 11, 2014 8:17 PM
    Saturday, October 11, 2014 6:35 PM
  • There has been several comments on this trigger already, and I fully agree that the trigger must be written so that it can handle multi-row inserts.

    I only like to point out one detail which has not been covered yet:

    INSERT INTO RENTAL VALUES
     (@CarNo, @StaffNum, @dateLoan, @receiveDate, @Remark);

    You should always include an explicit column list in your INSERT statements. If you write like this, the statement will blow up, if a column is added to the table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, October 11, 2014 7:02 PM
  • Hi,

    I am quite new to SQL and I am asked to create a SQL trigger for a database.

    I want to implement a constraint that prevent data from being inserted into Rental table if staff is not registered in the company and car has been rented out.

    I know that foreign key constraint can associate rows of Rental table to reference parent table within the same database but I need to come out with a Trigger to return error statement /rollback transaction.

    Although i do not explain very well, not sure how i can get the trigger to work accordingly, does anyone has any idea ?

    Thank you.

    Saturday, October 11, 2014 7:16 PM
  • I will post the solution that you are looking for, but this is very bad solution and you don't need any trigger as other mentioned, for this issue. In any case you have to learn basic SQL since your issue dont have anything to do with triggers but with simple queries as I wrote above! you have very basic mistakes which in 1 week learning of basic SQL you can learn not to do! please read my above response. I did not write it or my use :-)

    First in order to test the trigger you must have some data.

    insert Staff (StaffNum,StaffID,name) values (444,1,'a'),(555,2,'b')
    insert CAR (CarNo,brand,PurhasePrice) values (1,'',3), (2,'',3)
    GO

    Now the trigger:

    ALTER TRIGGER [Ariely_InsertLoanView_Trig] ON [dbo].[LoanView]
    INSTEAD OF INSERT
    AS BEGIN
    	
    	PRINT '************ Starting ************'
    	SET NOCOUNT ON;
    
    	-->> I use 2 queries, one for good records and one for bad records.
    	--   This can be done in one query but it will be easier for you to understand like this.
    
    	-->> First we will deal with bad records
    	Declare @MyMessage NVARCHAR(MAX) = ''
    	select @MyMessage = @MyMessage + CONVERT(NVARCHAR(MAX),MyMessage) + char(10) + Char(13)
    	from (
    		select 
    			MyMessage = case
    				when StaffNum not in (select StaffNum from Staff) then N'Staff ' + CONVERT(nvarchar(MAX),StaffNum) + N' is not registered yet'
    				when CarNo in (select CarNo from RENTAL) then N'The CAR ' + CONVERT(nvarchar(MAX),CarNo) + N' is already rented to another staff'
    			end
    		from inserted
    		where 
    			StaffNum not in (select StaffNum from Staff) -- we can use JOIN instead of sub-query
    			or CarNo in (select CarNo from RENTAL)
    	) T
    	PRINT @MyMessage
    
    	-->> Now we will insert all good records from our SET (all rows from the inserted table which fit the filter)
    	INSERT INTO RENTAL (CarNo, StaffNum, dateLoan, receiveDate, Remark) 
    	select CarNo, StaffNum, dateLoan, receiveDate, Remark
    	from inserted
    	where 
    		StaffNum in (select StaffNum from Staff) -- we can use JOIN instead of sub-query
    		and CarNo not in (select CarNo from RENTAL)
    
    END

    and here is the testing:

    -- Good insert:
    INSERT INTO LoanView (CarNo, StaffNum, dateLoan, receiveDate, remark)
    VALUES ('1','444', '2013-11-02','2013-12-02','Received in good');
    
    -- Bad insert: same as before become bad since the CAR is already rented to another staff
    INSERT INTO LoanView (CarNo, StaffNum, dateLoan, receiveDate, remark)
    VALUES ('1','444', '2013-11-02','2013-12-02','Received in good');
    
    -- bad insert: Staff is not registered yet
    INSERT INTO LoanView (CarNo, StaffNum, dateLoan, receiveDate, remark)
    VALUES ('1','666', '2013-11-02','2013-12-02','Received in good');



    [Personal Site]  [Blog]  [Facebook]
    signature

    Saturday, October 11, 2014 9:58 PM
    Moderator
  • Without writing code for you, I would think that a vehicle is identified by a VIN (required by law), the rental agreement identifiers would be structured (have the office, type of rental etc. in it) and that employees seldom have a 128 character name. 

    A vehicle has a status which includes "rented", "out of service", "in the motorpool", "destroyed", etc as options. A status is a state of being, so it has a (start_time, end_time) temporal attribute. I do not see any of that stuff in your DDL. 

    DRI actions will prevent a staff member from making an illegal rental. Since triggers and cursors are procedural code, we hate them. My guess, without specs is that you need a procedure that makes a valid rental from the motorpool and staff. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, October 12, 2014 1:50 AM
  • Thanks all about your detailed answers :)
    Thursday, October 16, 2014 4:02 AM
  • You are most welcome :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    Friday, October 17, 2014 11:07 AM
    Moderator