Trigger object system based on openGauss and implementation method

Abstract

The invention relates to a trigger object system based on openGauss (open Gauss). The system comprises a trigger creation module, a trigger head and a trigger control module, wherein the trigger head is used for specifying trigger user authority, a table object on which a trigger depends, trigger operation, trigger type, trigger position, trigger sequence, trigger frequency and trigger conditions; the trigger body comprises statements needing to be executed after the trigger is triggered; the trigger calling module is used for executing trigger statements and trigger operation statements according to a sequence specified by a trigger when a user needs to execute trigger operation on a table object on which the trigger depends; and the trigger deletion module is used for dropping the corresponding trigger to realize deletion of the trigger when the trigger has the related authority. The trigger object system can be directly created, deleted and used independent of the trigger function, the strong dependency relationship of an original mode is avoided, and the trigger can be used more flexibly and conveniently. Meanwhile, trigger permission access control is added in the system, so that the use of the trigger is safer.

Description

Technical Field

[0001] The present invention belongs to the technical field of trigger object systems, and in particular relates to an openGauss-based trigger object system and an implementation method thereof. Background Art

[0002] A trigger is an important object in a database management system. It is a special stored procedure related to table events. It is triggered by events and is mainly used to maintain data integrity, implement business rules, and perform logging to improve database reliability and application stability. When operating a table, a trigger can automatically update related content or prevent non-compliant data from being modified based on specific conditions. Triggers not only simplify data operation logic and improve development efficiency, but also reduce data problems caused by human errors.

[0003] The existing triggers in openGauss are mainly composed of two parts, namely the trigger function and the trigger. You must first create the trigger function, and then create a trigger based on the function. When the trigger condition is triggered, the specified function is executed. When deleting, you need to delete the related trigger function at the same time, which makes the usage more complicated. In addition, the triggers in openGauss currently have no permission control, and the triggers may be abused, causing damage to the data and structure of the database. Summary of the invention

[0004] In order to overcome the above defects of the existing openGauss triggers, optimize the use of trigger objects in the openGauss database, avoid cascading with functions, and improve the security of triggers, the present invention proposes a new openGauss-based trigger object system and implementation method.

[0005] The trigger object system of the present invention includes three modules: trigger creation, trigger use and trigger deletion. A trigger can be directly created without creating a trigger function, and a trigger can be directly deleted without considering related dependencies. At the same time, the trigger object system of the present invention increases the check of user permissions and enhances the security of the database.

[0006] Specifically, if Figure 2 As shown, the trigger object system of the present invention includes the following three modules: The trigger creation module mainly consists of two parts: trigger header and trigger body. The trigger header is used to specify the trigger user permissions, the table objects that the trigger depends on, the trigger operation, the trigger type, the trigger location, the trigger sequence, the trigger frequency and the trigger condition, etc. The trigger body contains the statements that need to be executed after the trigger is triggered.

[0007] Trigger calling module: if the user wants to perform a trigger operation on the table object that the trigger depends on, first check whether there is a related trigger. If so, determine the trigger condition. If the trigger condition is met, execute the trigger statement and trigger operation statement in the order specified by the trigger.

[0008] Trigger deletion module. The original trigger deletion in openGauss must specify the object table on which the trigger is based, and needs to specify whether cascade deletion is required. The trigger deletion module in the system of the present invention only needs to drop the corresponding trigger when relevant permissions are available.

[0009] Furthermore, in the present invention, when creating a trigger, the user needs to have the CREATE TRIGGER permission for the relevant table. The system administrator can create a trigger whose owner is himself, or a trigger whose owner is another ordinary user; while an ordinary user can only create a trigger whose owner is himself. The operation permissions that a trigger can perform can be determined based on the owner's permissions.

[0010] Furthermore, in the present invention, the trigger operation of the trigger includes operations such as adding (INSERT), deleting (DELETE), modifying (UPDATE) and truncating (TRUNCATE) on the trigger table object. When the object generates a corresponding operation, the trigger is triggered and executed. The trigger operation can be one or more.

[0011] Furthermore, in the present invention, the trigger type of a trigger is classified according to the relative position of the statement executed by the trigger and the operation of the table object, wherein BEFORE is to execute the trigger statement before the trigger event occurs, AFTER is to execute the trigger statement after the trigger event occurs, and INSTEAD OF is to execute the trigger statement directly instead of the trigger event.

[0012] Furthermore, the triggering order of triggers refers to the execution order of each trigger when the same triggering event of the same object and the same triggering position contain multiple triggers. Figure 1 As shown, if you specify a trigger that precedes (PRECEDES) or follows (FOLLOWS), the specified execution order must be followed. You cannot specify multiple triggers that precede (PRECEDES) or follow (FOLLOWS). At the same time, the trigger and the basic trigger type that specify the order must be consistent. If multiple triggers specify FOLLOWS for the same trigger, the most recently created trigger will be executed first in the order in which it was created; if multiple triggers specify PRECEDES for the same trigger, the earliest created trigger will be executed first in the order in which it was created. If the order is not specified, they need to be executed simultaneously and triggered in the order sorted by the trigger name.

[0013] Furthermore, the trigger frequency of a trigger refers to the number of times the trigger is triggered when a trigger operation is generated. In the present invention, it may be triggered once for each row of a trigger event, or it may be triggered only once for each SQL.

[0014] Furthermore, the trigger condition of the trigger is to determine whether to trigger according to a specified conditional expression. In the present invention, the trigger will only be triggered when the condition is true.

[0015] Furthermore, in the present invention, when deleting a trigger, the trigger name is specified, and the corresponding trigger is deleted according to the trigger name. It is required that no trigger with the same name can exist in the same database. When creating a trigger, it can be achieved by replacing the original trigger or renaming it.

[0016] Furthermore, the present invention also provides a method for implementing the trigger object system, comprising the following steps: S1. User creates triggers: System administrators can create triggers directly, while ordinary users need to be granted trigger creation permissions by the system administrator before they can create triggers. S2. Check the user's CREATE TRIGGER permission. If the user does not have this permission, the system administrator needs to grant this permission to the user. S3. Specify trigger owner: System administrators can specify themselves or ordinary users as trigger owners. Ordinary users can only specify themselves as trigger owners. S4. Select the trigger type and trigger operation, set the trigger frequency and trigger conditions, and trigger the execution of the data inserted into the base table or the updated data of the automatic modification table; S5. To call a trigger, you need to insert or update data in the base table. First, insert data into the base table, query and modify the record table to add a new record, and the record content is the inserted data. S6. Update the base table, then query the modification record table, and determine whether the created trigger is triggered by inserting and updating the base table based on the newly added data rows. S7. The user directly deletes the trigger and executes the insert and update data again; S8. If there is no new data in the modification record table, it means that the trigger has been deleted.

[0017] In addition, the present invention also provides a computer-readable storage medium, on which a computer program is stored, and when the program is executed by a processor, the steps of the above-mentioned method for implementing the trigger object system based on openGauss are implemented.

[0018] In summary, the trigger object system based on openGauss of the present invention can be directly created, deleted, and used independently of the trigger function, avoiding the strong dependency of the original mode, making the use of triggers more flexible and convenient. At the same time, the system adds trigger permission access control to make the use of triggers safer. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] In order to more clearly illustrate the technical solution of the present invention, the following briefly introduces the drawings required for use in the description of the present invention. Obviously, the following drawings are only some embodiments recorded in the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without paying creative work.

[0020] Figure 1 Schematic diagram of the execution sequence of the trigger object system of the present invention.

[0021] Figure 2 It is a schematic diagram of the composition structure of the trigger object system of the present invention.

[0022] Figure 3 This is a flow chart of the implementation method of the trigger object system of the present invention. DETAILED DESCRIPTION

[0023] In order to make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with specific embodiments. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments, and the present invention can also be implemented or applied through other different specific implementation methods. The details in this specification can also be modified or changed in various ways based on different viewpoints and applications without departing from the spirit of the present invention.

[0024] At the same time, it should be understood that the protection scope of the present invention is not limited to the specific embodiments described below; it should also be understood that the terms used in the embodiments of the present invention are for describing specific embodiments rather than for limiting the protection scope of the present invention.

[0025] Embodiment: A trigger object system implementation method based on openGauss This embodiment is based on the existing system users and common users, and uses triggers to insert or update the base table, inserting each data into the modification record table to record each changed data.

[0026] like Figure 3 As shown, the implementation method of the trigger object system includes the following steps: Step 1: The user creates a trigger. If you are a system administrator, you can create a trigger directly. If you are a common user, you need the system administrator to grant you the permission to create triggers before you can create a trigger.

[0027] Step 2: Check the user's CREATE TRIGGER permission. If the user does not have this permission, the system administrator needs to grant this permission to the user. Step 3: Specify the trigger owner. If you are a system administrator, you can specify yourself or a common user, while a common user can only specify himself. The default is the current user.

[0028] Step 4: Create a BEFORE type trigger for the INSERT and UPDATE operations on the base table. The trigger frequency is once per row. Do not set the trigger condition. It can be triggered by default. The trigger executes the data inserted into the base table or the updated data in the automatic modification table.

[0029] Step 5: To call the trigger, you need to insert or update data in the base table. First, insert the base table, query and modify the record table to add a new record with the inserted data.

[0030] Step 6: Update the base table and query the modification record table. There are two data in the current table. The newly added data line updates the data in the base table, indicating that both inserting and updating the base table triggered the created trigger.

[0031] Step 7: The user directly deletes the trigger and executes the insert and update data again.

[0032] Step 8: If there is no new data in the modification record table, it means that the trigger has been deleted.

[0033] The above description is only a preferred embodiment of the present invention and does not limit the present invention in any form. Any technician familiar with the profession can make some changes or modifications to the technical contents disclosed above without departing from the scope of the technical solution of the present invention to obtain equivalent embodiments of equivalent changes. However, any simple modification, equivalent changes and modifications made to the above embodiments based on the technical essence of the present invention without departing from the content of the technical solution of the present invention should be included in the scope of protection of the claims of the present invention.

Claims

1. A trigger object system based on openGauss, characterized in that: The trigger object system includes: a trigger creation module, a trigger calling module and a trigger deletion module; wherein: Trigger creation module: includes trigger header and trigger body; trigger header is used to specify trigger user permissions, table objects that the trigger depends on, trigger operation, trigger type, trigger location, trigger sequence, trigger frequency and trigger conditions; trigger body contains the statements that need to be executed after the trigger is triggered; Trigger calling module: When the user wants to perform a trigger operation on the table object that the trigger depends on, if the trigger conditions are met, the trigger calling module executes the trigger statement and the trigger operation statement in the order specified by the trigger; Trigger deletion module: used to delete triggers by dropping corresponding triggers when relevant permissions are available.

2. The trigger object system based on openGauss according to claim 1, characterized in that: The trigger user permission means that when creating a trigger, the user needs to have the CREATE TRIGGER permission for the relevant table; System administrators can create triggers whose owners are themselves or other ordinary users; ordinary users can only create triggers whose owners are themselves; The permissions of the trigger are determined by the permissions of the owner.

3. The trigger object system based on openGauss according to claim 1, characterized in that: The trigger operation includes adding, deleting, modifying and truncating operations on the table object that the trigger depends on, and there are one or more trigger operations.

4. The openGauss-based trigger object system according to claim 1, characterized in that: The trigger type refers to the classification of the relative position of the operation according to the statement executed by the trigger and the table object, among which BEFORE is to execute the trigger statement before the trigger event occurs, AFTER is to execute the trigger statement after the trigger event occurs, and INSTEAD OF is to execute the trigger statement directly instead of the trigger event.

5. The openGauss-based trigger object system according to claim 1, characterized in that: The trigger order refers to the execution order of each trigger when the same trigger event of the same object and the same trigger position contain multiple triggers; The trigger and base trigger type of the specified sequence must be consistent; If multiple triggers specify FOLLOWS the same trigger, the most recently created trigger is executed first in the order in which it was created; If multiple triggers specify the same trigger as PRECEDES, the earliest created trigger will be executed first in the order in which it was created; If order is not specified, triggers are sorted by name and fired in order.

6. The trigger object system based on openGauss according to claim 1, characterized in that: The trigger frequency refers to the number of times the trigger is triggered when a trigger operation is generated. The trigger frequency can be selected to trigger once for each row of the trigger event or only once for each SQL.

7. The trigger object system based on openGauss according to claim 1, characterized in that: The trigger condition is to determine whether to trigger based on the specified conditional expression, and it will only be triggered when the condition is true.

8. The openGauss-based trigger object system according to claim 1, characterized in that: The trigger deletion module specifies the trigger name when deleting a trigger, and deletes the corresponding trigger according to the trigger name.

9. The openGauss-based trigger object system according to claim 1, characterized in that: The implementation method of the trigger object system comprises the following steps: S1. User creates triggers: System administrators can create triggers directly, while ordinary users need to be granted trigger creation permissions by the system administrator before they can create triggers. S2. Check the user's CREATE TRIGGER permission. If the user does not have this permission, the system administrator needs to grant this permission to the user. S3. Specify trigger owner: System administrators can specify themselves or ordinary users as trigger owners. Ordinary users can only specify themselves as trigger owners. S4. Select the trigger type and trigger operation, set the trigger frequency and trigger conditions, and trigger the execution of the data inserted into the base table or the updated data of the automatic modification table; S5. To call a trigger, you need to insert or update data in the base table. First, insert data into the base table, query and modify the record table to add a new record, and the record content is the inserted data. S6. Update the base table, then query the modification record table, and determine whether the created trigger is triggered by inserting and updating the base table based on the newly added data rows. S7. The user directly deletes the trigger and executes the insert and update data again; S8. If there is no new data in the modification record table, it means that the trigger has been deleted.

10. A computer-readable storage medium having a computer program stored thereon, wherein the program, when executed by a processor, implements the steps of the method for implementing a trigger object system based on openGauss as claimed in claim 9.

Images