Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2006
    Posts
    39

    Unanswered: Creating a sp procedure and a trigger help

    There are 2 tables: OrderID and Order_Details
    PHP Code:
    CREATE TABLE Orders 
       
    (OrderID                 int
            Constraint Order_ID_pk primary key
      
    CustomerID              nchar (5)
      , 
    EmployeeID              int
      
    OrderDate               datetime
      
    RequiredDate            datetime
      
    ShippedDate             datetime
      
    ShipVia                 int
      
    Freight                 money
      
    ShipName                nvarchar (40)
      , 
    ShipAddress             nvarchar (60)
      , 
    ShipCity                nvarchar (15)
      , 
    ShipRegion              nvarchar (15)
      , 
    ShipPostalCode          nvarchar (10)
      , 
    ShipCountry             nvarchar (15)  
       ) 

    CREATE TABLE Order_Details 
       
    (OrderID                 int      NOT NULL
      
    ProductID               int      NOT NULL
      
    UnitPrice               money    NOT NULL
      
    Quantity                smallint NOT NULL
      
    Discount                real     NOT NULL
      
    constraint Order_Details_PK
            Primary key 
    (OrderIDProductID
       ) 
    Create a Transact SQL procedure, customer_activity, that would, for a given CustomerID, return the number of orders that customer has made, average amount of all the customer orders, and the maximum customer’s order. The CustomerID should be the stored procedure’s input parameter. The stored procedure should use the view customer_orders.

    what kind of code to create this? it says i need to use a view customer_orders, which i made:
    create view customer_orders as
    select orders.orderID, customerID, sum(order_details.orderID) as orderamount
    from orders, order_details where orders.orderID=order_details.orderID
    group by orders.orderID, customerID

    but i don't know how to do it.


    Next: the trigger:
    Write a trigger that would, for any order entered (inserted), print the order amount as well as the customer’s average and maximum order so far, by using the view and the stored procedures created in this lab.

    please and thank you

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    looks like homework. it seems they are testing your knowledge of aggregate functions.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Change your view to use a JOIN between the two tables, instead of relating them in your WHERE clause.
    Perform your aggregations in the view (look up the COUNT(), AVG(), and MAX() functions in Books Online).
    Select from the view in your stored procedure based upon a supplied ID.

    This:
    Quote Originally Posted by Chanyeehon
    Write a trigger that would, for any order entered (inserted), print the order amount as well as the customer’s average and maximum order so far, by using the view and the stored procedures created in this lab.
    ...is just plain stupid. Triggers are not for printing.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2006
    Posts
    39
    Quote Originally Posted by blindman

    This:

    ...is just plain stupid. Triggers are not for printing.
    i think printing means, printing the output, so as in screen.

  5. #5
    Join Date
    Oct 2006
    Posts
    39
    i did the procedure
    now i will try the trigger

    ...
    im stuck

    CREATE TRIGGER ordercheck
    ON ( i need to insert my view and the procedure that i created) how does that work.
    AFTER insert
    as
    select count(orderID),avg(orderID), max(OrderID) from .........
    Last edited by Chanyeehon; 03-01-08 at 22:52.

  6. #6
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    i think printing means, printing the output, so as in screen.
    Still plain Stupid !!!! you would never use a trigger for this either.

    This is an example of knowing what to do is far more important than knowing just the syntax of how to do it.

    Well I s'pose we all gotta start somewhere

    Definately homework (hopefully not university) - tehe

    GW

    PS. don't be afraid to lavish blindman with adulation if his post helped!!
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  7. #7
    Join Date
    Oct 2006
    Posts
    39
    Quote Originally Posted by GWilliy
    Still plain Stupid !!!! you would never use a trigger for this either.

    This is an example of knowing what to do is far more important than knowing just the syntax of how to do it.

    Well I s'pose we all gotta start somewhere

    Definately homework (hopefully not university) - tehe

    GW

    PS. don't be afraid to lavish blindman with adulation if his post helped!!
    yes this is some small lab assignment that i have to do.

  8. #8
    Join Date
    Oct 2006
    Posts
    39
    how do i use procedure and table within a trigger?

  9. #9
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    EXECute it or SELECT from it exactly as you would in any other normal TSQL Code

    Are you asking how do you return the data - to the screen - or the printer - lol.?
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Chanyeehon
    i think printing means, printing the output, so as in screen.
    Also stupid. Maybe even more stupid than printing to paper. Stupiderererer, even.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    maybe instead of calling the stranger names, something I prefer to only do to the regulars here, we should inquire into what the poster is trying to accomplish. Maybe the OP should explain the end result in as simple and non technical terms as possible.
    Last edited by Thrasymachus; 03-02-08 at 06:36.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm not calling the OP stupid. I'm calling whoever gave him that assignment stupid. And I'm right, too.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •