Creating and managing records with triggers

Question

Creating and managing records with triggers

Gary Dale

Old Hand

Points: 345
More actions
July 4, 2005 at 4:36 pm

#88912

I have a Class table thus: ClassID (PK), SetCode, SubjectCode, YearCode, InstructorID and I need to select from this to get a resultset with unique values for SetCode, SubjectCode and YearCode and include an additional field which can be used as a unique index into this resultset. So, the actual resultset will have UniqueID, SetCode, SubjectCode and YearCode. Now, separately I need to select from the Class table unique values for SetCode, SubjectCode, YearCode and InstructorID but I also need to include the same UniqueID column with the same values as in the first resultset.

This is so that a UI can be built with a tree structure listing unique SetCode, SubjectCode, YearCode and then for each of these nodes a unique list of InstructorID’s can be listed when the user clicks on that node. My UI developer tells me it would save time if these resultsets could be joined using a single column rather than 3 columns, hence the need to somehow generate this UniqueID column.

I have thought about having a separate table, ClassList with a composite unique key thus: SetCode, SubjectCode, YearCode with a further field UniqueID which is an IDENTITY. I would propose maintaining the records in this table using triggers on the Class table above. This way I can always join with this table in my two selects above and include the UniqueID column in the resultsets.

I have 2 questions:

1) Is this the best way to achieve the two resultsets that can be joined at the UI using a single column?

2) What would the Insert, Update and Delete triggers look like? (hint – I’ve never written a trigger before…)

Regards

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Brenna Duncan-211418 Right there with Babe Points: 777 More actions · Answer 1

Hi,

Does your unique ID have to be an incremental integer type? If not, I would just concatenate the values of SetCode, SubjectCode and YearCode to generate a unique field.

To get your first results...

SELECT FirstUniqueID = CONVERT(varchar,SetCode) + CONVERT(varchar,SubjectCode)+CONVERT(varchar,YearCode),

SetCode,

SubjectCode,

YearCode

FROM Class

GROUP BY CONVERT(varchar,SetCode) + CONVERT(varchar,SubjectCode)+CONVERT(varchar,YearCode)

To get your second results...

SELECT SecondUniqueID = CONVERT(varchar,SetCode) + CONVERT(varchar,SubjectCode)+CONVERT(varchar,YearCode)+CONVERT(varchar,InstructorID),

FirstUniqueID = CONVERT(varchar,SetCode) + CONVERT(varchar,SubjectCode)+CONVERT(varchar,YearCode)

SetCode,

SubjectCode,

YearCode,

InstructorID

FROM Class

GROUP BY CONVERT(varchar,SetCode) + CONVERT(varchar,SubjectCode)+CONVERT(varchar,YearCode)+CONVERT(varchar,InstructorID)

Now you have the same FirstUniqueID in each result.

Does this work for you?

Gary Dale Old Hand Points: 345 More actions · Answer 2

I think this is a *much* better way of achieving the same result!

I'm going to implement this and see how the dev likes it!

Many Thanks

noeld SSC Guru Points: 96590 More actions · Answer 3

if the table is big you may as well populate a computed column and create an index on it. You will not be sorry you did

* Noel

Gary Dale Old Hand Points: 345 More actions · Answer 4

Again this makes sense. The table can end up with ~20,000 rows which will certainly benefit from an indexed computed column.

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

The benefit is only there if you have a where condition on it and that the range of values is good (didn't read the whole thread).

Just my 2 cents.

noeld SSC Guru Points: 96590 More actions · Answer 6

the index in the computed column is a way of materialize the data and not calculate it on the fly. IT will definetly speed things up even if it is not in the where clause (which it should )

* Noel