Need Help Tring to make a database work properly

[justsomeguy]

What do you have so far?

[jones77622]

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 

[justsomeguy]

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.

[jones77622]

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

[justsomeguy]

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.

[jones77622]

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

[justsomeguy]

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.

[jones77622]

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

[jones77622]

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 

[justsomeguy]

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.

[jones77622]

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