Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use database triggers to achieve data synchronization," interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "how to use database triggers to synchronize data"!

1. What is a trigger

A database trigger is a stored PL/SQL statement associated with a table. Whenever a specific data operation statement (insert update delete) is issued on a specified table, Oracle automatically executes the sequence of statements defined in the trigger.

The trigger application scenarios are as follows:

Complex security checks

Confirmation of data

database audit

Backup and audit of data

Create [OR REPLACE] TRIGGER trigger_name {BEFORE| AFTER } {INSERT | DELETE | UPDATE [OF column [, column …]]} [OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...] ON [schema.] table_name | [schema.] view_name [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}] [FOR EACH ROW ] [WHEN condition] PL/SQL_BLOCK | CALL procedure_name;

Of which:

BEFORE and AFTER indicate that the trigger timing of the trigger is pre-trigger and post-trigger respectively. Pre-trigger refers to triggering the currently created trigger before executing the trigger event, and post-trigger refers to triggering the currently created trigger after executing the trigger event.

FOR EACH ROW option Description Triggers are row triggers.

The difference between row triggers and statement triggers is that row triggers require that when a DML statement manipulates multiple rows of data affecting the database, the trigger is activated once for each data row as long as they meet the trigger constraints; statement triggers treat the entire statement operation as a trigger event, and when it meets the constraints, the trigger is activated once.

When the FOR EACH ROW option is omitted, BEFORE and AFTER triggers are statement triggers, whereas INSTEAD OF triggers can only be row triggers

The REFERENCING clause describes the correlation name, which can be used in the PL/SQL block and WHEN clause of the row trigger to refer to the current new and old column values. The default correlation names are OLD and NEW, respectively. Correlation names must be preceded by a colon (:) when applied in PL/SQL blocks of triggers, but not in WHEN clauses.

WHEN clause describes trigger constraints. When Condition is a logical expression, it must contain the relevant name, and cannot contain query statements, nor can it call PL/SQL functions. The trigger constraint specified by the WHEN clause can only be used in BEFORE and AFTER row triggers, not INSTEAD OF row triggers and other types of triggers.

Stored procedures to be executed when a base table is modified ( INSERT, UPDATE, Delete) are automatically triggered according to the changes in the base table to which they are attached, so they are independent of the application. Database triggers can ensure data consistency and integrity.

3. Types of Oracle triggers

Row-level triggers: Once for each row affected by a DML statement, for example, an update statement updates 100 items of data. If we define row-level triggers for update, row-level triggers will be fired 100 times.

Statement-level triggers: Executed once per DML statement, e.g. an update statement updates 200 pieces of data. If we define a statement-level trigger for update, the statement-level trigger will fire once.

123456789create or replace trigger sync_salary after update on emp for each row begin update emp_back set sal=:new.sal where empno=:new.empno; end; /

In the example above, this row-level trigger will be automatically executed to update the salaries of employees in the backup table after we update the salaries of the main table emp.

At this point, I believe that we have a deeper understanding of "how to use database triggers to achieve data synchronization," may wish to actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.