CODEWITHSUNDEEP
sql-servertriggersinsert-update
new-learner
new-learner

Reputation: 73

SQL Server trigger does not work as expected

I have 3 tables, Hour, Daily, Record

As soon as the new record is added to Daily table, the new row is generated in Record table with pre filled DailyId and EId values . There is no value for HourId. Therefore, I want to have a trigger so every time new record is inserted to Hour table, it updates the HourId whose value is from Id of Hour table. Obviously, it must match EId and DailyId in tables, additionally, Date of Daily table has to be in between StartDate and EndDate of the new record in Hour table.

Here is my trigger, but it does not work, whenever I add new record to Hour table.

ALTER TRIGGER [dbo].[insertRecord]
ON  [dbo].[Hour]
AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @EmpId nvarchar, @StartDate datetime, @EndDate datetime, 
            @EntId int, @ToHourId int

    SELECT @EmpId = EId FROM inserted
    SELECT @StartDate =  StartDate FROM inserted
    SELECT @EndDate = EndDate FROM inserted

    SELECT @EntId = a.Id, @ToHourId = c.HourId  
    FROM (Daily a 
    JOIN Record b ON a.Id = b.DailyId) 
    JOIN Hour c ON b.EId = c.EId
    WHERE b.EId = @EmpId AND a.Date BETWEEN @StartDate AND @EndDate

    -- Insert statements for trigger here

    UPDATE Record
    SET Record.HourId = @ToHourId
    FROM Record
    WHERE Record.EId = @EmpId 
        AND Record.DailyId = @EntId
        AND Record.HourId IS NULL 
END

Upvotes: 0

Views: 57

Answers (1)

marc_s
marc_s

Reputation: 754368

Your fundamental flaw is that you seem to expect the trigger to be fired once per row - this is NOT the case in SQL Server. Instead, the trigger fires once per statement, and the pseudo table Inserted might contain multiple rows.

Given that that table might contain multiple rows - which one do you expect will be selected here??

SELECT @EmpId = EId FROM inserted

It's undefined - you'll get the values from one arbitrary row in Inserted, and all others are ignored - usually not what you want.

You need to rewrite your entire trigger with the knowledge the Inserted pseudo table WILL contain multiple rows! You need to work with set-based operations - don't expect just a single row in Inserted !

Upvotes: 2

Related Questions