Running procedure from trigger

  • 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?

  • 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

  • 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

    steve@dkranch.net

  • 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.

  • 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