Several applications of triggers

SummaryThis paper enumerates several representative applications of triggers: Data dispersion-centralized model design, historical data export, data interface between application systems. And how to design these triggers are discussed.
　　 Key WordsTriggers, data dispersion-centralized model, historical data export, data interface

1 Introduction
Triggers are often used in large database designs. It is characterized by: once defined, it exists in the background database system (server, servers) and is automatically implicitly executed under the corresponding conditions, thus making its design irrelevant to the platform of the foreground (client, client side) and the design of the data manipulation associated with the foreground.
In reference [1], several applications of triggers are enumerated: auditing, complex integrity constraints, complex security authorization, event login, column value export, and table replication in distributed databases.

Several other applications of 2 triggers
2.1 Data dispersion--centralized model design
In the actual development process, often encountered such data maintenance requirements: Units by a number of departments, requiring departments to maintain only the data of the Department, but on the other hand, they need to be dispersed to the various departments of the data collected together to get the summary data of this unit. If a school has more than one department, the school needs the grades of each department; a factory has a number of production workshops, the factory needs the production of each workshop summary; a company has a number of sales departments, the company needs the Department of sales summary and so on.
In this case, the database design is difficult without the use of triggers:
. If a table is established for each department, it is obviously difficult to get the aggregated data (in this case, the view mechanism is not available);
. If all departments share a table (at which point the data in this table is actually the aggregated data), because each department needs to maintain the data, it has the right to modify the table, so it is difficult to control the data security.
With triggers, the above problems can be solved: create a table for each department (all of the department's permissions are limited to modifying the table), create a table for the summary data, and then set up triggers on each department table to make the data update on the departmental table. The related data in the summary table will be changed accordingly (see Figure 1).

Fig. 1 Application of flip-flop to data dispersion--centralized model

In this model, we should pay attention to the design of the relevant fields of the Department table integrity constraints, so that the data in each department table is unique to prevent different department tables have the same data records, resulting in confusion in the summary table.

2.2 Historical Data export Tables in a database record only the latest data, not historical data. But in many cases, the record and analysis of historical data is more meaningful than real data (which is one of the differences between data Warehouse and database), such as the change of school number, the change of factory quota, the change of company's product and raw material price, the rise and fall of stock, etc., they all need to record historical data. How to make the database can also record historical data. Using triggers can solve such problems. The steps to establish such triggers are: After the data table is set up, the corresponding history table is established (in general, the history table is a superset of the data table in the field composition, that is, add the field of time on the original table fields), and then set up a trigger between the two (see Figure 2). In this way, whenever the data table has data changes, the trigger will record the changed data into the historical data table, thus achieving the purpose of automatically recording historical data.

Figure 2 Export mode of historical data 2.3 Data interface between application Systems The construction of a complete information system is generally not one-step, is often completed in batches, and the system will often have data transmission, but because of the change in demand or other reasons, The database design of the system without the same time may be inconsistent in the table structure and even in the fields (even during the same period of development, this phenomenon will often occur due to the deviation or inadequacy of the overall design or data dictionary, or the need to integrate multiple systems). Without the possibility of rebuilding these systems, the data between them can be delivered seamlessly. In other words, do they seamlessly connect to each other?

In this case, the trigger can be a better solution: the establishment of the intermediate table, the design of the intermediate table is in line with the design format of the demand-side application system, and its data is consistent with the data of the supplier application system. (See Figure 3) Figure 3 The data interface between different application systems It is important to note that the data between the application systems is unidirectional (i.e. data transfer), and if the data requires bidirectional flow (i.e. data exchange), there should be an exit mechanism in the design of the trigger to avoid the recurrence of triggers.