Creating a Trigger

---

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.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se