July 3, 2006 at 6:20 am
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
July 3, 2006 at 1:30 pm
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:
view v_budget
b.intBudgetID, b.monStartingAmount,
(a.monBudgetedAmount) monBudgetedSpent,
(a.monActualAmount) monActualSpent,
tblBudget b
tblAction a
a.intBudgetID = b.intBudgetID
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 4, 2006 at 1:44 am
Thanks for the reply,
I will look into this.
CCB
July 5, 2006 at 4:14 pm
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