Chapter 5 Building Triggers and Procedures

Defining a generation order for stored procedures

You can use extended dependencies to define an order in the generation of stored procedures.

Extended dependencies are free links between PDM objects. These links help to make object relationships clearer between model objects. Usually, these links are not interpreted and checked by PowerDesigner as they are meant to be used for documentation purposes only. However, if you assign the <<DBCreateAfter>> stereotype to an extended dependency between stored procedures, it will be analyzed during generation.

The procedure from which you start the extended dependency is the dependent procedure and the procedure at the other end of the link is the influent procedure. The influent procedure will be generated before the dependent procedure.

Circular extended dependencies

If you create a reflexive and/or circular set of extended dependencies with the <<DBCreateAfter>> stereotype, an error message appears during the check model. If you choose to ignore this error, the stored procedures will be generated in alphabetical order, without taking into account the generation order, which could cause errors in the creation of stored procedures in the database.

Example

A publisher may decide to sell certain books at a reduced rate (15%) when a customer's order is above 10 000$.

In this model, one stored procedure GENERAL CHECK globally verifies orders: check books availability, check the order amount, check if discount rate is calculated, and applies discount rate to order bill. During the execution of this stored procedure, the procedure DISCOUNT CALC is called to calculate the 15% discount rate. It is important to generate GENERAL CHECK before DISCOUNT CALC; you can define an extended dependency to set the generation order between these two objects. To do so, you have to open the property sheet of the dependent stored procedure, click the Extended Dependencies tab and create an extended dependency with the <<DBCreateAfter>> stereotype with the influent stored procedure.

To define a generation order for stored procedures:

1. Select Model→Procedures to display the List of Stored Procedures.
2. Select a dependent stored procedure in the list and click the Properties tool.
   
   The property sheet of the stored procedure appears.
3. Click the Extended Dependencies tab to display the Extended Dependencies page.
4. Click the Add Objects tool.
5. Select the Procedure tab in the Add Object selection dialog box.
6. Select the influent stored procedure check box and click OK.
7. Select the <<DBCreateAfter>> stereotype in the Stereotype dropdown listbox.
8. Click OK.
   
   The influent stored procedure appears in the Influent Object column of the list of extended dependencies of the dependent stored procedure.

For more information on extended dependencies, see Using Extended Dependencies in the General Features Guide.

Copyright (C) 2005. Sybase Inc. All rights reserved.