Help Required with 2 Triggers

  • Hi All - SQL Server 2000

    I am struggling with a couple of triggers and would appreciate any help...

    Scenario...

    two tables

    tblBudget

    intBudgetID (Key), monStartingAmount money, monBudgetedSpent money, monBudgetedRemaining money, monActualSpent money, monActualRemaining money

    tblAction

    intActionID, intBudgetID, monBudgetedAmount, monActualAmount.

    the budget table holds an initial starting amount and some other fields which hold the totals derived from summing linked records in the actions table. - ed starting amount - sum of linked amounts budgeted and starting amount - sum of linked amounts actual...

    My trigger on the Budget table needs to fire if the user changes the starting value in the table - needs to recalc remaining budgeted and actual.

    My trigger on the Actions table needs to fire if the budget is changed or the amounts change - If the budget changes, I need to update the old budget totals and the new budget totals. Can anyone help please !!!

    One other thing, the user could blockedit more than one budget or action thereby multiple records have changed and need updating - eg update budget starting value with new value where budget ID in Range of IDs...

    My attempt at the first trigger is below, but I am not sure it will work correctly.

    Thanks for any help.

    CCB

    CREATE TRIGGER budget_change_audit ON dbo.tblBudget

    FOR INSERT, UPDATE, DELETE

    AS

    DECLARE @ACT CHAR(6)

    DECLARE @del BIT

    DECLARE @ins BIT

    SET @del = 0

    SET @ins = 0

    IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @del=1

    IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @ins = 1

    IF @ins = 1 AND @del = 1 SET @ACT = 'UPDATE'

    IF @ins = 1 AND @del = 0 SET @ACT = 'INSERT'

    IF @ins = 0 AND @del = 1 SET @ACT = 'DELETE'

    If @ACT = 'UPDATE'

      Begin

       Update t Set 

           t.monBudgetedSpent = TotalBudgetedSpent,

           t.monBudgetedRemaining = t.monStartingAmount - TotalBudgetedSpent,

           t.monActualSpent = TotalActualSpent,

           t.monActualRemaining = t.monStartingAmount - TotalActualSpent

        From tblBudget t Join

           (Select  intBudgetID,

                      Sum(monBudgetedAmount)  as TotalBudgetedSpent,

                      Sum(monActualAmount)  as TotalActualSpent

            From tblAction

            Group By intBudgetID) A on A.intBudgetID =  T.intBudgetID Join

            inserted new on new.intBudgetID = A.intBudgetID

      End

     

     

  • I know this kind of advice isn't always welcome, but you shouldn't use triggers for this. Based on my (possibly imperfect) understanding of your schema:

    1. The trigger you've provided isn't an audit trigger - it doesn't save snapshots of data at a point in time. It just maintains denormalised (summary) data, which could be calculated on the fly.

    2. You should store denormalised (redundant) data in a way that clearly distinguishes it from core (nonredundant) data, so that it is clear which is which. Then if they somehow end up being inconsistent you can be reasonably sure that it is the summary data that should be changed - and if not then you have lost some data, and it's time to be rather concerned!

    3. There is in this case no need to use a trigger or to store these denormalised values in writable fields of a table. In fact doing so is a bad idea as it causes a lot of work to maintain the data, and there's always a possibility that incorrect summary values could be written to those columns by someone at some time.

    So my approach would be to get rid of all your summary data from the budget table, leaving just the budgetID and the starting amount, and create a view to provide the data you are currently planning to maintain with triggers. That way the data can't be overwritten, doesn't need to be maintained with any kind of TSQL procedure, and is clearly separate from the transactional data in your tables. If performance is an issue, you could index the view which has the effect of materialising (persisting) the data.

    Here's the view definition:

    create

    view v_budget

    as
    select

    b.intBudgetID, b.monStartingAmount,

    sum

    (a.monBudgetedAmount) monBudgetedSpent,

    b.monStartingAmount - sum(a.monBudgetedAmount) monBudgetedRemaining,

    sum

    (a.monActualAmount) monActualSpent,

    b.monStartingAmount - sum(a.monActualAmount) monActualRemaining

    from

    tblBudget b

    join

    tblAction a

    on

    a.intBudgetID = b.intBudgetID

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • Thanks for the reply,

    I will look into this.

    CCB

  • I (embarassingly!) dropped the GROUP BY clause when pasting the code in. Still, at least it wouldn't parse at all, as is.

    group

    by b.intBudgetID, b.monStartingAmount

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

Viewing 4 posts - 1 through 3 (of 3 total)

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