Triggers are blocks of procedural logic that are invoked automatically either before or after data manipulation events. These events include INSERT, UPDATE, DELETE, and any of the form status verbs. Triggers are useful for many things: data validation ("You can't change the ship data field after the item was already delivered."), maintaining data integrity ("Every order must have a contact record created for it.") and logging. ("We always keep a history record for every change on this table.")
Like procedures, triggers are implemented in a unique way on CodaServer. The definition of the trigger is very much like a SQL stored procedure, but the body of the trigger is written in a JVM-based scripting language called Groovy.
There are a few "gotchas" to this integration. Some of the most important ones:
- Coda is case-insensitive and Groovy is not. Within the trigger, Coda attempts to camel-capitalize type names and variable names; a type called email_address becomes EmailAddress, and a variable called user_id would become userId.
- Access to the database is provided through an object called "database." Only queries and DML statements can be run from within a trigger.
- All commits must be explicit; there is no autocommit.
- There is another object called "utility" that provides access to many handy utility functions such as Vector-to-Coda Array conversions.
Since triggers are automatically called before or after data manipulation events, they make available two objects that represent the previous and next state of the table or form being updated. These are called (ironically enough) "next" and "prev" withing the trigger itself, and each contain camelCapped public fields for each column or field on the table or form, respectfully. Manipulating next and prev directly does nothing. Any changes to the data must be done through the methods made available in the "database" object.
Create or Replace Trigger
create_trigger := [ CREATE | REPLACE ] TRIGGER ON [ <table_spec> | <form_spec> ] AS <trigger_body> END TRIGGER table_spec := TABLE [ <application_name> . ]? <table_name> [ BEFORE | AFTER ] [ INSERT | UPDATE | DELETE ] form_spec := FORM [ <application_name> . ]? <form_name> [ BEFORE | AFTER ] [ <form_status_verb> | UPDATE ]
Creates or replaces a trigger. Creating will NOT overwrite an existing trigger if one is already in use, and replace will. The application name is only required if one is not currently set in the session.
The trigger body contains Groovy code. Documentation for the language can be found here. Once saved, the trigger definition is used to create a method within a Java class representing the trigger. The body of the trigger becomes the body of the method. Relevant objects like the "database" object and the "prev" and "next" objects are initialized before the trigger body is run.
If it is ever necessary to cancel the DML statement that launched the trigger, this can be accomplished by first calling the rollback() method on the "database" object and then throwing an Exception. The error message provided in the exception will be what CodaServer returns to the client.
drop_trigger := DROP TRIGGER ON [ <table_spec> | <form_spec> ]
table_spec := TABLE [ <application_name> . ]? <table_name>
[ BEFORE | AFTER ] [ INSERT | UPDATE | DELETE ]
form_spec := FORM [ <application_name> . ]? <form_name>
[ BEFORE | AFTER ] [ <form_status_verb> | UPDATE ]
Permanently drops a trigger.