Skip to main content

Adding SQL Triggers and Foreign Keys to a Class

Every persistent Caché class is automatically projected as an SQL table. This chapter discusses how you can use Studio with those parts of a class definition that control its SQL behavior.

SQL Aliases

You can give classes as well as most class members an alternate name for use by SQL. This is useful because:

  • There is a long list of SQL reserved words that cannot be used as identifiers.

  • Caché does not support the underscore character in class or class member names.

To specify an SQL table name for a class, view the Class information in the Class Inspector and edit the value for the SqlTableName keyword.

To specify an SQL name for a class member, select the desired property in the Class Inspector and edit the value for its appropriate SQL name keyword (such as SqlFieldName for properties and SqlName for indices).

SQL Stored Procedures

An SQL Stored procedure is a Caché method or class query than can be invoked from an ODBC or JDBC client as a stored procedure.

Caché supports two styles of SQL stored procedure: those based on class queries and that return a result set; and those based on class methods and that do not return a result set.

Query-Based Stored Procedure

To create an SQL stored procedure that returns a result set, add a query definition to a class definition and then set the query's SqlProc keyword to true. Do this as follows:

  1. Create a query in a class definition using the New Query wizard with Class > Add > Query.

  2. Using the Class Inspector, set the value of the query definition keyword SqlProc to True.

You should end up with something similar to:

Class Employee Extends %Persistent 
{

/// A class query listing employees by name.
Query ListEmployees() As %SQLQuery(CONTAINID = "1") [SqlProc]
{
    SELECT ID,Name
    FROM Employee
    ORDER BY Name
}
}

You can invoke this stored procedure from an ODBC or JDBC client using a CALL statement:

CALL Employee_ListEmployees()

Following this call, the ODBC or JDBC application can fetch the contents of the result set returned by the class query.

Note that you can use this same technique with query definitions that are based on custom-written code; you are not limited to defining stored procedures solely based on SQL statements.

Creating Method-Based Stored Procedure

To create an SQL stored procedure that does not return a result set, add a class method to a class definition and then set the method's keyword SqlProc to True. Do this as follows:

  1. Create a class method in a class definition using the New Method wizard.

  2. Using the Class Inspector, set the value of method's keyword SqlProc to True.

You should end up with something similar to:

Class Employee Extends %Persistent
{

  ClassMethod Authenticate(
        ctx As %SQLProcContext,
        name As %String,
        ByRef approval As %Integer
        ) [SqlProc]
  {
    // ...
    Quit
  }

}

Note that the first argument of a method used as an SQL stored procedure is an instance of a %SQLProcContextOpens in a new tab object. For more information, see the chapter “Defining and Using Stored Procedures” in Using Caché SQL.

You can invoke this stored procedure from an ODBC client using a CALL statement:

CALL Employee_Authenticate('Elvis')

To invoke this stored procedure from a JDBC client, you can use the following code:

prepareCall("{? = call Employee_Authenticate(?)}")

Adding SQL Triggers to a Class

An SQL trigger is code that is fired by the SQL Engine in response to certain events.

Note that SQL triggers are not fired during object persistence (unless you are using %CacheSQLStorageOpens in a new tab storage class).

You can add an SQL trigger to a class definition in two ways:

  • Editing the class definition using the Class Editor.

  • Using the New SQL Trigger wizard

To add an SQL trigger using the Class Editor, position the cursor on a blank line in the Class Editor and enter a trigger declaration:

Class MyApp.Company Extends %Persistent 
{

/// This trigger updates the Log table for every insert
Trigger LogEvent [ Event = INSERT ]
{
    // ...
}

}

New SQL Trigger Wizard

You can use the New Trigger wizard to create a new SQL trigger. You can open the New SQL Trigger wizard using Class > Add > SQL Trigger. Alternatively, right-click in the Class Inspector and select Add SQL Trigger .

The New SQL Trigger wizard prompts you for information. Select Finish at any time (default values are provided for any information you have not specified).

Name and Description Page

The New SQL Trigger wizard prompts you for the following information (you can later modify any of these values):

Trigger Name

(required) Name of the trigger. This name must be a valid trigger name and must not conflict with the name of a previously defined trigger.

Description

(optional) Description of the new trigger. This description is used when the class' documentation is displayed in the online class library documentation.

Trigger Event Page

The New SQL Trigger wizard asks you to indicate when you want the new trigger to be fired by specifying the event and time for the trigger.

Event Type

This specifies which SQL event fires the trigger. The choices are Insert (when a new row is inserted), Update (when a row is updated), or Delete (when a row is deleted).

Event Time

This specifies when the trigger is fired. The choices are Before or After the event occurs.

Trigger Code

The New SQL Trigger wizard lets you enter the source code for the trigger if you want.

Results of Running the New SQL Trigger Wizard

After you finish using the New SQL Trigger wizard, the Class Editor window is updated to include text for the new trigger definition.

If you want to edit this trigger you can do this using either the Class Editor or the Class Inspector.

Adding New SQL Foreign Keys to a Class

An SQL foreign key defines an integrity constraint between one or more fields in a table and a key (unique index) in another table.

Object applications typically do not use foreign keys; they instead use relationships which offer better object-based navigation. Relationships automatically impose integrity constraints (for both SQL and object access) that are equivalent to manually defining foreign key definitions.

Typically you use foreign key definitions in applications that are originally purely relational in nature.

You can add an SQL foreign key to a class definition in two ways:

  • Editing the class definition using the Class Editor.

  • Using the New SQL Foreign Key wizard

To add an SQL foreign key using the Class Editor, position the cursor on a blank line in the Class Editor and enter a foreign key declaration:

Class MyApp.Company Extends %Persistent 
{

Property State As %String;

ForeignKey StateFKey(State) References StateTable(StateKey);

}

New SQL Foreign Key Wizard

Open the New SQL Foreign Key wizard using the Class > Add > Foreign Key. Alternatively you can right-click the Class Inspector and selecting Add Foreign Key or select the New Foreign Key icon, generated description: newforeignkeyicon, from the toolbar.

The New SQL Foreign Key wizard prompts you for information. When you have filled in the required information, select Finish (default values are provided for any information you have not specified).

Name and Description Page

The New SQL Foreign Key wizard prompts you for the following information (you can later modify any of these values):

Foreign Key Name

(required) Name of the foreign key. This name must be a valid foreign key name and must not conflict with the name of previously defined foreign key.

For a general discussion on names see the chapter “Caché Classes ” in Using Caché Objects.

Description

(optional) Description of the new foreign key. This description is used when the class' documentation is displayed in the online class library documentation.

A description can include HTML formatting tags. For more details, see “Using HTML Markup in Class Documentation” in the chapter “Defining and Compiling Classes” in Using Caché Objects.

Attributes Page

The second page asks you to select one or more properties of the class that you want constrained by the foreign key.

Key Construction Page

The third page asks you to select both the class and a key (unique index) in that class that specify the values used to constrain the foreign key properties.

Results of Running the New SQL Foreign Key Wizard

After running the New SQL Foreign Key wizard, the Class Editor window is updated to include the new foreign key definition.

If you want to make further modifications to this foreign key you can do this using either the Class Editor or the Class Inspector.

FeedbackOpens in a new tab