How to create a Trigger

Objective

Within this howto article we will use the HT_Salary table created in the How to develop a new window or the How to Create a Table howtos. Once we have that table created we want to ensure that salaries can only be entered within business partners that are marked as Employees and not other ones. The system must prevent us from entering salary information for business partners that are only marked as customers or vendors.

This constraint cannot be defined as a database check constraint because it requires a SQL query which is not allowed within checks. To implement this constraint a trigger must be used. A trigger is a piece of code that is executed whenever a table is modified (on INSERT, UPDATE and/or DELETE events).

Module

All new developments must belong to a module that is not the core module. Please follow the How to create and package a module section to create a new module.

Note the DB Prefix defined there is HT which will explicitely indicate the prefix of our new trigger! Through this prefix, Openbravo will know to package this trigger along with the howto module.

Adding the Trigger to Database

Triggers do not require any description within the application dictionary. They only need to be added to the database, following the DB Prefix rule that indicates which module they belong to.

Let's first add the trigger to the database and we'll comment on it afterwards. Note that the actual SQL code varies depending on the database engine used, Postgres or Oracle. Here is an example for both:

Oracle <source lang="sql"> CREATE OR REPLACE TRIGGER ht_salary_trg

        AFTER INSERT OR UPDATE
        ON ht_salary FOR EACH ROW

DECLARE
    v_IsEmployee CHAR(1);
  
BEGIN
    
    IF AD_isTriggerEnabled()='N' THEN 
      RETURN;
    END IF;

    SELECT IsEmployee
      INTO v_IsEmployee
      FROM C_BPartner
     WHERE C_BPartner_ID = :new.C_BPartner_ID;
  
   IF v_IsEmployee = 'N' THEN
     RAISE_APPLICATION_ERROR(-20100, 'Partner is not employee');
   END IF;

END ht_salary_trg;

</source>

Postgres <source lang="sql"> CREATE OR REPLACE FUNCTION ht_salary_trg()

  RETURNS trigger AS
$BODY$ DECLARE 

DECLARE
   v_IsEmployee CHAR(1);

BEGIN

  IF AD_isTriggerEnabled()='N' THEN IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF; 
  END IF;

  SELECT IsEmployee
    INTO v_IsEmployee
    FROM C_BPartner
   WHERE C_BPartner_ID = NEW.C_BPartner_ID;
  
   IF v_IsEmployee = 'N' THEN
     RAISE EXCEPTION '%', 'Partner is not employee';
   END IF;

   IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF; 

END; 
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER ht_salary_trg AFTER INSERT OR UPDATE ON ht_salary
    FOR EACH ROW EXECUTE PROCEDURE ht_salary_trg();

</source>

Rough breakdown of the structure from beginning to end is:

• Trigger name The name of the trigger follows the modularity naming conventions, so if we want to include the trigger in our module for which DBprefix is HT, the trigger will start with HT. In this case its name is HT_SALARY_TRG.
• When it is executed and for which table. After the trigger name we define when the trigger is going to be executed. In this case we define that it will be raised each time there is an insertion or update on our HT_SALARY table. Notice the difference between Oracle and Postgres.
• Define variables Define the local variables you require - in this case we only need one variable to store the flag if the partner is an employee or not.
• Enable soft trigger disable. All Openbravo triggers must implement soft disabling, next section explains how.
• Select if it is an employee. The following saves the flag IsEmployee to a local variable that indicates if the current record is an employee or not. Note that to get the values in the current record we can use the :New.C_BPartner_ID variable, in case it is an update (not an insert) there is also an :Old set of variables with the old values before the update takes place.

    SELECT IsEmployee
      INTO v_IsEmployee
      FROM C_BPartner
     WHERE C_BPartner_ID = :new.C_BPartner_ID;

• Raise an error if it is not correct and rollback the transaction. Once we have the v_IsEmployee we can check whether it is employee or not, and in case it is not raise an error and abort the transaction. Note that whenever a trigger raises an error the transaction is rolled back. There is a restriction with modularity here. Oracle allows a range of error codes from -20000 to -20999 for custom message, but modules cannot define custom messages in application dictionary because they cannot follow the naming rules. So when raising an error in a trigger within a module it must use one of the existing codes (for example -20100 is a generic error), but the message shown in the UI will not be as useful as if it were created specifically for this case.

   IF v_IsEmployee = 'N' THEN
     RAISE_APPLICATION_ERROR(-20100, 'Partner is not an employee');
   END IF;

• To export properly a RAISE EXCEPTION from postgresql to xml files you have to add the following comment at the end of the command; --OBTG:-20000--

RAISE EXCEPTION '%', '@Message@' ; --OBTG:-20000--

• Return the correct version of the object in case of Postgres where a trigger consists of a function plus a trigger assignment

   IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;

Trigger soft disabling

Some processes require to be executed without trigger being enabled in the transaction they are executed in, maintaining them enabled for the rest of transactions. In order to accomplish this, all Openbravo trigger must implement the so called soft disabling. This is code to implement it, so be included at the beginning of all triggers, in this manner if AD_isTriggerEnabled function returns N, no other code in the trigger will be executed:

Oracle:

    IF AD_isTriggerEnabled()='N' THEN 
      RETURN;
    END IF;

PostgreSQL:

    IF AD_isTriggerEnabled()='N' THEN
       IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;
    END IF;

Disabling triggers

When a process requires to be executed with triggers disabled for its current DB transaction, it must execute:

In Java, note this will disable triggers in current DAL transaction:

<source lang="java"> try {

     TriggerHandler.getInstance().disable();
     
     // do your logic here
   } finally {
     TriggerHandler.getInstance().enable();
   }

</source>

PL:

This feature is available starting from 3.0PR18Q3.

Disabling: <source lang="sql">ad_disable_triggers</source>

Enabling: <source lang="sql">ad_enable_triggers</source>

Oracle vs Postgres

Writing triggers for Postgres or Oracle is somewhat different so let's describe the main differences:

• trigger's in Postgres are functions that return a trigger object associated with a table for a specific action (INSERT, UPDATE or/and DELETE). In Oracle, their definition is slightly simpler. Hence there are two CREATE statements required in Postgres (one for the function and one for assigning the function as the trigger of a table) versus one in Oracle.
• the use of NEW/OLD reserved words to reference the new record (that is being inserted or updated to) or the old one (that is being deleted or updated) is different (:NEW in Oracle vs NEW in Postgres)
• Postgres trigger function must explicitly take care of returning the trigger object, also depending on the type of the trigger (e.g. the last line is IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF; )

For more details on database differences, please see the following article.

Exporting Triggers as Part of the Module

Whenever application dictionary or physical database is modified, it is possible to export that information to xml files belonging to the specific module. This way you can maintain Openbravo ERP database data as source code XML files (that can then be source controlled).

To do so, execute:

 ant export.database

This will export all artifacts of the module currently marked as In Development within the application dictionary.

For further explanations read the Development tasks document.