Greetings,
I'm trying to properly build an after-insert trigger with set based queries (instead of cursors), and I'm having a little trouble getting my head around this.
This may be difficult to explain, so I'll try to be clear. Please ask if you need more information.
The table: attributesCategories contains attributes, based on categories. This table simply contains an attribute number (attributeNo) and a category number (categoryNo).
Sample:
attributeNo categoryNo ----------- ---------- 1 632 2 642 3 721
The table: equipmentCategories contains the categories associated with a piece of equipment. Columns are equipmentNo and categoryNo.
Sample:
equipmentNo categoryNo ----------- ---------- 1252 632 1252 633 2113 642 3431 721
Equipment may be found in one or more categories.
My challenge is, after inserting a new category for a piece of equipment (adding a record to equipmentCategories), I need my after-insert trigger to add all attributes for this new category to the equipment (in the equipmentAttributes table). One catch is, the equipment may already have some of the attributes that were also found in another category that this equipment is associated with, so we don't want duplicates.
The attributes can come from the current category number, but they can also come from any "parent" category (any attributes in a higher level category apply to all subcategories). The "tree" or lineage of the category is stored in the categories table:
Sample:
categoryNo lineage ---------- -------------------------- 2 /0/630/600/601/238/ 3 /0/630/600/601/225/414/ 4 /0/630/600/601/228/ 5 /0/630/600/601/225/433/
So, I have a "split" function I use to return a table of all the lineage category numbers (it splits, based on the '/' delimiter). The lineage does not include the current category number, so I have to add that. Thus, to return all the attributes that I would need to add for sample category number 642 and sample equipmentNo 2, my query would be:
SELECT* FROM attributesCategories WHERE categoryNo IN (
SELECT * from core.dbo.fnSplit(
(SELECT lineage + '642/' FROM categories WHERE categoryNo = 642),'/')
) AND attributeNo NOT IN
(SELECT attributeNo FROM equipmentAttributes WHERE equipmentNo = 2)
[sampleQuery 1]
The results of this query would be something like:
attributeNo categoryNo ----------- ---------- 1 632 2 632 3 632 4 632 5 642 6 642
This is the basic query I need to pull all required attributes for a given equipmentNo and categoryNo.
The trouble I'm having is with joining this to the "inserted" table to have a set-based query do the insert in my trigger.
The best I've come up with is something like this (please contain your laughter, all you SQL experts!!)
INSERT INTO equipmentAttributes (equipmentNo, attributeNo)
SELECT i.equipmentNo, a.attributeNo
FROM inserted i INNER JOIN (
SELECT * FROM attributesCategories WHERE categoryNo IN
(SELECT * FROM core.dbo.fnSplit(
(SELECT lineage + CAST(i.categoryNo AS VARCHAR) + '/' FROM
categories where categoryNo = i.categoryNo),'/'))
and attributeNo NOT IN (SELECT attributeNo FROM equipmentAttributes WHERE
equipmentNo = i.equipmentNo)
) a on a.categoryNo = i.categoryNo
[sampleQuery 2]
Right then.
Ok, so the above, of course, doesn't work, as I cannot use "i.categoryNo" in my subquery. Errors I get are:
Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "i.categoryNo" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "i.categoryNo" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "i.equipmentNo" could not be bound.
Somehow I need to insert the results (multiple records) from [sampleQuery 1] above, for each inserted row, into the equipmentAttributes table.
Any suggestions? I'm learning, but these kinds of queries still give me a headache... :)
Thanks much!
Bill
