justsomeguy Posted November 21, 2013 Share Posted November 21, 2013 What do you have so far? Link to comment Share on other sites More sharing options...
jones77622 Posted November 21, 2013 Author Share Posted November 21, 2013 Here is the transact code i cant seem to get it to work properly USE GO/****** Object: Trigger [dbo].[Trigger1] Script Date: 11/21/2013 10:31:57 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Name-- Create date:-- Description: -- =============================================ALTER TRIGGER [dbo].[Trigger1] ON [dbo].[order1] AFTER INSERT,UPDATEASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger hereSELECT Crust, Toppings, Sides, Crustprice + ToppingPrice + SidePrice as TotalCost FROM order1, Crusttable, ToppingsTable, SidesTableEND Link to comment Share on other sites More sharing options...
justsomeguy Posted November 21, 2013 Share Posted November 21, 2013 That SELECT statement isn't going to do much other than return all possible combinations of all records in those tables. I would use 2 triggers, one for insert and one for update. Inside a trigger SQL Server will give you tables called INSERTED and DELETED that you can use to figure out what happened. For an insert trigger, the INSERTED table will contain the new row. For an update trigger, the DELETED table will contain the old values and the INSERTED table will contain the new values. You can use those tables to figure out which record you need to calculate the cost for. So, you can create an update query to update the total cost by selecting and adding the costs of the various parts. When you have the total cost, then you can use the INSERTED table to get the ID of the record that you need to update in the orders table, and then update that record. The INSERTED table will also have the current IDs for the crust, topping, etc. Link to comment Share on other sites More sharing options...
jones77622 Posted November 21, 2013 Author Share Posted November 21, 2013 can you help me figure out what the correct syntax would be for those triggers??? Link to comment Share on other sites More sharing options...
justsomeguy Posted November 21, 2013 Share Posted November 21, 2013 Yes, I am happy to help you figure it out. I'm not going to do it for you, though. There should be plenty of research that you can do for this. I would need to do research myself in order to tell you how to write a trigger for SQL Server, so if one of us is going to do research then it might as well be you, right? https://www.google.com/search?client=opera&q=sql+server+insert+trigger&sourceid=opera&ie=UTF-8&oe=UTF-8#q=sql+server+insert+triggers Read up on triggers and feel free to post here if you have questions. Link to comment Share on other sites More sharing options...
jones77622 Posted November 21, 2013 Author Share Posted November 21, 2013 i made this function do you think this would work for me? USE GO/****** Object: UserDefinedFunction [dbo].[CalculateTotal] Script Date: 11/21/2013 17:42:13 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER FUNCTION [dbo].[CalculateTotal]()RETURNS moneyASBEGIN DECLARE @totalcost money; SELECT @totalcost = (crustprice + Sideprice + Toppingprice) FROM dbo.Crusttable, dbo.SidesTable, dbo.ToppingsTable, dbo.order1 Return @totalcost END Link to comment Share on other sites More sharing options...
justsomeguy Posted November 22, 2013 Share Posted November 22, 2013 You need to have a join condition. That join is going to join every row in those tables. You want to join the records for a single record in the order1 table, so you need to tell it which record you're working with. That means the function needs a parameter that represents the row that you're trying to calculate the total for, and then the join needs to use that ID to get the data for that order. Link to comment Share on other sites More sharing options...
jones77622 Posted November 25, 2013 Author Share Posted November 25, 2013 You need to have a join condition. That join is going to join every row in those tables. You want to join the records for a single record in the order1 table, so you need to tell it which record you're working with. That means the function needs a parameter that represents the row that you're trying to calculate the total for, and then the join needs to use that ID to get the data for that order. can you help me write the correct syntax for that join command thanks Link to comment Share on other sites More sharing options...
jones77622 Posted November 25, 2013 Author Share Posted November 25, 2013 would this be the correct syntax??USE GO/****** Object: UserDefinedFunction [dbo].[CalculateTotal] Script Date: 11/21/2013 17:42:13 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER FUNCTION [dbo].[CalculateTotal]()RETURNS moneyASBEGIN DECLARE @totalcost money; SELECT @totalcost = (crustprice + Sideprice + Toppingprice) FROM dbo.Crusttable, dbo.SidesTable, dbo.ToppingsTable, dbo.order1LEFT JOIN crusts AS c ON o.crust=c.crustIDLEFT JOIN toppings AS t ON o.toppings=t.toppingIDLEFT JOIN sides AS s ON o.sides=s.sideID Return @totalcost END Link to comment Share on other sites More sharing options...
justsomeguy Posted November 25, 2013 Share Posted November 25, 2013 No, that's not correct. You listed all 4 tables first, and then started listing joins. Look at the one I wrote in post 4. When you do a left join you list your primary table first (in your case, the order table), and then you join it with another table on a certain condition. That's what I did in post 4. Your code above also uses table aliases (o, c, t, s) without defining them first. Link to comment Share on other sites More sharing options...
jones77622 Posted November 25, 2013 Author Share Posted November 25, 2013 Thanks for all your help my instructor said it was great. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now