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 

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

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.

Sides foreign key int

TotalCost money