Unable to copy Some coloumns using Trigger

Question

I have a two database tables,that database tables connect with 1:1 relationship.Using a trigger i need to copy some coloumns data to Log table.(When New Insert or Update Happens)

Error (1) Invalid column name 'ItemTbl_ItemId'.
(2) Invalid column name 'Price'
(3)Column name or number of supplied values does not match table definition.

First Table has

Table Name - ItemTbl

ItemId, ItemName, ItemPrice,Comments, Brand_BrandId

Second Table

Table Name - ItemLog

ItemLogId, ItemTbl_ItemId,ItemName,ItemPrice,ModifiedDate

My Trigger

CREATE TRIGGER [dbo].[ItemHistoryTrigger]
ON [dbo].[ItemTbl]
FOR INSERT,UPDATE
AS
BEGIN
 IF EXISTS(SELECT ItemId,ItemName,ItemPrice FROM INSERTED)
  BEGIN
   INSERT INTO [dbo].[ItemLog]
    SELECT ItemTbl_ItemId,ItemName,ItemPrice FROM  INSERTED;
  END
END

I Just wanted to copy ItemName & ItemPrice from the first table to second table using trigger.

Filipe Silva · Accepted Answer · 2013-11-18T16:22:47.810

1

You have no column named ItemTbl_ItemId in the table you are creating the trigger (ItemTbl). Try this:

CREATE TRIGGER [dbo].[ItemHistoryTrigger]
ON [dbo].[ItemTbl]
FOR INSERT,UPDATE
AS
BEGIN
 IF EXISTS(SELECT ItemId,ItemName,ItemPrice FROM INSERTED)
  BEGIN
   INSERT INTO [dbo].[ItemLog](ItemTbl_ItemId,ItemName,ItemPrice)
    SELECT ItemId,ItemName,ItemPrice FROM  INSERTED;
  END
END

And this assumes that the ItemLog's columns ItemLogId and ModifiedDate are auto_incremented and have a default value respectively.

edited Nov 18 '13 at 16:22

answered Nov 18 '13 at 16:16

Filipe Silva

21,189
5
53
68

ItemId & Item_ItemId are Foreginkey relationship.then it needs to copy it from the trigger? – TechGuy Nov 18 '13 at 16:22
Of course. If you are doing an insert you have to include all the columns that you want to insert. See my last remark to see if you have to include also the id and modifieddate or not. – Filipe Silva Nov 18 '13 at 16:25
i think thats the error.Onething.Is this tables are need to Foreginkey relationship ? or just remove the relationship ? – TechGuy Nov 18 '13 at 16:28
Beeing this a log table, i don't think you need the foreign key, since a log table is to handle history data, not really an actual part of the system anymore. See [this question](http://stackoverflow.com/q/5794417/1385896) to a good discussion on that. – Filipe Silva Nov 18 '13 at 16:33

Unable to copy Some coloumns using Trigger

1 Answers1