Sql-server – SQL Server trigger (update or insert with inner join problem)

sql servertrigger

ALTER TRIGGER [dbo].[killertrigg]
   ON  [dbo].[tbl_pvporderview]
   AFTER UPDATE
AS 
IF (UPDATE([Kill]))
BEGIN
  SET NOCOUNT ON;

  INSERT  killer(serial,[Kill])
    SELECT i.serial,i.[Kill] FROM inserted AS i
    INNER JOIN deleted AS d
    ON i.serial = d.serial 
    AND i.[Kill] <> d.[Kill] AND i.[Kill] > 0;
END

However how would I go if I would want it to update if the serial already exist in the killer table. Could do update first and if it returns rowcount 0/null then do insert but I got stuck WHERE – since I can't use i.serial = d.serial to compare there.

Best Answer

How about changing the trigger to a BEFORE UPDATE? That way you can check the values before any action has taken place.

EDIT

OK so I think I understand what you are trying to do. How about this:

ALTER TRIGGER [dbo].[killertrigg]
   ON  [dbo].[tbl_pvporderview]
   AFTER UPDATE
AS 
BEGIN
  SET NOCOUNT ON;
  Merge killer as k
  Using (select serial, [kill] from inserted) as I (serial, [kill])
  On (k.serial = I.serial)
  When matched then
   Update set [kill] = I.[kill]
   When not matched then
   Insert (serial, [kill]) values (I.serial, I.[kill]);
END
Related Question