SQL Server: update values based on ID with out inserting into table

Question

ALTER TRIGGER MyTrigger 
ON persons 
AFTER INSERT
AS 
    IF EXISTS(SELECT * 
              FROM Persons t 
              INNER JOIN inserted i ON i.LastName <> t.LastName 
                                    OR i.FirstName <> t.FirstName 
                                    OR i.address <> t.address 
                                    OR i.City <> t.City 
                                    AND i.PersonID = t.PersonID) 
    BEGIN
        UPDATE p 
        SET p.LastName = i.LastName, 
            p.FirstName = i.FirstName,
            p.address = i.address,
            p.City = i.City
        FROM persons AS p
        INNER JOIN inserted i ON p.PersonID <> i.PersonID
        WHERE p.PersonID = i.PersonID 
    END

When I try to insert a new column with previous id, that row is getting updated, that's fine. But a new row is also getting inserted which I don't want

Possible duplicate of [How to upsert (update or insert) in SQL Server 2005](https://stackoverflow.com/questions/11010511/how-to-upsert-update-or-insert-in-sql-server-2005) — GolezTrol, Jan 10 '19 at 10:43
Or see [this very nice article about the various bad and the few good solutions to do upserts in SQLserver](http://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/). — GolezTrol, Jan 10 '19 at 10:46
You are looking for an `INSTEAD OF INSERT, UPDATE` not `AFTER INSERT` — Ilyes, Jan 10 '19 at 10:48
Same question has been asked earlier https://stackoverflow.com/questions/54124525/sql-server-trigger-to-update-values-based-on-primary-key/54124806#54124806 — Suraj Kumar, Jan 10 '19 at 11:05
@adams . . . I think you should learn to use `MERGE` rather than doing this in a trigger. — Gordon Linoff, Jan 10 '19 at 11:15

Ilyes · Answer 1 · 2019-01-10T12:19:25.710

I think you are looking for

CREATE TRIGGER <Trigger Name Here>
ON <Table Name Here>
INSTEAD OF INSERT, UPDATE
AS
  INSERT INTO <Table Name Here>
  SELECT I.*
  FROM INSERTED I INNER JOIN <Table Name Here> T
  ON I.ID <> T.ID; --Insert all rows with the ID does not exists in the table

  UPDATE T
  SET T.FirstName = I.FirstName,
      T.LastName = I.LastName,
      T.Address = I.Address,
      T.City = I.City
  FROM INSERTED I INNER JOIN <Table Name Here> T
  ON T.ID = I.ID --UPDATE all rows with the same ID already exists in the table
  WHERE T.FirstName <> I.FirstName
        AND --OR
        T.LastName <> I.LastName
        AND --OR
        T.Address <> I.Address
        AND --OR
        T.City <> I.City;

Replace <Trigger Name Here> by a name for your trigger.
Replace <Table Name Here> by your table name.

Gordon Linoff · Accepted Answer · 2019-01-10T12:57:29.060

I think you should learn how to use merge rather than doing this logic in a trigger. However, your question is about how to make the trigger work. What you want is a trigger that replaces the insert, rather than running afterwards.

You need to do the insert inside the trigger. This looks like:

ALTER TRIGGER MyTrigger ON persons INSTEAD OF INSERT
AS 
BEGIN
    -- Insert person ids that are new
    INSERT INTO persons
        SELECT i.*
        FROM inserted i
        WHERE NOT EXISTS (SELECT 1
                          FROM persons p
                          WHERE i.PersonID = p.PersonID
                         );

    -- update existing person ids
    UPDATE p 
        SET p.LastName = i.LastName, 
            p.FirstName = i.FirstName,
            p.address = i.address,
            p.City = i.City
        FROM persons p INNER JOIN
             inserted i
             ON p.PersonID = i.PersonID
        WHERE p.LastName <> i.LastName OR 
              p.FirstName <> i.FirstName OR
              p.address <> i.address OR
              p.City <> i.City;
END;  -- MyTrigger

I have left the comparison conditions that you have, although I don't think they are correct. You need to be very careful about NULL values. If any of the old or new values are NULL, then no UPDATE will take place.

we need `INSTEAD OF INSERT, UPDATE` not `AFTER INSERT` in the above query — adams, Jan 10 '19 at 11:59

score 0 · Answer 3 · answered Jan 10 '19 at 13:32

Why don't you try with a stored procedure like this

 create stored procedure USP_UpsertPerson
(
    @personId
    @ other inputsinputs
)


AS 
BEGIN
 IF( @personId IS NOT NULL)
BEGIN
 ----------- here Update table script With @personId
END
ELSE
BEGIN
   ----------- here Insert table script
END
END 
GO

then send @personId as null when you want to insert and send id when you want it to update

execution is faster than a query, easy to refer code and works fine

SQL Server: update values based on ID with out inserting into table

3 Answers3