March 14, 2002 at 5:01 pm
I created insert/update and delete triggers
that run a procedure with 3 parameeters for
each insert/update or delete in one table to
be reflected in another.
I used a select to get the parameters for the procedure for individual updates, however, when data is added to the table
through a separate procedure (multiple inserts) the insert trigger either is not
fired or it is not handling the incoming
data correctly.
How do I trigger bulk inserts to be handled
individually? Are there multiple rows in the
'inserted' table?
Note that I need to capture parameters for
the called procedure.
Make sense?
March 14, 2002 at 5:16 pm
Yes, there can be multiple rows in the inserted table. Triggers fire per transaction, not per row. ALL triggers should be coded assuming that multiple rows will be affected. The preference is set based, but sometimes there is no way to do it other than a cursor.
Andy
March 15, 2002 at 10:02 am
If you want to call this procedure multiple times, then you can use a table datatype to pass in data, but I hate to do that in a trigger, especially if you are row processing.
What is this for? An audit of some type?
Perhaps we have another solution if you post more code.
Steve Jones
March 18, 2002 at 9:08 am
The tables involved are:
1. Purchase Order items
2. SubItems (additional costs such as transport etc.)
3. Tagged equipment (id's such as ser no.for large items)
4. Cost centers these charges are to be applied to.
5. Distribution of these costs.
All costs for item and subitem get rolled up,
then distributed across either the cost centers or the tagged items. Because the tagged items are pieces of equipment, they can only be charged to one account. Because the item on a PO is normally a commodity item (bulk), it can be applied to many locations/cost centres in a major project.
All this is working now through a procedure, affter the fact, but I am now trying to keep
all of this in synch dynamically as changes are made to (4.) ie on insert/update etc.
The triggers were working when I assumed only one record affected, but when I tried to use a cursor for multiples, they didn't seem to work.
March 18, 2002 at 5:16 pm
I seem to have the INSERT/UPDATE trigger working with a cursor to handle multiple
updates when there is no DELETE trigger, however, the DELETE trigger seems to be getting in the way when entering changes.
//======== procedure being called
CREATE PROCEDURE UpdatePODistribution @PONum char(20), @PORev int , @POItem int
AS
DECLARE @TagCount int
DECLARE @ItemQuantity int
DECLARE @SubItemTotal decimal (9,2)
DECLARE @Description varchar(255)
-- RJ 14-Feb-2002 Eliminate any items that were previously exported for this PO/Rev
-- Want to eliminate this as soon as testing is complete as we dont
-- want to do this after someone has processed the extracted data.
DELETE FROM PODistribution
WHERE PONum = @PONum
AND PORev = @PORev
AND Item = @POItem
-- It is necessary to distribute subitem costs
-- between tags or allocate across multiple cost areas determined by
-- the distribution from MC. Tag information must be keyed manually
DECLARE crControl CURSOR FAST_FORWARD FOR
SELECT
(SELECT SUM(COALESCE(Quantity,1) * COALESCE(Cost,0))
FROM POSubItem
WHERE PONum = @PONum
AND PORev = @PORev
AND Item = @POItem
) SubItemCost,
(SELECT SUM(COALESCE(Quantity,0))
FROM POItemExp
WHERE PONum = @PONum
AND PORev = @PORev
AND Item = @POItem
AND NOT (Tag IS NULL OR Tag = '')) TagCount,
(SELECT SUM(COALESCE(Quantity,0))
FROM POItemCost
WHERE PONum = @PONum
AND PORev = @PORev
AND Item = @POItem
AND NOT (Tag IS NULL OR Tag = '')) ItemQuantity
OPEN crControl
FETCH NEXT FROM crControl
INTO @SubItemTotal, @TagCount, @ItemQuantity
CLOSE crControl
DEALLOCATE crControl
DECLARE crDescription CURSOR FAST_FORWARD FOR
SELECT COALESCE(RTrim(ItemDescription),'')
FROM POITEM
WHERE PONum = @PONum
AND PORev = @PORev
AND ITEM = @POItem
OPEN crDescription
FETCH NEXT FROM crDescription
INTO @Description
CLOSE crDescription
DEALLOCATE crDescription
IF NOT (@TagCount IS NULL OR @TagCount = 0)
BEGIN
-- insert tag information
INSERT INTO PODistribution (PONum,PORev,Item,Num,Tag,Quantity,UnitCost,TotalCost,CostCode,Major,Minor,Area,CostType,AFE, Explanation, Description )
SELECT PONum, PORev,Item, Num, Tag, Quantity, UnitCost, TotalCost,CostCode,Major,Minor,Area,CostType,AFE, 'Direct tag costs', @Description
FROM POItemCost
WHERE PONum = @PONum
AND PORev = @PORev
AND ITEM = @POItem
AND NOT (Tag IS NULL OR Tag = '')
-- insert subitem costs apportioned to the tags
IF NOT (@SubItemTotal = 0.00 )
BEGIN
INSERT INTO PODistribution (PONum,PORev,Item,Num,Tag,Quantity,UnitCost,TotalCost,CostCode,Major,Minor,Area,CostType,AFE, Explanation ,Description)
SELECT PONum, PORev,Item, Num, Tag, 0, 0, Quantity * @SubItemTotal / @TagCount ,CostCode,Major,Minor,Area,CostType,AFE, 'Allocation of subitem costs',@Description
FROM POItemCost
WHERE PONum = @PONum
AND PORev = @PORev
AND ITEM = @POItem
AND NOT (Tag IS NULL OR Tag = '')
END
END
ELSE
BEGIN
-- insert an item that has no tags
INSERT INTO PODistribution (PONum,PORev,Item,Num,Quantity,UnitCost,TotalCost,CostCode,Major,Minor,Area,CostType,AFE, Explanation, Description )
SELECT PONum, PORev,Item, Num, Quantity, UnitCost, (Quantity * UnitCost ) TotalCost,CostCode,Major,Minor,Area,CostType,AFE, 'Direct costs',@Description
FROM POItemCost
WHERE PONum = @PONum
AND PORev = @PORev
AND Item = @POItem
IF NOT (@SubItemTotal = 0.00)
BEGIN
IF NOT (@ItemQuantity = 0.00 )
BEGIN
INSERT INTO PODistribution (PONum,PORev,Item,Num,Quantity,UnitCost,TotalCost,CostCode,Major,Minor,Area,CostType,AFE, Explanation, Description )
SELECT PONum, PORev,Item, Num, 0, 0, Quantity * @SubItemTotal / @ItemQuantity ,CostCode,Major,Minor,Area,CostType,AFE, 'Allocation of subitem costs', @Description
FROM POItemCost
WHERE PONum = @PONum
AND PORev = @PORev
AND ITEM = @POItem
END
ELSE
BEGIN
INSERT INTO PODistribution (PONum,PORev,Item,Num,Quantity,UnitCost,TotalCost,CostCode,Major,Minor,Area,CostType,AFE, Explanation, Description )
SELECT PONum, PORev,Item, Num, 0, 0, @SubItemTotal,CostCode,Major,Minor,Area,CostType,AFE, 'Allocation of subitem costs', @Description
FROM POItemCost
WHERE PONum = @PONum
AND PORev = @PORev
AND ITEM = @POItem
END
END
-- if breakdown to multiple areas, then need to distribute
-- subitem costs across these areas. do not do this if already
-- done according to tags or values will be doubled up.
END
//============== trigger
CREATE TRIGGER POItemCostChanged ON [POItemCost]
FOR INSERT, UPDATE
AS
DECLARE @PONum char (20)
DECLARE @PORev int
DECLARE @POItem int
DECLARE crItem CURSOR FAST_FORWARD FOR
SELECT DISTINCT inserted.PONum,
inserted.PORev,
inserted.Item
FROM inserted
OPEN crItem
FETCH NEXT FROM crItem
INTO @PONum, @PORev, @POItem
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC UpdatePODistribution @PONum, @PORev, @POItem
FETCH NEXT FROM crItem
INTO @PONum, @PORev, @POItem
END
CLOSE crItem
DEALLOCATE crItem
//======== delete trigger same with deleted. table
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy