Trigger

With a trigger, before / after data changes to a specific custom action table can be performed.

Limbas supports two types: Database-Trigger and Limbas-Trigger.

Administration

Triggers can be managed under Admin → Tools → Trigger.

To add a trigger, a (unique) name, table, and type (BEFORE / AFTER INSERT / UPDATE / DELETE) of the trigger must be specified. Then you can use the pen symbol of the Definition column to adjust the actions of the trigger to be executed.

Database-Trigger

A database trigger executes a database procedure after appropriate initiation. It should be noted that each type of table (BEFORE / AFTER INSERT / UPDATE / DELETE) can only be defined once in the database for each table.

Triggers are always stored in the system table lmb_trigger and in a second step synchronized with the actual database triggers.

With ‘Save’ new/modified input in the LIMBAS system table ‘lmb_trigger’ will be stored, the actions are grouped together if necessary with procedures and are then created as a trigger in the database.

When ‘syncronising’, LIMBAS checks whether actions are defined in the procedures of Database-Triggers, which do not exist in the system table ‘lmb_trigger’. This function also tries to add the missing actions to the table ‘lmb_trigger’ before the operation continues.

When inserting links in a table, LIMBAS automatically creates Database-Triggers, which make the entries in the affiliated ‘link table’. For details see Links. These System-Triggers can, through the option ‘show Limbas-Triggers’, be hidden.

LIMBAS-Trigger

Unlike database triggers, Limbas triggers can execute their own PHP code. It is recommended to outsource this to a function of Extensions. The usual Limbas functionality is available. Triggers are even executed when changes are made to special fields such as links or multiple selection fields. For each table, multiple triggers of the same type can be defined. These are executed when the database is changed in the order listed.

After creating a Limbas trigger and a reset, the newly created trigger can be assigned to a table under Admin → Tables in the column “Trigger”. Optionally, it can only be assigned to specific fields in the Table Field Settings or to specific groups only in the Table Rights. Only after assignment the trigger is active!

When executing a trigger, the following PHP variables are available and can be passed to your own function in any order. Some variables are not available for all trigger types:

Variable	Description	Valid for
$typ	INSERT / UPDATE / DELETE	all
$position	AFTER	all
$gtabid	Tables-ID	all
$fieldid	Field-ID	all trigger retrieved from field
$trigger	Trigger Definition	all
$ID	Data Record-ID	all
$new_value	value – the value or array	BEFORE UPDATE / AFTER UPDATE
$old_value	old value – as an array	BEFORE UPDATE / AFTER UPDATE
$triggerResult	rivate function return value “false” terminates the calling function and initiated a rollback	all

For BEFORE, a rollback can be initiated by a return false. Changes will not be accepted.

Examples

after insert

Modification of a new data record. The field DATE should contain the current timestamp.

function myExt_updateBill($ID) {
  global $db;
  $query = 'UPDATE BILLING SET DATE = TIMESTAMP WHERE ID = ' . $ID;
  $rs = odbc_exec($db, $query);
}

The trigger would be:

myExt_updateBill($ID);

before delete

Check whether an invoice can be deleted. A return false will cancel the delete function before it is executed.

function myExt_checkBill($ID, $gtabid) {
   global $db;
   $query = 'SELECT ID,RESERVED FROM BILLING WHERE ID = ' . $ID;
   $rs = odbc_exec($db, $query);
   if(odbc_result($rs, 'RESERVED')){
       lmb_alert('deleting reserved billings is not allowed!');
       return false;
   }
   return true;
}

The trigger would be:

return myExt_checkBill($ID, $gtabid);

after update

In the table ‘Position’ there are various fields, including ‘Price’, ‘Quantity’ and ‘Sum’. When changing the price or quantity fields, the sum is recalculated. To do this, we create a LIMBAS trigger ‘my_calculate_sum($ID)’ by choosing a name, for the table ‘Position’, with the attribute ‘after update’ and select it in the table settings, in the fields ‘Price’ and ‘Quantity’.

function my_calculate_sum($ID) {
   global $db;
   $sqlquery = 'UPDATE POSITIONEN SET SUMME = (PREIS * ANZAHL) WHERE ID = ' . $ID;
   $rs = odbc_exec($db, $sqlquery);
}

The trigger would be:

my_calculate_sum($ID);

after update

The same function can be described for the field number with the variables $new_value and $old_value.

function my_calculate_sum2($ID, $gtabid, $new_value, $old_value) {
   global $db;

   $sum = ($old_value[$gtabid][PREIS_FIELD_ID][0] * $new_value);

   $sqlquery = 'UPDATE POSITIONEN SET SUMME = ' . parse_db_float($sum) . ' WHERE ID = ' . $ID;
   $rs = odbc_exec($db, $sqlquery);
}

The trigger would be:

my_calculate_sum2($ID, $gtabid, $new_value, $old_value);