Jump to content

Need Help Tring to make a database work properly


jones77622
 Share

Recommended Posts

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

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

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

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

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

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

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...