Hey!

I have a problem using "for insert, update" -style trigger. It works nicely with table updates but inserts do not seem to work. Here is a sample of the trigger:


CREATE TRIGGER customer_trigger ON dbo.CUSTOMER FOR INSERT, UPDATE AS BEGIN DECLARE @col_name varchar(30),@old_value varchar(80),@new_value varchar(80) SELECT @col_name = 'NAME',@old_value = del.NAME,@new_value = ins.NAME FROM deleted del, inserted ins IF @new_value<>@old_value BEGIN INSERT INTO HISTORY_TABLE(FIELD,OLDVALUE,NEWVALUE,TIME) SELECT @col_name,@old_value,@new_value,getDate() FROM deleted del, inserted ins END END


I haven't worked with triggers much so I appreciate all the help I can get.

It is unclear what you are trying to do. I do see a proble in that you are selecting from 2 tables but there is no JOIN condition.

Ex:
 FROM deleted del, inserted ins

These tables are not joined in any way.

Please explain in more detail what you are trying to accomplish.

Jim

I'm trying to create a trigger which would insert a row to the table HISTORY_TABLE every time a specified row in the table CUSTOMER is updated or if a new row is inserted. A New row is inserted to HISTORY_TABLE for every updated field in CUSTOMER table, so if I insert a new row to CUSTOMER, it would add a new row to HISTORY_TABLE for every field that had a value added to it.

Again, it works when I update a row in CUSTOMER table, but it doesn't
work when I insert a new row to the table.

I hope this makes more sense.


Thanks,

Janne

I think the problem is your comparing the new value to the old. On an insert there is no old value to compare to. Thus nothing to compare to or null. Thus your logic works on update but not on insert. I am also rusty on this. To trouble shoot I suggest you write some test code and insert 100% of the new values into the test table (don't do a compare, thus no "if"). First write just the new or inserted value, then try to also write the "old" value. Then if you do one insert and one update you should see the differenc in how sql treats things in your test table.