CODEWITHSUNDEEP
sql-servertriggers
Shubh
Shubh

Reputation: 595

How to set trigger based on update query in SQL Server?

I am trying to set trigger whenever phone is updated in table1 and then do operation like check phone from table2 and if present, pick up the employee code from table2 and then update employee code in table1.

My code for trigger:

CREATE TRIGGER UPDATED_Contact_Trigger 
ON table1 
AFTER UPDATE
AS
    DECLARE @tuid bigint;
    DECLARE @phone varchar(15);
    DECLARE @employee_code varchar(10);

    IF (UPDATE (phone)) --Phone is the Column Name
    BEGIN
        SELECT @employee_code = i.emp_code 
        FROM table2 i (nolock) 
        WHERE i.MOBILE_NO = @phone 

        UPDATE table1 
        SET client_code = @employee_code
        WHERE tuid= @tuid
    END;

This trigger is set, but there is no update on table1 even if I update a contact which is present in table2

Upvotes: 0

Views: 457

Answers (1)

marc_s
marc_s

Reputation: 754408

You're not even looking at the Inserted or Deleted pseudo tables in your code - how do you want to know what rows have been updated??

Inserted is a pseudo table that contains all updated rows - and it contains the new values after the update operation, while Deleted contains the same rows, but with the old values before the update.

You need to do something like this:

  • join your Table1 to Inserted to get the rows that were updated (since you didn't show your table structure, I cannot know what your primary key on Table1 is - you need to use that to join to Inserted)
  • add a join to your Table2 and pick those rows that have been updated, and limit those to the ones that have been updated in the Phone column (by comparing the Inserted.Phone value to Deleted.Phone)

Try this code:

CREATE TRIGGER UPDATED_Contact_Trigger 
ON table1 
AFTER UPDATE
AS
BEGIN
    -- update your base table
    UPDATE t1
    -- set the client_code to the employee_code from Table2
    SET client_code = t2.employee_code
    FROM dbo.Table1 t1
    -- join to "Inserted" to know what rows were updated - use the primary key
    INNER JOIN Inserted i ON t1.(primarykey) = i.(primarykey)
    -- join to "Deleted" to check if the "Phone" has been updated (between Deleted and Inserted)
    INNER JOIN Deleted d ON i.(primarykey) = d.(primarykey)
    -- join to "Table2" to be able to fetch the employee_code
    INNER JOIN dbo.Table2 t2 ON t2.mobile_no = t1.phone
    -- ensure the "Phone" has changed, between the old values (Deleted) and new values (Inserted)
    WHERE i.Phone <> d.Phone;
END;

Upvotes: 2

Related Questions