Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
ALTER TRIGGER [dbo].[killertrigg]
   ON  [dbo].[tbl_pvporderview]
IF (UPDATE([Kill]))

  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;

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.

share|improve this question
Can you not use the INSERTED table? – Mr.Brownstone Aug 24 '12 at 16:07
inserted and deleted are sql special tables not real tables in my db (stores deleted and insert values before query is actually run or something like that). – Agony Aug 24 '12 at 16:19
Update what to what? – Aaron Bertrand Aug 24 '12 at 17:14
up vote 3 down vote accepted

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


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

ALTER TRIGGER [dbo].[killertrigg]
   ON  [dbo].[tbl_pvporderview]
  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]);
share|improve this answer
then i woudl still have to compare the values it wants to enter with existign ones, so my trigger would run only if specific column gets updated IF (UPDATE([Kill])) – Agony Aug 24 '12 at 16:34
Could you give more detail in your question please? I really don't understand what you are trying to do that cannot be accomplished by using a combination of the INSERTED/DELETED tables or by using a BEFORE and a AFTER trigger. – Mr.Brownstone Aug 24 '12 at 16:40
the problem with my trigger above is that it will only insert - however i need a "exist" check first and update if a serial in the table its writing to, exists already (the serial in killer table is a primary key). – Agony Aug 24 '12 at 16:54
The trigger works - however due to the primary key, i cant insert duplicates so i need to update if it already exists. But im not sure how to set up the UPDATE [Kill] WHERE serial = xxxx - i cant use i.serial here (a multi-part error) – Agony Aug 24 '12 at 16:59
Does the one above work? – Mr.Brownstone Aug 24 '12 at 17:00

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.