Trigger gives false null values

Question

I'm trying to create a trigger that is executed upon inserting new rows. The trigger's goal is to add information to the table every time when employer loans something.

The table that the trigger is inserted : Employer.Loan

ID ToolId CardId Time
1    4     1001    2021-05-08 15:41:47

The table that the trigger should insert information : Employer.LoanStats

CardId Year Month Quantity

So for example, I want to add values to the Loan table :

INSERT INTO Employer.Loan(ToolId,CardId,Time) *ID is automatic
VALUES (3, 1005, GETDATE() - 40, 3, 1005, GETDATE(), (4, 1002, GETDATE())

The results are fine in the table LoanStats:

CardId Year Month Quantity
1005   2021   4      1
1005   2021   3      1
1002   2021   4      1

However, when I try to add more values in, it stops working and gives NULL values:

INSERT INTO Employer.Loan(ToolId,CardId,Time) *ID is automatic
VALUES (3, 1005, GETDATE() - 40, (4, 1003, GETDATE())

Results :

CardId Year Month Quantity
---------------------------
1005   2021   4      2
1005   2021   3     NULL
1002   2021   4      1
1003   2021   4     NULL

I have tried the following:

CREATE TRIGGER AddStats
ON Employer.Loan
FOR INSERT
AS
    IF (SELECT COUNT(*) FROM inserted) > 0
    BEGIN
        DECLARE @CardId int,
                @Year int,
                @Month int,
                @Time DATE
 
       DECLARE Updated CURSOR FOR 
            SELECT CardId, Time
            FROM inserted

       OPEN Updated

       FETCH NEXT FROM Updated INTO @CardId, @Time

       WHILE (@@FETCH_STATUS = 0)
       BEGIN
           SET @Year = DATEPART(YEAR, @Time)
           SET @Month = DATEPART(MONTH, @Time)

           IF EXISTS (SELECT * FROM Employer.LoanStats 
                      WHERE CardId = @CardId AND Year = @Year AND Month = @Month)
           BEGIN
               UPDATE Employer.LoanStats
               SET Quantity = (SELECT Employer.LoanStats.Quantity + 1
                               FROM inserted
                               WHERE inserted.CardId = Employer.LoanStats.CardId 
                                 AND Employer.LoanStats.Month = @Month 
                                 AND Employer.LoanStats.Year = @Year)
               WHERE CardId IN (SELECT CardId FROM inserted)
           END
           ELSE
           BEGIN
               INSERT INTO Employer.LoanStats (CardId, Year, Month, Quantity)
               VALUES (@CardId, @Year, @Month, 1)
           END

           FETCH NEXT FROM Updated INTO @CardId, @Time
       END

       CLOSE Updated
       DEALLOCATE Updated
    END

Answer 1

Your UPDATE statement is flawed and is not joining correctly.

Be that as it may, the whole procedure is complete overkill. It can be done in a single MERGE statement:

CREATE TRIGGER AddStats
ON Employer.Loan
AFTER INSERT
AS
    SET NOCOUNT ON;
    IF NOT EXISTS (SELECT 1 FROM inserted)
        RETURN;

    MERGE Employer.LoanStats l
    USING (
        SELECT *,
            Year = DATEPART(YEAR, Time),
            Month = DATEPART(MONTH, Time)
            FROM inserted
    ) i
    ON i.CardId = l.CardId AND i.Year = l.Year AND i.Month = l.Month
    WHEN MATCHED THEN UPDATE SET
        Quantity += 1
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (CardId, Year, Month, Quantity)
        VALUES (i.CardId, i.Year, i.Month, 1);

GO