Skip to main content

Adding SQL Triggers and Foreign Keys to a Class

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

Also see Using Triggers and Using Foreign Keys. For details, see Trigger Definitions and Foreign Key Definitions in the Class Definition Reference.

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.

  • InterSystems IRIS 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 indexes).

SQL Stored Procedures

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

InterSystems IRIS supports two styles of SQL stored procedure:

  • Procedures based on class queries.

  • Procedure 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. Select Class > Add > Query to open the New Query Wizard.

  2. Complete the wizard to add a new query to the class definition.

  3. 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 Defining and Using Stored Procedures.

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 %Storage.SQL 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 such as:

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 > New 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.

See Rules and Guidelines for Identifiers.

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 is updated to include text for the new trigger definition.

You can edit the trigger 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 such as:

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 select Add > New Foreign Key. You can also click the New Foreign Key button square containing rows. The square has a yellow starburst 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.

See Rules and Guidelines for Identifiers.

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 may include HTML formatting tags. See Creating Class Documentation in Defining and Using Classes.

Attributes Page

The second page of the wizard 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 of the wizard 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 is updated to include the new foreign key definition.

You can make changes to this foreign key using either the Class Editor or the Class Inspector.

FeedbackOpens in a new tab