July 8, 2005 at 2:53 am
Hi guys, I have a trigger that sets a field in a table to the value of 3 other varchar fields concatenated together thus:
CREATE
TRIGGER TRG_Class_INS_UPD ON Class
FOR INSERT, UPDATE
NOT
FOR REPLICATION AS
UPDATE Class
SET ClassTreeKey = Inserted.AcademicYearCode + Inserted.SubjectCode + Inserted.SetCode
FROM Inserted
GO
This works for single row insertions/updates but it doesn't work for multiple row inserts/updates because all the new/updated rows get the ClassTreeKey field set to the value of the last record in INSERTED.
How can I make this trigger work for multiple row insertions/updates?
July 8, 2005 at 2:58 am
In fact it doesn't even work for single row insertions/updates because it always sets the value of the ClassTreeKey field on ALL records in the table to the value obtained from Inserted.
July 8, 2005 at 3:39 am
Don't do it!! Set up a computed column or view instead.
If you're determined to do it, then you need to join the inserted table to the Class table:-
UPDATE Class
SET ClassTreeKey = Inserted.AcademicYearCode + Inserted.SubjectCode + Inserted.SetCode
FROM Inserted inner join Class on class.pk = inserted.pk
July 8, 2005 at 4:01 am
If a Computed column is more efficient then that's what I want to do. You'll notice I'm new to SQL Server....
Could you tell me what the syntax for a computed column that concatenates 3 other varchar columns together would be?
July 8, 2005 at 4:56 am
Assuming that your table already exists, then the syntax would be:-
alter table class add ClassKey AS (AcademicYearCode + SubjectCode + SetCode)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply