Home

	Expand Search

WarehouseArchitect - User's Guide

Part 3 WarehouseArchitect Model

Chapter 21: Triggers and Procedures

Generating triggers and procedures

Generating triggers and procedures

Trigger generation parameters

Generation parameters indicate the following:

• Triggers and procedures to generate
• Commands to generate in the trigger script

The availability of generation parameters depends on the target database. Unavailable parameters display in gray and are not selectable.

The tables below indicate the generation that results from the selection of generation parameters.

Selection parameters

Parameter	Resulting generation command
Triggers: All	Apply generation parameters to all triggers, both default and user-defined
Triggers: By default	Apply generation parameters to default triggers only
Triggers: User-defined	Apply generation parameters to user-defined triggers only
Tables: All tables	Apply generation parameters to triggers and procedures for all tables
Tables: Selected	Apply generation parameters to triggers and procedures only for tables selected graphically in model
Tables: List	Apply generation parameters to triggers and procedures only for tables selected from a list of tables in model
Procedures: All	Apply generation parameters to all procedures
Procedures: List	Apply generation parameters to selected procedures only

Creation parameters

Parameter	Resulting generation command
Create procedure	Create procedure
Create trigger	Create trigger
For insert	Generate insert triggers
For update	Generate update triggers
For delete	Generate delete triggers

Deletion parameters

Parameter	Resulting generation command
Drop procedure	Delete procedure
Drop trigger	Delete trigger

Database parameters

Parameter	Resulting generation command
Open database	Open existing database
Close database	Close existing database

Error message parameters

Parameter	Resulting generation command
Standard	Generate standard error messages
User-defined	Generate user-defined error messages

Script options and referential integrity options for triggers

Script options indicate the format of generation scripts for triggers and procedures.

Referential integrity options indicate whether to generate referential integrity as a trigger or as a declarative statement.

The availability of these options depends on the target database. Unavailable options display in gray and you cannot select them.

Script options

Option	Result of selection
Uppercase	Script contains all uppercase characters
Lowercase	Script contains all lowercase characters
No accent	Non-accented characters replace accented characters in script
Codes	Script refers to objects by their codes
Names	Script refers to objects by their names
Windows (ANSI)	Script uses ANSI character set
DOS (OEM)	Script uses DOS character set
Add titles	Each section of the script includes commentary in the form of titles (for example, Database Name: TUTORIAL)
Check model	Check the WAM before generating the database or script, and stop generation if an error is found

Referential integrity optionsThere are two ways to generate referential integrity in a database:

Option	Result of selection
Trigger	Generates a trigger
Declarative	Generates a declarative statement in a script

Generating a trigger creation script

PowerDesigner generates trigger and procedure scripts that you can run in your DBMS environment.

• To generate a trigger and procedure script:
  1. Select Database-->Generate Triggers and Procedures.

     The Generation Parameters dialog box appears.

     

  2. Type a filename in the File Name box.

  3. Type a directory name in the Directory box.

  4. Select generation parameters.

  5. Click the Options tab.

     The Options page appears.

     

  6. Select script options and referential integrity options.

  7. Click the Generate Script button.

Creating triggers directly in a database

PowerDesigner can generate triggers directly. To do so, you must connect to a data source via an ODBC driver.

• To create triggers directly in a database:
  1. Select Database-->Generate Triggers and Procedures.

     The Generation Parameters dialog box appears.

  2. Type a filename in the File Name box.

  3. Type a directory name in the Directory box.

  4. Select generation parameters.

  5. Click the Options tab.

     The Options page appears.

  6. Select script options and referential integrity options.

  7. Click the Create Triggers button.

     A dialog box asks you to identify a data source and connection parameters.

  8. Select a data source from the dropdown listbox.

  9. Type your user name and password.

  10. Click Connect and, if prompted by your data source, enter additional connection parameters.

      A message window shows the progress of the generation process.

Creating and generating user-defined error messages

You can create user-defined error messages. The error messages are stored in a message table which you need to create in your database. When you select trigger generation parameters, you can choose to generate an error message from this table.

In the generated trigger script, the message table is called in a SELECT command. If an error number in the script corresponds to an error number value in the table column, then the standard error message is replaced by the message defined in the table.

Creating a message table

You create a message table which stores error message information.

• To create a message table:
  1. Create a table with columns to store the following information:

     Column to store...	Description
     Error number	Number of the error message that is referenced in the trigger script
     Message text	Text of message

     
     
     

  2. Generate the table in your database.

  3. Select DatabaseExecute SQL.

     A dialog box asks you to identify a data source and connection parameters.

  4. Select a data source and fill in connection parameters.

  5. Click Connect.

     An SQL query editor box appears.

  6. Type an SQL statement to insert a message number and text in the appropriate columns. You can use the following format:

For example:

insert into ERR_MSG values (1004,'The value that you are trying to insert does not exist in the referenced table')

1. Click Execute.A message box tells you that the command has been successfully executed.
2. Click OK.You return to the SQL query box.
3. Click Cancel.

Generating a user-defined error message

You can choose to generate a user-defined error message from the trigger generation parameters box.

• To generate a user-defined error message:
  1. Select DatabaseGenerate Triggers and Procedures.

     The generation parameters dialog box appears.

  2. Click the Trigger and Procedure tab.

  3. From the Error Messages group box, select the User-Defined radio button, and click the Ellipses button.

     A dialog box appears.

  4. Enter table and column information.

     

  5. Click OK.

     You return to the generation parameters dialog box.

  6. Select other generation parameters as required.

     Click the Generate Script button.

     or

     Click the Create Triggers button.