CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
adams
adams

Reputation: 301

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

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

Upvotes: 0

Views: 1342

Answers (3)

Hungry_Developer
Hungry_Developer

Reputation: 61

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

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.

Upvotes: 2

Related Questions