wes wes - 21 days ago 6
SQL Question

Visual Studio SQL Server Insert Statement

This is my Trigger. The first two UPDATE Statements work but, the UPDATE Student does not work.

"UPDATE Student SET PointsAccumulated = PointsAccumulated + @Points
Where @StudentID = StudentID"


CREATE TRIGGER [Trigger]
ON [dbo].[Attendance]
After insert
AS
BEGIN
DECLARE @EventID int
Declare @StudentID nchar
DECLARE @Points int

SELECT @EventID=EventID from inserted
SELECT @EventID = EventID, @Points = Points from Event
SELECT @StudentID = StudentID from inserted

Update Event SET Attendance = Attendance + 1
WHERE @EventID = EventID

UPDATE Attendance SET Points = @Points
Where @EventID = EventID

UPDATE Student SET PointsAccumulated = PointsAccumulated + @Points
Where @StudentID = StudentID


THE SOLUTION that worked for me from HLGEM :

Update E
SET Attendance = Attendance + 1
FROM Event E
JOIN Inserted I ON i.eventid = e.eventid


UPDATE A
SET Points = e.points
FROM Attendance a
JOIN Event E ON a.EventID = e.EventID
JOIN Inserted I ON i.EventID = e.EventID


UPDATE S
SET PointsAccumulated = PointsAccumulated + e.points
FROM Student S
JOIN Inserted I ON i.studentID = s.studentID
JOIN Event E ON i.eventid = e.eventid


Lessons Learned:
In SQL Server, it is never appropriate to use a variable (other than a table variable) to grab what was changed in the table in a trigger as they do not work row-by row like triggers in some other databases
--HLGEM

Answer

This is not tested, but I think this is what you need:

CREATE TRIGGER [Trigger]
ON [dbo].[Attendance]
After insert 
AS
BEGIN



    Update E
    SET Attendance = Attendance + 1 
    FROM  Event  E
    JOIN Inserted I ON i.eventid = e.eventid


    UPDATE A 
    SET Points = e.points
    FROM Attendance  a
    JOIN Event  E ON a.eventid = e.eventid
    JOIN Inserted I ON i.eventid = e.eventid


    UPDATE S
    SET PointsAccumulated = PointsAccumulated + e.points
    FROM Students s 
    JOIN  Inserted I ON i.StudentID = e.StudentID
    JOIN Event  E ON i.eventid = e.eventid

Note that this fixes the incorrect first two updates as well. When writing SQL Server trigger, you must assume there will be more than one record in inserted or deleted. Further, you need to test the trigger by deliberately making sure that multiple records were affected by the initial insert or update. It is irresponsible to create a trigger with the assumption that only one record at a time will ever be inserted, updated or deleted. ANd if you don;t test for the behavior like that, you will have a trigger that creates a data integrity disaster as almost all tables eventually have a multiple record insert/update or deletion.