Дерекқор триггерлері: деректерді басқару және тұжырымдамалық тұтастықты сақтау

Database code that executes after certain events on a table
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 can also be used to log historical data, for example to keep track of employees' previous salaries.

Below follows a series of descriptions of how some popular DBMS support triggers.

In addition to triggers that fire (and execute PL/SQL code) when data is modified, Oracle 10g supports triggers that fire when schema level objects (that is, tables) are modified and when user logon or logoff events occur.

From Oracle 8i, database events logons, logoffs, startups can fire Oracle triggers.

A list of all available firing events in Microsoft SQL Server for DDL triggers is available on Microsoft Docs. Performing conditional actions in triggers (or testing data following modification) is done through accessing the temporary Inserted and Deleted tables.

Limited support for triggers in the MySQL/MariaDB DBMS was added in the 5.0 version of MySQL, launched in 2005. As of version 8.0, they allow for DDL (Data Definition Language) triggers and for DML (Data Manipulation Language) triggers. They also allow either type of DDL trigger (AFTER or BEFORE) to be used to define triggers. They are created by using the clause CREATE TRIGGER and deleted by using the clause DROP TRIGGER. The statement called upon an event happens is defined after the clause FOR EACH ROW, followed by a keyword (SET or BEGIN), which indicates whether what follows is an expression or a statement respectively.

IBM DB2 for distributed systems known as DB2 for LUW (LUW means Linux, Unix, Windows) supports three trigger types: Before trigger, After trigger and Instead of trigger. Both statement level and row level triggers are supported. If there are more triggers for same operation on table then firing order is determined by trigger creation data. Since version 9.7 IBM DB2 supports autonomous transactions. Before trigger is for checking data and deciding if operation should be permitted. If exception is thrown from before trigger then operation is aborted and no data are changed. In DB2 before triggers are read only — you can't modify data in before triggers. After triggers are designed for post processing after requested change was performed. After triggers can write data into tables and unlike some other databases you can write into any table including table on which trigger operates. Instead of triggers are for making views writeable. Triggers are usually programmed in SQL PL language.