Creating Database Objects

The Database Objects service provides database administrators with a tool for creating, viewing, and editing database-related objects (such as views, stored procedures, and triggers). In providing this service, Aptify allows administrators to modify database objects directly using the Aptify graphical user interface.

Note that an administrator should not modify or create database objects through SQL Server Enterprise Manager since Aptify maintains additional metadata on each database object in the system.

Some database objects are created automatically by the system. For example, when a new Entities record is defined, the view for the entity and the four default stored procedures (Get, Create, Update, Delete) are created automatically. Additional database objects can be created as organization-specific business logic requires.

Follow these steps to create a new Database Object:

1. Open a new record from the Database Objects service.  
2. Enter the Name of the database object.
   
   • Aptify recommends that you use the common naming convention for each type of database object. For example, all stored procedures should begin with the prefix sp, and all views should begin with the prefix vw. 
3. Enter a Description of the database object.
4. Select the SQL Server database where the object is to reside in the DB field.
5. Select the database Object Type:
   
   • View: Views are virtual tables and are often used to filter data or join data from multiple tables.
   • Stored Procedure: Stored procedures are groups of transact-SQL statements combined into one execution plan. These can take input data, return data, and implement business logic as needed.
   • Trigger: A trigger is a special type of stored procedure that is designed to run automatically whenever an Update, Insert, or Delete command is run against the specified table or view. First, Last, and Instead Of triggers are supported in Aptify.
   • Function: User-defined functions consist of transact-SQL statements that can accept input parameters and return data.
6. Enter the SQL statements for the database object in the SQL tab.
   
   • To format the SQL statement, click the Format button.
     • If you paste unformatted text from another source into the record, you can click the Format button to update the formatting.

   • The Recompile button recompiles the SQL statement if the code is updated.

     If your SQL expression uses any Outer Join operations, use the ANSI Outer Join operators (LEFT OUTER JOIN or RIGHT OUTER JOIN) rather than the non-ANSI operators (= or =). SQL Server does not support SQL statements that use non-ANSI outer join operators.  

7. Click the Grant SQL tab and specify user permissions.
   
   • Users must have permissions to the database objects in order for them to execute.

   • Permissions for the four default stored procedures and view for entities are granted automatically by the system when entity permissions are configured. Permissions for additional database objects created to handle organization-specific business logic should be granted manually through the Grant SQL tab of the Database Objects record.
     
     

     If SQL Is Generated and/or Grant SQL Is Generated appear to the right of the DB and Type fields, Aptify automatically generated this database object. This text will not appear for new database objects that you create manually or for database objects that you manually update.

8. If desired, click the Required DB Objects tab and specify any related database objects that you reference in this new Database Objects record. When creating an entity or data pack that includes your new object, the system will also pack the objects you specify in this tab.
9. Save the Database Objects record.