0

I am trying to set up a trigger in SQL server that when the price of an existing item on table item is changed, it inserts that entire item (row) into table item_price_history, putting the old price as old_price and the updated price as new_price. I'm pretty stumped as to how to get this to work. I keep getting errors that item_id cannot be bound, or is an invalid column, but it is a column in both tables. Here is what I have so far:

CREATE TRIGGER update_price_trg
ON item
AFTER UPDATE,INSERT
AS 
BEGIN
  DECLARE @DATE_OF_CHANGE datetime;
  SET @DATE_OF_CHANGE = GETDATE();
  INSERT INTO item_price_history,item_id,old_price,new_price,date_of_change)
  VALUES (item_id,old.price,new.price,@DATE_OF_CHANGE)
END;

closed as off-topic by LowlyDBA, mustaccio, dezso, McNets, James Anderson Apr 11 '17 at 12:25

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – LowlyDBA, mustaccio, dezso, McNets, James Anderson
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Your code has errors where you don't specify a table to insert into, and you're missing an open paren on the column list for the insert. – Erik Darling Apr 10 '17 at 16:21
3

You need to use the correct table names in the trigger -- inserted and deleted are the internal table names used for the new and old data. Also, you need to associate the two tables correctly, and handle multiple rows simultaneously. Your code will need a rewrite to do all this:

CREATE TRIGGER update_price_trg
ON item
AFTER UPDATE,INSERT
AS 
BEGIN
    INSERT INTO item_price_history(
        item_id,
        old_price,
        new_price,
        date_of_change
        )
    SELECT 
        i.item_id,
        d.price,
        i.price,
        GETDATE()
    FROM inserted i
    LEFT JOIN deleted d
        ON i.item_id = d.item_id
    WHERE i.price != ISNULL(d.price, -1)
END;
  • That is the part I was struggling with. I tried the "old.price" and got nowhere. Now I know the right way, and thank you very much for the help! – user121168 Apr 10 '17 at 16:37

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