Create a Scripted Procedure
Contents
Go Up to Creating and Editing SQL Procedures
- In the Data Model Explorer, expand the physical Main Model, right-click the Procedures node, and then click New Procedure.
- Complete the Procedure SQL Editor and then click Validate to ensure the SQL is valid.
- Click OK to create the procedure and exit the editor.
- Once you have created the auxiliary table, you can edit it by right-clicking the auxiliary table you want to change, and then selecting Procedure.
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
SQL page/tab
Enter the SQL for the procedure.
Description page/tab
Enter or edit a definition for the procedure. If the target database supports it, ER/Studio Data Architect adds this definition as a comment when generating SQL code.
Permissions page/tab
Sets access roles and user permissions for the procedure. Keep in mind that the more specific permissions are, the more time you may have to spend maintaining and updating them. The roles and users must be previously assigned to appear in the Procedure Editor. For more information, see Granting and Revoking Permissions to Modify Database Objects, Creating and Editing Database Roles, and Creating and Editing Database Users.
- Against InterBase platforms, no Permissions page/tab is available.
Attachment Bindings tab
Bind an external piece of information, or attachment to the procedure. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you have moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect opens the Value Override Editor or a list depending on the attachment datatype. Attachments are created in the Attachments folder of the Data Dictionary. For more information, see Attaching External Documents to the Data Model.