jones77622

Thanks for all your help my instructor said it was great.

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

can you help me write the correct syntax for that join command thanks

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

can you help me figure out what the correct syntax would be for those triggers???

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

i am having trouble making a trigger for the order table can you help me with the syntax please?

i would like to execute this function every time i enter a new row in the order table. my guess is that i need to create a function then create a trigger to activate that function every time i enter a new row into the order table. am i correct?

can i use the same idea in creating a function??

well i have a table that doesn't have anything in it yet then i have a table that i am using for testing purposes

using the same idea

what would the correct syntax be for an insert into statement??

what about using a multistatement table-valued function ??

i have been looking for a few weeks on how to do this i am working on this for a project for my SQL class as extra credit to boost my grade.

SQL management studio i believe it is SQL server 2008 r2

one of my friends said i should try a multistatement table-valued functions but am not sure how to go about that.

i am trying to get it to calculate total cost automatically on the order table as i am entering the product in the order table.

through the edit top 200 rows

any ideas???

How do i get it to show up on the order table when i view it in select top 1000 rows on the order table ??? It shows the proper price in the query but it does not show it on the ordertable query select top 1000 rows This is the query that worked so far SELECT OrderID, Crust, Toppings, Sides, Crustprice + ToppingPrice + SidePrice as OrderPrice FROM order1 AS o LEFT JOIN Crusttable AS c ON crust=crustID LEFT JOIN ToppingsTable AS t ON Toppings=toppingID LEFT JOIN sidestable AS s ON sides=sideID

Sorry about that i thought i put all of the order table in. I am starting with only one topping and one side but was thinking about adding more after i figure out how to properly add up cost.

I have a database of 5 tables. 1st table is my customer table. 2nd is my crust table. 3rd is my toppings table. 4th is my sides table. 5th is my order table. My crust, toppings and sides table all have a price column and I am trying to get them to add together on my order table total cost column. What function would be the best to use to accomplish getting the crust price toppings price and sides price to add up on the order table Total cost column? Here is my table schema: Crust TableCrustID Primary KeyCrustnameCrustsizeCrustprice moneyToppings TableToppingID Primary KeyToppingNameToppingPrice moneySides TableSideID Primary KeySideNameSidePortionSidePrice moneyCustomer TableCustomerID Primary KeyFnameLnameStreetCityStateZipPhonenumberemailaddress Order TableOrderId Primary Key INTCustomer Foreign Key INTCrust Foreign Key INTToppings Foreign Key INT Sides foreign key int TotalCost money