Hi,

I have an unique key calculated field in a table that derives its value from a combination of values from other fields in other tables and is setup to update its value inside an INSERT trigger as follows:

UPDATE A SET Mailcode = B.MailTypeCode+C.PackageCode+RIGHT(STR(YEAR(D.MailDate)),2)
            +E.MonthCode+F.Code+G.Code+H.Code+D.PanelCode
FROM Mailcode A JOIN inserted A2 ON A.MailcodeId = A2.MailcodeId
	JOIN MailType B ON B.MailTypeId=A2.MailTypeId
	JOIN PackageCode C ON C.PackageCodeId = A2.PackageCodeId
	JOIN Panel D ON D.PanelId = A2.PanelId
	JOIN Month E ON E.MonthId = MONTH(D.MailDate)
	JOIN Position F ON F.PosId = A2.Pos7
	JOIN Position G ON G.PosId = A2.Pos8
	JOIN Position H ON H.PosId = A2.Pos9

It works fine when I'm saving just one record at a time but I get an unique index key violation error when I write an INSERT query such as:

INSERT Mailcode (MailPlanId, MailTypeID, PackageCodeId, Pos7, Pos8, Pos9, PanelID, CategoryId)
SELECT A.MailPlanId, 3, A.PackageCodeId, A.Pos7, A.Pos8, A.Pos9, A.PanelID, A.CategoryId
FROM Mailcode A
		JOIN MailType B ON B.MailTypeId=A.MailTypeId
		JOIN PackageCode C ON C.PackageCodeId = A.PackageCodeId
		JOIN Panel D ON D.PanelId = A.PanelId
		JOIN Month E ON E.MonthId = MONTH(D.MailDate)
		JOIN Position F ON F.PosId = A.Pos7
		JOIN Position G ON G.PosId = A.Pos8
		JOIN Position H ON H.PosId = A.Pos9
WHERE A.MailPlanId = 1
		AND A.MailTypeId = 2
		AND A.PackageCodeId = 3
		AND A.PanelId = 4
		AND A.CategoryId = 5

As you can see, all I'm trying to do is copy a set of existing records but with different value for one of the field. I know the trigger is only called once on the complete data set not on each record inserted which is the issue but I don't know how to go about fixing it.

TIA.