SQL Server trigger for update and insert

[jennypretty]

Hello,

I created a TRIGGER to update tblStudents every time there is an INSERT or UPDATE for any record on this table from ASP form online.

The TRIGGER works to run for the whole table. But I just want to update it on any record it just gets inserted or updated.

How do I fix it to get it worked?

Thanks.

tblStudents
Student_ID Name Department
---------- ------ ------
1 A A1
2 B B7
3 C C6

tblDepartment
Department_ID Department Student_ID
-------------------------------------
001 A1 1
002 B7 2
003 C6 3
004 D7 4

CREATE TRIGGER Update_Department
ON tblStudents
AFTER INSERT OR UPDATE
AS BEGIN
UPDATE tblStudents
SET Department = tblDepartment.Department
FROM tblSTudents
JOIN tblDepartment ON
tblStudents.STudent_ID = tblDepartment.STudent_ID
END

[nanda.t]

Hi jenny,

I presume, you want to update only on the inserted/updated records.
In that case, use where clause.

To get the current inserted or updated values, use the select statement with inserted in the end.
example,

DECLARE @V_ID INT
SELECT @V_ID = STUDENT_ID FROM INSERTED

--the @V_ID variable holds the value of inserted or updated row and use it in the where --clause to update the values in other tables or any retriving operations.