Insert trigger doesnt do what i want it to do-CodePudding

i made a trigger which should avoid inserting a record in the rental 'uitlening' table if the person has an overdue payment (Boete). Unfortunately it doesnt work and i cant find the reason why. 'Boete' is an attribute of another table than rental. Can someone help me?

CREATE TRIGGER [dbo].[Trigger_uitlening]
ON [dbo].[Uitlening]
FOR INSERT
AS
BEGIN
    DECLARE @Boete decimal(10, 2);

    SET @Boete = (SELECT Boete FROM Lid WHERE LidNr = (SELECT LidNr FROM inserted));
    IF @Boete = 0
    BEGIN
        INSERT INTO Uitlening
        SELECT *
        FROM inserted;
    END;
END;

CodePudding user response：

Assuming you just want to insert records into [Rental] of those users, who have [PaymentDue] <= 0. As you mentioned in your last comment:

no record in rental can be inserted if the person has a PaymentDue thats greater than zero

And other records should be silently discarded as you didn't give a clear answer to @Larnu's question:

should that row be silently discarded, or should an error be thrown?

If above assumptions are true, your trigger would look like:

CREATE TRIGGER [dbo].[Trigger_uitlening]
ON [Rental]
INSTEAD OF INSERT
AS
BEGIN

    INSERT INTO [Rental] ( [DATE], [personID], [productID])
    SELECT i.[DATE], i.[personID], i.[productID]
    FROM INSERTED i
    INNER JOIN Person p ON i.[personID] = p.[personID]
    WHERE p.[PaymentDue] <= 0

END;

CodePudding user response：

It sounds like what you actually need is a cross-table constraint.

You can either do this by throwing an error in the trigger:

CREATE TRIGGER [dbo].[Trigger_uitlening]
ON [Rental]
AFTER INSERT
AS

SET NOCOUNT ON;

IF EXISTS (SELECT 1
    FROM inserted i
    INNER JOIN dbo.Person p ON i.[personID] = p.[personID]
    WHERE p.[PaymentDue] <= 0
)
    THROW 50001, 'PaymentDue is less than 0', 1;

A better solution is to utilize a trick with an indexed view. This is based on an article by spaghettidba.

We first create a dummy table of two rows

CREATE TABLE dbo.DummyTwoRows (dummy bit not null);
INSERT DummyTwoRows (dummy) VALUES(0),(1);

Then we create the following view:

CREATE VIEW dbo.vwPaymentLessThanZero
WITH SCHEMBINDING  -- needs schema-binding
AS
SELECT 1 AS DummyOne
FROM dbo.Rental r
JOIN dbo.Person p ON p.personID = r.personID
CROSS JOIN dbo.DummyTwoRows dtr
WHERE p.PaymentDue <= 0;

This view should in theory always have no rows in it. To enforce that, we create an index on it:

CREATE UNIQUE CLUSTERED INDEX CX_vwPaymentLessThanZero
  ON dbo.vwPaymentLessThanZero (DummyOne);

Now if you try to add a row that qualifies for the view, it will fail with a unique violation, because the cross-join is doubling up the rows.

Note that in practice the view index takes up no space because there are never any rows in it.

CodePudding user response：

Attention! When you create a trigger by FOR INSERT or AFTER INSERT then don't write insert into table select * from inserted, because DB will insert data automatically, you can do only ROLLBACK this process. But, when creating a trigger by INSTEAD OF INSERT then you must write insert into table select * from inserted, else inserting not be doing.