Insert trigger doesnt do what i want it to do

Question

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;

Answer 1

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.

Answer 2

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.