Database trigger

A database trigger is procedural code that is automatically executed in response to specific events on a table or view in a database. Triggers are commonly used to maintain data integrity within a database. For example, inserting a new employee record can automatically generate related entries in the tax, vacation, and salary tables. Similarly, triggers enable historical data logging, such as tracking charges to employee salaries over time.

A database trigger is procedural code that is automatically executed in response to specific events on a table or view in a database. Triggers are commonly used to maintain data integrity within a database. For example, inserting a new employee record can automatically generate related entries in the tax, vacation, and salary tables. Similarly, triggers enable historical data logging, such as tracking charges to employee salaries over time. CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] } ON table_name [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE funcname ( arguments ) {CREATE | RECREATE | CREATE OR ALTER} TRIGGER name FOR {table name | view name} [ACTIVE | INACTIVE] {BEFORE | AFTER} {INSERT [OR UPDATE] [OR DELETE] | UPDATE [OR INSERT] [OR DELETE] | DELETE [OR UPDATE] [OR INSERT] } [POSITION n] AS BEGIN .... END Starting with version 2.1, Firebird introduced support for database-level triggers, which execute during specific session and transaction events. These include connection events, via and triggers, and transactional events, via , , and triggers. If an exception is raised during a trigger, the connection sequence is aborted. Similarly, exceptions raised within a trigger block the completion of the transaction, including the preparation phase during a two-phase commit. Database-level triggers can be utilized to enforce multi-table constraints or to emulate materialized views. When an exception is raised inside a trigger, the transactional modifications made by the trigger prior to the error are rolled back and the client application is notified. However, the transaction itself remains active as if the operation had never been initiated, allowing the application to perform further data modifications and request the commit sequence. The general syntax for creating or altering database-level triggers in Firebird uses the following structure: {CREATE | RECREATE | CREATE OR ALTER} TRIGGER name [ACTIVE | INACTIVE] ON {CONNECT | DISCONNECT | TRANSACTION START | TRANSACTION COMMIT | TRANSACTION ROLLBACK} [POSITION n] AS BEGIN ..... END CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] [database_name .] trigger_name [BEFORE | AFTER | INSTEAD OF] {DELETE | INSERT | UPDATE [OF column_name [, column_name]...]} ON {table_name | view_name} [FOR EACH ROW] [WHEN condition] BEGIN ... END SQLite supports only row-level triggers and does not provide native support for statement-level triggers. Updatable views, which are also unsupported by default within the engine, can be emulated by implementing triggers over target view structures. ===XML databases=== Triggers are implemented within non-relational database management systems. For example, the XML database Sedna supports triggers utilizing XQuery. Triggers in Sedna are designed to mirror the structural behavior of standard SQL:2003 triggers, but they are natively optimized around XML query and update paradigms, including XPath, XQuery, and XML update extensions. A trigger in Sedna can be assigned to arbitrary nodes within an XML document stored in the database. When these targeted nodes undergo modification, the trigger automatically executes the corresponding XQuery queries or update scripts defined within its action body. For example, the following trigger blocks the deletion of a node if any open auctions continue to reference that specific profile: CREATE TRIGGER "trigger3" BEFORE DELETE ON doc("auction")/site//person FOR EACH NODE DO { if (exists($WHERE//open_auction/bidder/personref/@person=$OLD/@id)) then ( ) else $OLD; } { _id: ObjectId("59cf1860a95168b8f685e378"), customerId: ObjectId("59cf17e1a95168b8f685e377"), orderDate: ISODate("2018-06-26T16:20:42.313Z"), shipDate: ISODate("2018-06-27T08:20:23.311Z"), orderContents: [ { qty: 1, name: "Earl Grey Tea Bags - 100ct", price: NumberDecimal("10.99") } ], shippingLocation: [ { location: "Memphis", time: ISODate("2018-06-27T18:22:33.243Z") }, ] } CREATE OR REPLACE TRIGGER phone_book_audit AFTER UPDATE ON phone_book FOR EACH ROW BEGIN INSERT INTO phone_book_audit (audit_id,audit_change, audit_l_name, audit_f_name, audit_old_phone_number, audit_new_phone_number, audit_date) VALUES (audit_id_sequence.nextVal,'Update', :OLD.last_name, :OLD.first_name, :OLD.phone_number, :NEW.phone_number, SYSDATE); END; An command executed against the table modifies records where the last name is "Jones": UPDATE phone_book SET phone_number = '111-111-1111' WHERE last_name = 'Jones'; The resulting table is populated with two distinct entries because the source database contains two records matching the surname "Jones". Because a row-level trigger executes once for each modified row, the engine invokes the trigger logic twice during the execution of a single update statement. CREATE OR REPLACE TRIGGER phone_book_history AFTER UPDATE ON phone_book BEGIN INSERT INTO phone_book_edit_history (audit_history_id, username, modification, edit_date) VALUES (audit_history_id_sequence.nextVal, USER,'Update', SYSDATE); END; Executing the identical statement isolates the behavioral contrast of a statement-level trigger: UPDATE phone_book SET phone_number = '111-111-1111' WHERE last_name = 'Jones'; The transaction log indicates that the trigger executed only once, despite the data modification affecting two individual records. CREATE OR REPLACE TRIGGER phone_book_insert BEFORE INSERT ON phone_book FOR EACH ROW WHEN (LENGTH(new.last_name) > 10) BEGIN :new.last_name := SUBSTR(:new.last_name,0,1); END; The mechanism is observed when attempting to insert a record that satisfies the filter criteria: INSERT INTO phone_book VALUES (6, 'VeryVeryLongLastName', 'Erin', 'Minneapolis', 'MN', '989 University Drive', '123-222-4456', 55408, TO_DATE('11/21/1991', 'MM/DD/YYYY')); The engine commits the row data with the truncated surname variable, confirming the modification occurred before the record was written to the table. CREATE OR REPLACE TRIGGER hauschbc BEFORE INSERT ON SOMEUSER.phone_book BEGIN RAISE_APPLICATION_ERROR ( num => -20050, msg => 'Error message goes here.'); END; An attempted data insertion targeted at the protected resource causes the database engine to abort the transaction sequence and return an error message to the calling client application interface: SQL Error: ORA-20050: Error message goes here. Custom runtime exceptions raised via the protocol are subject to a restricted numeric range. To avoid conflicts with pre-allocated system error codes, the application developer must define this variable within the numeric boundaries of −20000 to −20999. ==References==

Text derived from https://en.wikipedia.org/w/index.php?oldid=1371117893 under CC BY-SA 4.0; modifications have been made.