Extension Documentation

Triggers
Prev Chapter 4. Usage and Screens Next

Triggers

The following description is taken from Wikipedia:

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries.

Triggers are commonly used to:

  • audit changes (e.g. keep a log of the users and roles involved in changes)

  • enhance changes (e.g. ensure that every change to a record is time-stamped by the server's clock)

  • enforce business rules (e.g. require that every invoice have at least one line item)

  • execute business rules (e.g. notify a manager every time an employee's bank account number changes)

  • replicate data (e.g. store a record of every change, to be shipped to another database later)

  • enhance performance (e.g. update the account balance after every detail transaction, for faster queries)

The examples above are called Data Manipulation Language (DML) triggers because the triggers are defined as part of the Data Manipulation Language and are executed at the time the data is manipulated. Some systems also support non-data triggers, which fire in response to Data Definition Language (DDL) events such as creating tables, or runtime or and events such as logon, commit and rollback. Such DDL triggers can be used for database auditing purposes.

The following are major features of database triggers and their effects:

  • triggers do not accept parameters or arguments (but may store affected-data in temporary tables)

  • triggers cannot perform commit or rollback operations because they are part of the triggering SQL statement (only through autonomous transactions)

MySQL

MySQL 5.0.2 introduced support for triggers. MySQL supports these trigger types:

  • Insert Trigger

  • Update Trigger

  • Delete Trigger

[Note]Note

MySQL allows only one trigger of each type on each table (i.e. one before insert, one after insert, one before update, one after update, one before delete and one after delete).

[Note]Note

MySQL does NOT fire triggers outside of a statement (i.e. API's, foreign key cascades)

Triggers List

The triggers list display shows the database table triggers created by the component.

It does not show any triggers created by another mechanism, unless they happen to contain the specific string 'jaudit' within their name.

Triggers are names after the table upon which they are applied, followed by the specific string '_jaudit_' and then by a two character string, the first of which indicates whether it is a 'BEFORE' (b) or 'AFTER' (a) trigger, and the second indicates whether it is applied on an 'INSERT' (i), 'UPDATE' (u) or 'DELETE' operation.'

The screen also allows a single point where triggers may be enabled, which is where the trigger is created within the database. Disabling the trigger removes the trigger from the database.

[Important]Important

MySQL has a few restrictions upon triggers which may be briefly summarised as follows: Only one trigger of any specific type is possible upon any given table. Triggers if present are automatically enabled. Triggers can not be disabled. Disabling a trigger requires the trigger be deleted.

The screen can also used to delete triggers. Deletion of a trigger not only removes it from the database (disabling), but also removes all trace of the trigger from the component.

Figure 4.5. Triggers List

Triggers List

Prev Up Next
Change History Entry Home Trigger Entry
Go To Top

Joomla! Debug Console

Session

Profile Information

Memory Usage

Database Queries