This chapter discusses database triggers: how to create them, execute them and drop them.
A trigger defines an SQL statement that is automatically executed before, after, or instead of a specified data manipulation operation on a particular table or view.
A trigger can either be a statement trigger which means that the trigger is executed once for a data manipulation statement, or a row trigger which means that the trigger is executed once for each row affected by the data manipulation statement causing the trigger.
The execution of the SQL statement can be made conditional on the evaluation of a search condition.
The SQL statement in the trigger definition is typically a compound SQL statement, thus allowing a number of SQL statements to be executed by the trigger. The compound SQL statement must be defined as ATOMIC. Thus, the body of a trigger is similar to the body of a routine and the same language constructs may be used within it. In this code it is possible to refer to the data that was affected by the data manipulation statement which caused the trigger to be executed.
In a statement trigger the affected data is stored in temporary tables. The data in these tables can only be read and not modified. Depending on which event that causes the trigger there can be one or two tables. For delete there is an old table containing all rows that are deleted. For insert there is a new table containing all inserted rows. An update trigger will have both an old and a new table. The old table contains the rows as they were before the update took place while the new table contains the rows as they are after the update has taken place.
In order to be able to refer to these temporary tables, the trigger definition must contain a referencing clause which identifies which names that are used when referencing these tables in any DML statement within the trigger body. The old and new table will have the same layout as the table on which the trigger is defined. An example can be seen below.
A row trigger, depending on the event, will have old row and new row variables that can be referred to in the trigger code. These row variables will have fields with the same name and data type as the columns in the table on which the trigger is defined.
A delete trigger will have an old row variable that contains the deleted row. An insert will have a new row variable that contains the inserted data. An update trigger will have both an old row and a new row variable. Individual data items in these variables are referenced by using dot notation. (See the row trigger example below where o.country_code is used to refer to data for the deleted row.) The old row variable is read only but the new row variable can be modified in a before trigger (except that columns defined as large objects are read only in this version of Mimer SQL.)
A trigger is created by using the CREATE TRIGGER statement, see the Mimer SQL Reference Manual, CREATE TRIGGER.
Example of statement trigger:
CREATE TRIGGER products_after_insert AFTER INSERT ON products
REFERENCING NEW TABLE AS pdt
FOR EACH STATEMENT
BEGIN ATOMIC
-- Force the update trigger to fire
UPDATE products
SET product_search = DEFAULT
WHERE product_id IN (SELECT product_id
FROM pdt);
END -- of trigger products_after_insert
A trigger is created on a named table or view and the trigger must be created in the schema to which the table or view belongs.
The trigger name must follow the rules for naming private database objects, see the Mimer SQL Reference Manual, Naming Objects, and the name must be unique within the schema in which the trigger is created.
You can create any number of triggers on a named table, each of which may have the same trigger time, see Trigger Time, and trigger event, see Trigger Event, specified.
If two or more triggers exist on the same table with the same trigger time and trigger event, they will be executed in the same order as they were created.
Example of row trigger:
create trigger checkExists before delete on currencies
referencing old row as o for each row
if exists (select *
from countries
where countries.currency_code = o.currency_code) then
signal sqlstate 'UE123'
set message_text = 'Depending row in countries exists';
end if
When creating a trigger using the BSQL tool it is convenient to enclose the code as
@
create trigger setversion before udpate on document_versions
referencing new row as new_version old row as old_version
begin atomic
if old_version.version = new_version.version then
set new_version.version = new_version.version + 1;
end if;
end
@
thus avoiding conflicts when using ; as a delimiter in the trigger definition.
The trigger time specifies when, in relation to the execution of the triggering data manipulation statement, the trigger is executed.
The possible values for the trigger time for a base table are:
This specifies that the trigger will be executed prior to the execution of the triggering data manipulation statement. The table name must specify a base table which is located in a databank with TRANS or LOG option.
This specifies that the trigger will be executed following the execution of the triggering data manipulation statement.
For a view it is possible to create instead of triggers. This specifies that the trigger will execute when the triggering data manipulation statement would normally be executed. In this case the triggering data manipulation statement itself has no direct effect, it only causes the trigger to execute.
It is possible to have both row and statement triggers for the same event on a base table. The logic for invoking statement and row triggers for a base table can schematically be seen as:
--
-- invoke before statement triggers
--
call before_statement_trigger_1;
...
call before_statement_trigger_n;
get_data:
loop
--
-- get rows affected by data manipulation statement
--
if not found then
leave get_data;
end if;
--
-- invoke before row triggers
--
call before_row_trigger_1;
...
call before_row_trigger_n;
--
-- save data to old/new table if used
--
--
-- do actual operation
--
delete/insert/update;
--
-- invoke after row triggers (currently not supported)
--
call after_row_trigger_1;
...
call after_row_trigger_n;
end loop;
--
-- invoke after statement triggers
--
call after_statement_trigger_1;
...
call after_statement_trigger_n;
Note that this schema includes after row triggers even though these are not supported in this version of Mimer SQL.
Analogously with base tables, if you have both statement and row trigger the schematical code for invoking triggers would look like
get_data:
loop
--
-- get rows affected by data manipulation statement
--
if not found then
leave get_data;
end if;
--
-- execute instead of row trigger
--
call instead_of_row_trigger_1;
...
call instead_of_row_trigger_n;
--
-- save data to old/new table if used
--
end loop;
--
-- call instead of statement triggers
--
call instead_of_statement_trigger_1;
...
call instead_of_statement_trigger_n;
Note that this schema includes instead of row triggers even though these are not supported in this version.
Example
Example of an instead of trigger, which can be used for handling join views.
CREATE TRIGGER book_details_instead_of_update
INSTEAD OF UPDATE ON mimer_store_book.book_details
REFERENCING NEW TABLE AS new_bd
BEGIN ATOMIC
--
-- Update one table with some of the data from the join view
--
UPDATE titles
SET authors_list = (SELECT authors_list
FROM new_bd
WHERE item_id = titles.item_id)
WHERE item_id IN (SELECT item_id
FROM new_bd);
--
-- Update another table using another column from the join view
--
UPDATE producers
SET producer_name = (SELECT publisher
FROM new_bd
WHERE item_id = producers.producer_id)
WHERE producer_id IN (SELECT item_id
FROM new_bd);
END
Example
The following example describes how triggers can be used to log all changes made to a table:
create table maintab (c1 integer primary key, c2 varchar(10));
create table logtab (ts timestamp default localtimestamp,
username nvarchar(128) collate SQL_IDENTIFIER
default session_user,
operation varchar(6),
c1old integer, c2old varchar(10),
c1new integer, c2new varchar(10));
@
create trigger maintabinserts after insert on maintab
referencing new table as newt
for each statement
begin atomic
insert into logtab (operation, c1new, c2new)
select 'INSERT', newt.c1, newt.c2
from newt;
end
@
@
create trigger maintabupdates after update on maintab
referencing new table as newt
old table as oldt
for each statement
begin atomic
insert into logtab (operation, c1old, c2old, c1new, c2new)
select 'UPDATE', oldt.c1, oldt.c2, newt.c1, newt.c2
from oldt, newt
where oldt.mimer_rowid = newt.mimer_rowid;
end
@
@
create trigger maintabdeletes after delete on maintab
referencing old table as oldt
for each statement
begin atomic
insert into logtab (operation, c1old, c2old)
select 'DELETE', oldt.c1, oldt.c2
from oldt;
end
@
A trigger’s old and new tables’ rows are sorted in the same order. This means that if old table data and new table data are fetched in parallel, the corresponding rows will be read even if the primary key has been updated.
This example’s update trigger uses the mimer_rowid pseudo-key to ensure the performance when joining the old and new tables.
The trigger event specifies the data manipulation statement that will cause the trigger to execute. The possible values for the trigger event are: INSERT, UPDATE and DELETE.
A statement trigger will be executed once each time the specified data manipulation statement is executed on the table on which the trigger was created.
A row trigger will be executed once for each row affected when the specified data manipulation statement is executed on the table on which the trigger was created.
Note:If the trigger time is INSTEAD OF, the trigger event itself has no effect on the table (view), it just causes the trigger to execute. The environment executing the trigger event behaves as if the data manipulation statement is actually being executed, even though no changes actually occur in the table(s) that would normally be affected. The only data manipulations possible in this case are those performed by the trigger action.
The trigger action, like the body of a routine, consists of a single procedural SQL statement. In addition, the execution of the SQL statement can be made conditional on the evaluation of a search condition.
The search condition is specified in the optional WHEN clause of the CREATE TRIGGER statement.
As for routines, it is recommended that a compound SQL statement always be used for the trigger action.
Note:The entire trigger action must be executed in a single atomic execution context, therefore if a compound SQL statement is used, it must be defined as ATOMIC, see The ATOMIC Compound SQL Statement.
The SQL statement(s) of the trigger action are always executed within the transaction started for the trigger event. The normal restrictions on the use of certain procedural SQL statements within a transaction apply.
In addition, because the trigger action must be atomic, a COMMIT or ROLLBACK statement cannot be executed within it.
The creator of the trigger must hold the appropriate access rights, with grant option, for all the operations performed within the trigger action. This is checked when the CREATE TRIGGER statement is executed.
If the trigger time specified for the trigger is BEFORE, the following restrictions apply to the trigger action:
•the trigger action must not contain any SQL statement that performs data update (i.e. DELETE, INSERT and UPDATE statements are not permitted)
•a routine whose access clause is MODIFIES SQL DATA must not be invoked from within the trigger action.
If an exception is raised from the trigger action, it can be handled within the trigger by declaring a handler in the normal way for a compound SQL statement, see Declaring Exception Handlers.
If there is no handler declared in the trigger action to handle the exception, it will propagate to the environment executing the trigger event and will be dealt with appropriately there. The default behavior at that level will be to undo the effect of the trigger event and all the operations performed in the trigger action.
It is possible to explicitly raise an exception from within the trigger action, or from within an exception handler declared in it, by executing the SIGNAL statement.
When the rows of the database table on which the trigger was created are examined from within the trigger action, they will always reflect the actual data manipulations performed by the trigger event and the trigger action.
In the case of an AFTER statement trigger, all rows inserted by the trigger event will be visible, all rows deleted by the trigger event will not be found and all rows updated by the trigger event will appear in their altered state.
In the case of an INSTEAD OF trigger, none of the data manipulations specified by the trigger event will seen when the table is examined because the trigger event does not actually perform any of its data change operations.
The rows of the old table and the new table will always show the changes that were specified by the trigger event, even if these changes were not actually performed on the database table (as is the case for INSTEAD OF triggers).
Any data manipulation statements occurring in a trigger action will be executed in the normal way. It is, therefore, possible that the execution of a data manipulation statement in the trigger action may lead to the execution of another trigger or the recursive execution of the current trigger.
In either case, the execution context of the current trigger action is preserved and the newly invoked trigger executes in the normal way, in its own execution context, with appropriate versions of any old table and new table or old row and new row variables.
Example
The following trigger is called recursively. An update statement causes the trigger to fire even when no rows are updated, hence the presence of a when clause to avoid an infinite recursive invocation.
CREATE TRIGGER products_after_update
AFTER UPDATE ON products
REFERENCING NEW TABLE AS pdt
WHEN ( EXISTS (SELECT * FROM pdt) )
BEGIN ATOMIC
UPDATE products
SET product_search = product_search_code(product),
product = (SELECT capitalize(TRIM(product))
FROM pdt
WHERE product_id = products.product_id)
WHERE product_id IN (SELECT product_id FROM pdt
WHERE product_search <>
product_search_code(products.product)
OR product <> capitalize(TRIM(products.product));
END
The COMMENT ON TRIGGER statement can be used to create a comment on a trigger.
Only the creator of the schema to which the trigger belongs may create a comment on the trigger.
The following points apply to triggers when using DROP and REVOKE with triggers:
•A trigger can be dropped by using the DROP TRIGGER statement.
•Only the creator of the trigger can drop it using the DROP TRIGGER statement.
•When a trigger is dropped, the comments created on it are also dropped.
•Dropping an object referenced from an SQL statement in a trigger action will cause the trigger to be dropped.
•If the required privileges held on a database object are revoked from the creator of a trigger whose trigger action contains an SQL statement referencing the object, the trigger will be dropped.