Take the 2-minute tour ×
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. It's 100% free, no registration required.
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.

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
add comment

1 Answer

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.

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
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
show 5 more comments

Your Answer

 
discard

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.