June 4, 2008 at 6:54 am
I am having trouble even finding a starting point... my setup is that I have 2 tables. I have a table called Budget and a table called Debit. In the Budget table there is a column called BeginningBalance, and another called CurrentBalance. In the Debit table each time you enter a debit you put an amount in the Amount column. On the debit table there is also a lookup for Budget so you can select which budget you would like to associate that debit. I need to have the sum of the Amount column automatically subtract from the BeginningBalance column and put that new amount into the CurrentBalance column. That needs to happen on an Insert or Update on the Debit table. I only want it to subtract the debits that are associated with that particuliar budget though. So the trigger should only update the CurrentBalance row for the budget that is associated with the debit. Hope that doesnt sound too confusing. I have not written a trigger before. I have the trigger basic code that sql gives you, but I am not sure how to write it so that it makes things happen in this order. Again, thanks for any help!
June 4, 2008 at 7:04 am
Hi,
A good place to start would be to read up about the INSERTEd,DELETED tables that are created when an update/insert/delete trigger fires.
Cause once you understand those tables you will beable to get the data you are inserting into your Debit tables to then update your balance table.
I could have just written the code for you but it's always good to help someone buy pointing them in the right direction rather than just giving them the answer 🙂
If you still stuck just should.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 4, 2008 at 7:15 am
I understand to an extent how those tables work, I just having some trouble figuring out the actual code for the trigger to make it do what I would like it to based on the generic trigger code that SQL provides. If someone could provide any code I think it would help me understand how the code within the trigger would work.
June 4, 2008 at 7:27 am
HI There,
CREATE DROP TRIGGER [trg_Debit_U]
ON [Debit] FOR INSERT
AS
UPDATE
SET CurrentBalance = BeginningBalance - [Sum].Amount
FROM Budget
INNER JOIN (SELECT SUM(Amount) as [Amount],BudgetID FROM Budget GROUP BY BudgetID) [Sum]
ON .BudgetId = [Sum].BudgetId
INNER JOIN INSERTED
ON .[BudgetID] = [Sum].BudgetID
GO
HEre is a basic example for the insert trigger, you may have to change if it's possible to have multiple updates at the same time , but this should give you a good head start for all the triggers.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 4, 2008 at 7:38 am
OKAY so I updated your code to put all of my literal table/column names in where I thought they would go, and I got an error. The error is:
Msg 208, Level 16, State 4, Procedure trg_Debit_U, Line 1
Invalid object name 'New_Budgets'.
My code is: CREATE TRIGGER [trg_Debit_U]
ON [New_Budgets] FOR INSERT
AS
UPDATE
SET new_CurrentBudgetBalance = new_BeginningBudgetBalanceINT - [Sum].new_debitamount
FROM Budget
INNER JOIN (SELECT SUM(new_debitamount) as [Amount],new_BudgetsID FROM new_Budgets GROUP BY new_BudgetsID) [Sum]
ON .new_BudgetsId = [Sum].new_BudgetsId
INNER JOIN INSERTED
ON .[new_BudgetsID] = [Sum].new_BudgetsID
GO
Thanks!
June 4, 2008 at 7:44 am
That means you trying to create a trigger on a table called [New_Budget] and that doesn't exists.
Is your table not called debit?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 4, 2008 at 7:52 am
I had just given you generic names. The table that has the column that needs to be updated (the currentbudgetbalance column) is on a table called new_budgets. Full string would be dpob_mscrm.dbo.new_budgets. That also gives the same error. Am I doing something wrong?
June 4, 2008 at 8:54 am
Ok I figured out the first thing I did wrong. The tables I was calling on were not right. To simplify things I wanted to use the filtered views because there is a base table and an extension table for each one of the different tables. If I use the fitlered views they are already joined. I changed the code but now my question is where am I joining my new_budgets to my new_budgetcreditsdebits table based on that lookup?
CREATE TRIGGER [trg_Debit_U]
ON [FilteredNew_Budgets] FOR INSERT
AS
UPDATE
SET new_CurrentBudgetBalance = new_BeginningBudgetBalanceINT - [Sum].new_debitamount
FROM Filterednew_Budgets
INNER JOIN (SELECT SUM(new_debitamount) as [Amount],new_BudgetsID FROM Filterednew_Budgets GROUP BY new_BudgetsID) [Sum]
ON .new_BudgetsId = [Sum].new_BudgetsId
INNER JOIN INSERTED
ON .[new_BudgetsID] = [Sum].new_BudgetsID
GO
June 4, 2008 at 5:22 pm
Okay, well I got the trigger to fire with no errors, however it doesn't actually do anything. Here is the code.. let me know if I am missing something. Thanks!
ALTER TRIGGER [trg_Debit_U]
ON [New_BudgetsExtensionBase] FOR INSERT
AS
UPDATE
SET new_CurrentBudgetBalance = .new_BeginningBudgetBalanceINT - [sum].[Amount]
FROM new_Budgetsextensionbase
INNER JOIN (SELECT SUM(new_debitamount) as [Amount],new_fromBudgetID FROM New_BudgetCreditsDebitsExtensionBase GROUP BY new_fromBudgetID) [Sum]
ON .new_BudgetsId = [Sum].new_fromBudgetId
INNER JOIN INSERTED
ON .[new_BudgetsID] = [Sum].new_fromBudgetID
GO
June 4, 2008 at 5:46 pm
Well, if at first you don't succeed... I got it working. I had put the trigger on the wrong table's insert. Put it on the right table and BLAMO! It worked, and it feels good! Thanks for all the help! This was my first trigger...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply