Writing SQL Code in PowerDesigner
The objects that you create in your model display the SQL code that will be generated for them on the Preview tab of their property sheets. Certain objects provide editors on other tabs to allow you to modify the SQL statements.
- Specify a view query (see View Queries).
- Write a trigger (see Triggers (PDM)) or procedure (see Stored Procedures and Functions (PDM)).
- Define a computed column (see Creating a Computed Column).
- Insert scripts at the beginning and/or end of database or table creation (see Customizing Creation Statements).
The following tools are available in the PowerDesigner SQL editors:
|
Tool |
Description |
|---|---|
|
Add Trigger Item From Model / DBMS - [triggers and trigger templates only] Opens a dialog box to list trigger template items defined in the model or in the DBMS definition file for insertion in the trigger body (see Trigger Template Items). |
 |
Operators / Functions - List logical operators and group, number, string, date, conversion and other functions for insertion in the SQL code. Operators and functions are DBMS-specific and these lists are populated from entries in the Script\Sql\Keywords category (see Customizing and Extending PowerDesigner > DBMS Definition Files > Script/Sql Category). |
|
Macros / Variables - List PDM macros and variables for insertion in the SQL code (see Customizing and Extending PowerDesigner > DBMS Definition Files > PDM Variables and Macros). You can also use formatting variables to force values to lower-case or upper-case or to truncate the length of values characters. |
|
Edit with SQL Editor - Opens the full SQL Editor dialog which gives access to model objects for insertion in the SQL code. |
|
SQL/XML Wizard - [procedures only] Opens the SQL/XML Wizard to build a SQL/XML query from a table or a view for insertion in the SQL code (see Creating SQL/XML Queries with the Wizard). |
|
Insert SQL/XML Macro - Opens a dialog box to select a global element from an XML model open in the workspace (and which must have the SQL/XML extension file attached) for insertion in the SQL code. |
In addition to these tools, the pop-out SQL Editor lists PDM object types in the upper left pane and the available objects of the selected type in the upper right pane. Double-click an object to insert it into your code in the lower pane:
You can use the PowerDesigner Generation Template Language (GTL) and PDM variables and macros to reference objects and object properties and iterate over collections when writing SQL statements. While you can perform many tasks using the PDM variables and macros, GTL is more powerful, as it allows you to access any information about any object in the model.
In the following example, a trigger is written using the PDM variables and macros and attached to the Example table, to write the contents of any insertion to HistoryTable.
The same trigger can be written using GTL:
In each case, the trigger code to be generated is the same, and can be viewed by clicking the Preview tab:
For detailed information about working with GTL, see Customizing and Extending PowerDesigner > Customizing Generation with GTL . For lists of the available variables and macros, see Customizing and Extending PowerDesigner > DBMS Definition Files > PDM Variables and Macros.