phpMyEdit 5.7.1: instant MySQL table editor and code generator
Prev	Chapter 3. General options	Next

3.8. Triggers

Triggers overview

Triggers provide advanced users with the ability to write their own PHP scripts for such things as validating user input, and to have their code executed at the appropriate time. Triggers are files that are included via an include() statement and conditionally executed by phpMyEdit.class.php. SQL triggers are included before or after insert, update, or delete of record. FORM triggers are included before displaying the form that will allow the corresponding operation, or after the user canceled this form.

View form is related to the 'select' operation.
Add and Copy forms are related to the 'insert' operation
Edit form is related to the 'update' operation.
Delete form is related to the 'delete' operation.

For SQL triggers, the operation sequence is this: before, main, after. If any operation fails, not only should the next operation(s) not be executed, but the previous ones are 'rolled back' as if they never happened. If a database is not able to do this, it is not 'transaction-safe'.

Triggers are risky in basic MySQL as there is no native transaction support. It is not transaction-safe by default. There are transaction-safe table types in MySQL that can be conditionally built (see MySQL-Max), but phpMyEdit is currently not set up to support real transactions. What that means is that if an operation fails, the database may be left in an intermediate and invalid state.

The programmer must understand and accept these risks prior to using the phpMyEdit triggers mechanism. If the triggers are used, they execute within the namespace or scope of the phpMyEdit class.

Triggers must return true or false to indicate success or failure.

Triggers types

There are following types of phpMyEdit triggers:

'pre' triggers are usually used to check conditions before displaying the operation's page. For example, users may be allowed to View all records but can only Edit a subset of them. Another usage is to lock the record in order to avoid other users to start to change it at the same time.
'before' triggers are usually used to verify conditions prior to executing the main operation. For example, they can be of some use for input validation.
'after' triggers are usually used to perform follow-up operations after the main operation. For example, to update secondary tables to enforce referential integrity or to update aggregate tables.
'cancel' triggers are usually used to perform follow-up operations after users cancel the form. For example, if a record is locked using a 'pre' triggers, then a 'cancel' trigger can unlock it.

If 'pre' triggers fail, users are sent back to the list, except for the 'update' case, where users are sent back to view page if the pre-update trigger fails.

Trigger examples

Example 3-21. Select triggers

// Before displaying the view page
$opts['triggers']['select']['pre']    = 'categories.TSP.inc';
// After canceling the view page
$opts['triggers']['select']['cancel'] = 'categories.TSC.inc';

Example 3-22. Insert triggers

// Before displaying the add/copy page
$opts['triggers']['insert']['pre']    = 'categories.TIP.inc';
// After requesting save or more in the add/copy page
$opts['triggers']['insert']['before'] = 'categories.TIB.inc';
$opts['triggers']['insert']['after']  = 'categories.TIA.inc';
// After canceling the add/copy page
$opts['triggers']['insert']['cancel'] = 'categories.TIC.inc';

Example 3-23. Update triggers

// Before displaying the edit page
$opts['triggers']['update']['pre']    = 'categories.TUP.inc';
// After requesting save or apply in the edit page
$opts['triggers']['update']['before'] = 'categories.TUB.inc';
$opts['triggers']['update']['after']  = 'categories.TUA.inc';
// After canceling the edit page
$opts['triggers']['update']['cancel'] = 'categories.TUC.inc';

Example 3-24. Delete triggers

// Before displaying the delete page
$opts['triggers']['delete']['pre']    = 'categories.TDP.inc';
// After requesting delete in the delete page
$opts['triggers']['delete']['before'] = 'categories.TDB.inc';
$opts['triggers']['delete']['after']  = 'categories.TDA.inc';
// After canceling the delete page
$opts['triggers']['delete']['cancel'] = 'categories.TDC.inc';

Please note that ['select']['after'] and ['select']['before'] triggers currently do not exist.

In the following sample are steps during a View, Edit, Apply and Cancel operation described. All involved triggers return true.

user starts from the list page
user asks to view a record
['select']['pre'] trigger is included (if defined)
if true is returned then continue, else go back to list page
user is now in the view page
user asks to edit the record
['update']['pre'] trigger is included (if defined)
if true is returned then continue, else go back to view page
user is now in the edit page
user makes some changes and asks to apply (save and continue)
['update']['before'] trigger is included (if defined)
if true is returned then continue, else, back to list without updating
record is updated in the database
['update']['after'] trigger is included (if defined)
['update']['pre'] trigger is included (if defined)
if true is returned then continue, else go back to view page
user is now back to the edit page
user makes some other changes but asks to cancel them
['update']['cancel'] trigger is included (if defined)
user is back to the list page

Triggers variables

In every trigger file you have available following usable variables. Some of them affect only a particular action.

`$this`	object reference
`$this->dbh`	initialized MySQL database handle
`$this->key`	primary key name
`$this->key_type`	primary key type
`$this->key_delim`	primary key deliminator
`$this->rec`	primary key value (update and delete only)
`$newvals`	associative array of new values (update and insert only)
`$oldvals`	associative array of old values (update and delete only)
`$changed`	array of keys with changed values

There are also other variables available. For example every class property can be accessed using $this object reference. All variables occur in 'before' triggers as well as in 'after' triggers. Only class properties occurs in 'pre' and 'cancel' triggers currently.

It is recommended to use the $this->myQuery() method in order to perform database queries for fetching additional data or doing inserts or updates to other database tables.

Chained triggers

You may set several triggers for the same operation. This allows to isolate code, to share more easily triggers between multiple calling scripts and to reuse code produced by another phpMyEdit users.

The order triggers are executed in is important. It is set by the keys of the chained triggers. As soon as one of the chained trigger fail, the overall return value is set to false, and following triggers are not executed. If none of chained triggers failed, then the return value is true.

Example 3-25. Chained update before triggers

$opts['triggers']['update']['before'][1] = 'lock.TUA.inc';
$opts['triggers']['update']['before'][0] = 'check.TUB.inc';

In this example, when the user clicks on the Save button during editing a record, check.TUB.inc will be run first. If it returns true, then lock.TUB.inc will be run as well. If one of these triggers fails, update of the database won't be performed, just like if a simple ['update']['before'] trigger failed. Note that although the overall return value of 'cancel' triggers does not inluence phpMyEdit behavior, the return value of each chained trigger is important.

Prev	Home	Next
Filters	Up	Logging user actions