1. Defines and implements stored procedures
Key Points
- Stored Procedure: Callable routine stored in the database, invoked within SQL context via CALL or ODBC/JDBC
- Three Types: Query procedures (return result sets), method procedures (modify data), function procedures (return single values)
- Implementation Methods: CREATE PROCEDURE DDL command or class method with SqlProc keyword
- Naming Convention: Projected as SchemaName_ProcedureName (e.g., MyApp.Person_FindTotal)
- SqlProc Keyword: Marks class methods as SQL-callable stored procedures
Detailed Notes
Overview
InterSystems IRIS stored procedures provide callable routines that integrate SQL with the full power of object-oriented programming.
- Implementation: Stored procedures are implemented as class methods (unlike traditional relational databases)
- Arguments: Accept input, input-output, and output arguments
- Invocation: Can be invoked using CALL statement or through JDBC/ODBC connections
Types of Stored Procedures
InterSystems IRIS supports three types of stored procedures:
- Query stored procedures: Return result sets using Query definition with SqlProc keyword and %SQLQuery type
- Ideal for data retrieval operations defined with embedded SQL
- Automatically generate Execute(), Fetch(), FetchRows(), GetInfo(), and Close() methods
- Method stored procedures: Defined as ClassMethods with the SqlProc keyword
- Perfect for operations that modify database data or perform calculations
- Uses %sqlcontext object to pass procedure context (SQLCODE, ROWCOUNT, error messages)
- Function stored procedures: Return single values
- Can be used within SQL queries like built-in functions
Implementation Methods
- CREATE PROCEDURE DDL: Generates stored procedure always projected as callable routine
- Class-based definitions: Provide more flexibility and integration with object features
- SqlName keyword: Allows customization of the SQL name
Naming and Limits
- Naming convention: Projected as SchemaName_ProcedureName (e.g., MyApp.Person_FindTotal)
- Maximum limit: 2000 user-defined methods per class
Benefits
- Encapsulation of complex business logic
- Code reuse promotion
- Enterprise integration through ODBC and JDBC connectivity
Documentation References
2. Defines and implements triggers
Key Points
- Trigger Definition: Code executed automatically in response to INSERT, UPDATE, or DELETE events
- Event Types: Single-event (one operation) or multiple-event (INSERT/UPDATE, UPDATE/DELETE, INSERT/UPDATE/DELETE)
- Timing Options: BEFORE (default) or AFTER the event occurs, controlled by Time keyword
- Execution Granularity: Once per row (default), row/object, or once per statement (Foreach keyword)
- Implementation Methods: CREATE TRIGGER DDL or Trigger definition in persistent class with Event keyword
Detailed Notes
Overview
InterSystems IRIS triggers provide event-driven automation for database operations, executing code in response to data modification events.
- Definition methods: CREATE TRIGGER SQL command or Trigger definition within persistent class
- Privileges required: %CREATE_TRIGGER and %DROP_TRIGGER administrative privileges
- Maximum limit: 200 user-defined triggers per class
Trigger Characteristics
Triggers are characterized by:
- Event keyword (required): Specifies which operations fire the trigger (INSERT, UPDATE, DELETE, or combinations)
- Time keyword (optional): Controls when trigger executes relative to event (BEFORE or AFTER; defaults to BEFORE)
- Order keyword: Manages execution sequence for multiple triggers (lower Order values fire first)
- Foreach keyword: Provides execution granularity:
- row (default): Fires once per affected row
- row/object: Fires for both SQL and object access
- statement: Fires once per SQL statement regardless of rows affected
AFTER Trigger Behavior
- Execution condition: Execute only when SQLCODE equals 0 (success) or 100 (no row found)
- Not executed: When SQLCODE is negative (failures)
Trigger Recursion
Recursion behavior depends on trigger type:
- row and row/object triggers: Can recurse; programmer must handle to avoid FRAMESTACK errors
- AFTER statement triggers: Protected from recursion by the system
- BEFORE statement triggers: Not protected; require programmer management
Trigger and Callback Correspondence
Triggers correspond to callback methods:
- BEFORE/AFTER INSERT: Map to %OnBeforeSave()/%OnAfterSave()
- BEFORE/AFTER UPDATE: Map to same save callbacks
- BEFORE/AFTER DELETE: Map to %OnDelete()/%OnAfterDelete()
- Timing difference: Callbacks execute before object locks; triggers execute after field validation
Documentation References
3. Understands language options for business logic (SQL, ObjectScript, Python)
Key Points
- ObjectScript: Native language for triggers and stored procedures, uses {fieldname} syntax and special variables
- Python: Supported for triggers with Language = python keyword, uses trigger object with attributes and getfield() method
- SQL: Can be used within CREATE TRIGGER for trigger code (converted to ObjectScript internally)
- Embedded SQL: Supported within ObjectScript stored procedures and triggers using &sql() syntax
- Language Selection: Based on developer expertise, integration requirements, and organizational standards
Detailed Notes
Overview
InterSystems IRIS provides flexible language options for implementing business logic, supporting ObjectScript, Python, and SQL.
- Purpose: Accommodate diverse developer skill sets and integration scenarios
- Data access: All three languages can access the same underlying data and objects
ObjectScript for Triggers
ObjectScript is the native language providing full system integration and optimal performance.
Field access syntax:
- {fieldname}: Current values
- {fieldname*N}: New values in UPDATE/DELETE
- {fieldname*O}: Old values in INSERT/UPDATE
- {fieldname*C}: Detect changes
System variables:
- %ok: Success flag
- %msg: Error message
- %oper: Operation type
Method invocation:
- Use `##class(classname).Methodname()` syntax
- Cannot use instance method syntax (`..Methodname()`) since no object is open
- All trigger variables are public (not scoped to a procedure) except system variables
Python for Triggers
Python support enables trigger implementation using familiar Python syntax.
- Requirement: `Language = python` keyword in trigger definition
- Limitation: Only supports `Foreach = row/object` granularity
Trigger object attributes:
- trigger.type: row/object or row
- trigger.operation: BEFORE, UPDATE, DELETE
- trigger.time: before or after
- trigger.ok: Corresponds to ObjectScript's %ok
- trigger.msg: Corresponds to ObjectScript's %msg
Field access:
- `trigger.getfield(fieldName)`: Retrieves field values
- Optional boolean parameter: new=1 (default) or old=0
- Change detection: Compare `trigger.getfield(fieldname,0)` and `trigger.getfield(fieldname,1)` (no direct equivalent to {fieldname*C})
SQL in Triggers
- CREATE TRIGGER: SQL can be used for trigger action code
- Conversion: InterSystems IRIS converts SQL to ObjectScript in generated class definition
- Embedded SQL: Use &sql() syntax in ObjectScript for query capabilities
- Host variables: Access with colon prefixes (:variable)
Language Selection Guidelines
- ObjectScript: IRIS specialists, deep system integration
- Python: Data scientists, machine learning pipelines
- SQL: Database administrators, straightforward data operations
Documentation References
Exam Preparation Summary
Critical Concepts to Master:
- Stored Procedure Types: Understand differences between query, method, and function stored procedures
- Trigger Event Types: Know single-event vs. multiple-event triggers and BEFORE vs. AFTER timing
- Foreach Granularity: Distinguish between row, row/object, and statement-level trigger execution
- Language Syntax: Master ObjectScript {fieldname} syntax and Python trigger.getfield() method
- SqlProc Keyword: Remember to mark class methods with SqlProc to expose as stored procedures
Common Exam Scenarios:
- Creating a stored procedure that returns a result set using Query with SqlProc keyword
- Implementing a BEFORE INSERT trigger to validate or modify data before storage
- Choosing between ObjectScript and Python for trigger implementation based on requirements
- Using embedded SQL within a stored procedure to query or modify data
- Understanding when AFTER triggers execute (SQLCODE=0 or 100, not negative values)
Hands-On Practice Recommendations:
- Create query, method, and function stored procedures in Management Portal
- Define triggers using both CREATE TRIGGER and class-based approaches
- Implement triggers in ObjectScript and Python to compare syntax
- Test trigger execution order using the Order keyword with multiple triggers
- Practice using {fieldname*N}, {fieldname*O}, and {fieldname*C} syntax in ObjectScript triggers
- Call stored procedures using CALL statement and use stored functions in SELECT queries
- Examine generated methods (Execute, Fetch, Close) for query stored procedures