View source | Discuss this page | Page history | Printable version   
Toolbox
Main Page
Upload file
What links here
Recent changes
Help

PDF Books
Show collection (0 pages)
Collections help

Search

ERP 2.50:Developers Guide/How To Create a Trigger

ERP 2.50:Developers Guide

Index

Contents

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.

Bulbgraph.png   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

 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;

Postgres

 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();

Rough breakdown of the structure from beginning to end is:

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

You should use this code if you are working with PostgreSQL:

    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_APPLICATION_ERROR(-20100, 'Partner is not an employee');
   END IF;
RAISE EXCEPTION '%', '@Message@' ; --OBTG:-20000--
   IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;

Oracle vs Postgres

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

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.



ERP 2.50:Developers Guide/How To Add a Constraint | ERP 2.50:Developers Guide/How to add a field to a Window Tab 

Retrieved from "http://wiki.openbravo.com/wiki/ERP_2.50:Developers_Guide/How_To_Create_a_Trigger"

This page has been accessed 12,963 times. This page was last modified on 14 June 2011, at 11:04. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.