Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Nov 2005
    Posts
    225
    Thanks
    2
    Thanked 0 Times in 0 Posts

    SQL Server trigger for update and insert

    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

  • #2
    New Coder
    Join Date
    Jul 2011
    Location
    Chennai, India
    Posts
    23
    Thanks
    0
    Thanked 5 Times in 5 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •