Database objects are stored procedures, triggers, and views within Aptify that may be linked to an entity to provide additional functionality. The database objects distributed with the original system or in an upgrade are referred to as core database objects. Core database objects should not be modified because they contain code that affects the main functionality of the system. In addition, core database objects are often included in upgrades and, if the client alters the core object, it is possible that those client alterations could be overwritten when the core object is replaced during an upgrade.

If a modification to the functionality of a core database object is required, a copy of the core object should be created with a unique name. The modified functionality may then be implemented in the copied object. Only database objects based on membership or customer-management entities should be modified. Again, the existing object itself should not be modified; developers should create a copy of the object using a different name if different functionality is required.

When saving a newly created database object, Aptify checks the Database Objects service for existing records with the same name. If one exists, the system returns an error message requiring that the new database object be given a unique name.

Naming Conventions

When a database object is created, following a specified naming convention helps organize the objects as well as clarify the functionality of the object. While organizations may define naming conventions that suit their business practices, the Aptify-generated naming conventions are explained in the sections below. In addition, suggested client-specific naming conventions are listed in those cases where the organization has not defined their own. It should be noted the naming conventions described below are Aptify's recommendations and the actual name of the database object does not in any way drive the functionality of the system.

Stored Procedures

When an entity is created in Aptify, four stored procedures are automatically created if the Generate SQL Table option on the Entities form is selected. These four stored procedures include functionality for retrieving, creating, updating and deleting data.

For example, when the Lists entity was created, four stored procedures were generated: spGetList, spCreateList, spUpdateList, and spDeleteList. This naming convention includes the sp prefix to indicate that it is a stored procedure, the functionality (for example, Get, Create, Update, and Delete), and the name of the table containing the data manipulated by the stored procedure.

If a client-specific stored procedure is needed, it is recommended the naming convention include the sp prefix followed by an abbreviated description of the function of the stored procedure including the name of the table containing the data affected by the stored procedure.

For example, if a client needs a stored procedure to retrieve Companies records based upon a supplied State abbreviation, the stored procedure could be named spGetCompaniesByState. Creating a new stored procedure rather than modifying an existing stored procedure helps ensure that the client-specific business requirements are not overwritten during an upgrade because the stored procedure will not be replaced with one in the upgrade package.

Triggers

An organization should not modify any of Aptify's core triggers as these changes can be overwritten by future service packs. In the event that you need to modify the logic of a core trigger, Aptify recommends that you create a new trigger (following the naming convention described below) with the LAST modifier that contains only the revised trigger logic. This would apply the core Aptify trigger first and then the client-specific logic. See the Specifying First and Last Triggers and sp_settriggerorder topics in the SQL Server 2008 Books Online for more information:

• https://technet.microsoft.com/en-us/library/ms189568(v=sql.100).aspx
• https://technet.microsoft.com/en-us/library/ms186762(v=sql.100).aspx
   

Also, Aptify recommends against using triggers wherever possible since they are back-end updates that bypass the standard business logic, including Record History.

The naming convention for a trigger might include the tr prefix to indicate that it is a trigger, the functionality, and the singular name of the table containing the data manipulated by these triggers. The name of the trigger might also include a brief version of the functionality in order to make the name unique.

For example, if an organization wants to create a trigger that updates a Persons record each time a Companies record is updated, the name could be trUpdateCompanyPersonInfo. As with all database objects, it is important not to modify any core triggers because they may be included in an upgrade package and the modified functionality could be overwritten during the upgrade process.

Views

The naming convention for a view generated by Aptify includes the vw prefix to indicate that it is a view, and the plural form of the table name being queried. For example, the system-generated view for the Companies entity is named vwCompanies. If an organization wants to add functionality to the view, a new view with a unique name should be created. Aptify recommends the naming convention for the new view include the vw prefix, the plural form of the table name being queried, and optionally, a brief version of the functionality in order to clarify the purpose of the view and to ensure that the name is unique.

For example, if a view is necessary to present all Companies records that have recently changed addresses, the name, vwCompaniesRecentlyRelocated, follows the recommended naming convention.