Reusing Procedural Logic
Go Up to Working with the Data Dictionary
Contents
The Data Dictionary supports the following types of procedural logic and enables you to organize them by database platform:
- Reusable Triggers: A special type of stored procedure that automatically executes when data modification operations such as INSERT, UPDATE, or DELETE occur. Because triggers are customizable and fire automatically, they are often used to maintain referential integrity in a database.
- Reusable Procedures: Templated procedures written in BASIC code. These procedures can be applied to any table, since they use the table's context in the code.
- Libraries: Lets you compartmentalize blocks of code used to generate SQL for Reusable Triggers and Reusable Procedures. This lets you reuse blocks of code. Reusable triggers or procedure code can call library functions.
The editor that launches when creating new procedural logic is the Sax Basic editor, which is compatible with Microsoft Visual Basic.
Topics covered in this section are:
Create and Edit Reusable Triggers
- Select a table.
- The SQL in the procedure will be validated in context of the table selected and the database platform. If no table is selected, the SQL cannot be properly validated.
- In the Data Dictionary, expand the Reusable Procedural Logic node and then expand the Reusable Triggers node.
- Right-click the node for the appropriate database platform and then click New Trigger.
- Define the trigger as required and then click OK to complete the editor.
- Tip: Once created, you can edit the trigger by double-clicking its object on the Data Dictionary tab to launch the editor.
The following describe options that require additional explanation:
- In order for code to be generated for procedural logic such as triggers and procedures, the results of the logic must be stored in a variable called resultstring. For example, in the Northwind.dm1 sample model, the Data Dictionary lists a reusable trigger, SYSUPDATE, for the Oracle platform. The code for this trigger includes the following statement, which is required for code to be generated for the trigger:
resultstring = trigBody
BASIC tab Includes a text editor as well as all the editing and formatting tools that are available on the ER/Studio Data Architect Macro Editor. You can enter trigger code or you can use the Import button to import it. You can use Visual BASIC and/or AI to create the code for the reusable trigger.
- DBMS: If you change the DBMS from the one you clicked when you opened the editor, you can use the Copy Dictionary Object functionality to move the Trigger to the correct DBMS node.
- Select Libraries: Click to select a previously defined library to add to the trigger code. When using library functions, the library file needs to be selected by selecting this option.
Create and Edit Reusable Procedures
- Select a table.
- The SQL in the procedure will be validated in context of the table selected and the database platform. If no table is selected, the SQL cannot be properly validated.
- In the Data Dictionary, expand the Reusable Procedural Logic folder and then expand the Reusable Procedures folder.
- Right-click the folder for the appropriate database platform and then click New Procedure.
- Define the reusable procedure as required and then click OK to complete the editor.
- Tip:* Once created, you can edit the procedure by double-clicking its object on the Data Dictionary tab to launch the editor.
The following describe options that require additional explanation:
- In order for code to be generated for procedural logic such as triggers and procedures, the results of the logic must be stored in a variable called resultstring. For example, in the Northwind.dm1 sample model, the Data Dictionary lists a reusable trigger, SYSUPDATE, for the Oracle platform. The code for this trigger includes the following statement, which is required for code to be generated for the trigger:
resultstring = trigBody
BASIC tab Includes a text editor as well as all the editing and formatting tools that are available on the ER/Studio Data Architect Macro Editor. You can enter procedure code or you can use the Import button to import it. You can use Visual BASIC and/or AI to create the code for the reusable procedure.
- DBMS: If you change the DBMS from the one you clicked when you opened the editor, you can use the Copy Dictionary Object functionality to move the procedure to the correct DBMS node.
- Select Libraries: Click to select a previously defined library to add to the procedure code.
Create and Edit Libraries
- In the Data Dictionary, right-click the Libraries folder and then click New Library.
- Define the library as required and then click OK to complete the editor.
The following describe options that require additional explanation:
BASIC tab Includes a text editor as well as all the editing and formatting tools that are available on the ER/Studio Data Architect Macro Editor. You can enter library code or you can use the Import button to import it. You can use Visual BASIC and/or AI to create the code for the library.
Library Example
'This function generates the header for a templated insert
'procedure. Input is entity name that is used to generate
'procedure name. Platform is Oracle.
Function insertprocheader (ent As Entity) As String
Dim result As String
Dim attr As AttributeObj
'add create statement for the procedure with naming convention
result = "CREATE OR REPLACE PROCEDURE P" & ent.TableName & "INSERT" & vbCrLf
result = result & "(" & vbCrLf
'add parameter list for insert statement
'loop in actual sequence order
For i = 1 To ent.Attributes.Count
For Each attr In ent.Attributes
If attr.SequenceNumber = i Then
'make parameter line for column
result = result & "V" & attr.ColumnName & vbTab & vbTab & vbTab
result = result & "IN "
result = result & attr.Datatype & "," & vbCrLf
End If
Next attr
Next i
'trim last comma of the parameter list
result = Left(result, Len(result) - 3)
'add last closed parantheses
result = result & ")" & vbCrLf
result = result & "AS" & vbCrLf & "BEGIN" & vbCrLf
'return header
insertprocheader = result
End Function
Call to Library Example
This sub main routine demonstrates how to call a library function.
Note: The library needs to be added to the reusable trigger or stored procedure.
Sub Main
Dim procstring As String
procstring = insertprocheader(CurrEntity)
'call other library functions for body of procedure
'call other library functions for end of procedure
'check procedure text with a message box
'disable when generating DDL
MsgBox(procstring)
'output procedure string variable to DDL wizard
resultstring = procstring
End Sub