INSTEAD OF trigger type implementation method and device based on openGauss database

Abstract

The invention relates to an INSTEAD OF trigger type implementation method based on an openGauss database. The method comprises the following steps: receiving a database modification table operation request; judging whether an INSTEAD OF trigger exists or not, and checking whether the trigger can be triggered or not and whether repeated triggering exists or not; the tuple is processed, and the tuple is stored in a tuplestore; if the INSTEAD OF trigger needs to be triggered, a trigger function corresponding to the INSTEAD OF trigger is executed, if the INSTEAD OF trigger does not need to be triggered, an AFTER trigger function is executed, and then an operation result is returned. According to the method, by introducing the virtual tables INSERTED and DELETED, flexible interception and rewriting of DML operation are achieved, the flexibility and practicability of database operation are enhanced, meanwhile, direct modification of data is avoided, and the data security is improved.

Description

Technical Field

[0001] The present application belongs to the technical field of database operation, and in particular relates to a method, device and electronic device for implementing an INSTEADOF trigger type based on an openGauss database. Background Art

[0002] A trigger is a special object bound to a database table or view that automatically executes the bound stored procedure when a specific database event (such as INSERT, UPDATE, DELETE, etc.) occurs. Its core features include: (1) Event-driven: triggered by database DML (Data Manipulation Language) or DDL (Data Definition Language) commands rather than manual calls.

[0003] (2) Implicit execution: Users do not need to explicitly call triggers, and their lifecycles are automatically managed by the database.

[0004] The operation of the trigger depends on the following key elements: Trigger event (Event): defines the type of operation (such as INSERT, UPDATE, DELETE) to which the trigger responds.

[0005] Trigger timing (Timing): Specifies whether the trigger is triggered before (BEFORE) or after (AFTER) the event is executed.

[0006] Action: The specific logic that runs when triggered, usually implemented as a stored procedure or anonymous code block, such as data validation, cascading operations, or logging.

[0007] Trigger granularity (Granularity): Row-level trigger (ROW LEVEL): Triggered once for each row of data change, suitable for row-by-row processing scenarios.

[0008] Statement-level trigger (STATEMENT LEVEL): Triggered once for the entire SQL statement, regardless of the number of rows of data affected. Suitable for unified processing of batch operations.

[0009] openGauss is an enterprise-class open source relational database. Its trigger functionality is based on the SQL standard and PostgreSQL design, supporting the following types of triggers: Classification by triggering time: BEFORE trigger: It is activated before the triggering event (INSERT / UPDATE / DELETE) is executed. It is often used for data verification, automatic field filling, or business logic before modifying operations.

[0010] AFTER trigger: Activated after the trigger event is executed. It is usually used to record operation logs, synchronize data, or trigger cascading operations.

[0011] Classification by triggering event: DML triggers: triggers for data manipulation languages (INSERT, UPDATE, and DELETE), which can be bound to specific tables or views (support for views is currently limited). In openGauss's design philosophy, DML triggers and DDL triggers are two different types of objects. The triggers mentioned in this article primarily refer to DML triggers.

[0012] DDL trigger: A trigger for data definition language (CREATE, ALTER, DROP), also known as an event trigger.

[0013] Classification by trigger granularity: Row-level trigger: Triggered for each row of data change, suitable for scenarios that require row-by-row processing (such as data auditing).

[0014] Statement-level trigger: Triggered once for the entire SQL statement, suitable for unified processing of batch operations (such as counting the number of rows affected by the operation).

[0015] Typical trigger usage scenarios include: using BEFORE triggers to verify data integrity and using AFTER triggers to automatically record audit logs.

[0016] However, compared with commercial databases such as Oracle or SQL Server, openGauss's existing trigger functions still have the following shortcomings: Lack of support for INSTEAD OF triggers. INSTEAD OF triggers can intercept DML operations and completely rewrite them into specific operations, making them an important tool for implementing advanced database requirements in complex business scenarios. The lack of support for INSTEAD OF triggers limits the flexibility of the openGauss database in certain complex business scenarios, making it unable to meet advanced requirements such as intercepting DML and completely rewriting them into specific operations, thus restricting its performance in these scenarios. Summary of the Invention

[0017] To address these issues, this paper proposes a novel implementation method for INSTEAD OF triggers based on the openGauss database. This paper aims to provide statement-level INSTEAD OF trigger functionality for the openGauss database, enhancing its competitiveness in complex business scenarios. By implementing this functionality, the present invention can support the following typical application scenarios: Logical deletion: Use triggers to replace the DELETE operation with updating the IsDELETED field of the corresponding row, thereby achieving logical deletion rather than physical deletion.

[0018] Data verification and repair: Automatically check and repair illegal values when inserting data (for example, transfer amounts cannot be negative).

[0019] Data routing under sharding: Based on data characteristics, DML operations are routed to different physical tables, achieving an effect similar to that of partitioned tables.

[0020] In particular, to facilitate the use of statement-level INSTEAD OF triggers, this paper introduces two virtual tables, INSERTED and DELETED, that have the same structure as the DML target table. These tables are used to store the datasets involved in this DML statement. The specific supported scenarios are as follows: For an INSTEAD OF trigger on an INSERT statement, the newly added data will be saved in the INSERTED table.

[0021] For INSTEAD OF triggers on DELETE statements, the deleted data will be stored in the DELETED table.

[0022] For an INSTEAD OF trigger on an UPDATE statement, the modified data is saved in the INSERTED table, and the unmodified data is saved in the DELETED table.

[0023] The core of this invention lies in the following two technical strategies: (1) Support virtual table INSERTED and DELETED that are only visible during the execution of INSTEAD OF TRIGGER: To implement the INSTEAD OF TRIGGER function, the present invention introduces the virtual tables INSERTED and DELETED. These two tables are visible only during the execution of INSTEAD OF TRIGGER. Their design is based on the EphemeralNamedRelation (ENR) mechanism in the PostgreSQL database.

[0024] Creation and use of ENR: An ENR is created when the SPI (Server Programming Interface) is connected and is available only to queries planned and executed through the current SPI connection. Users can customize its name.

[0025] Mounting INSERTED and DELETED data: When executing an INSTEAD OF TRIGGER function, a database connection is established through SPI. ENRs named INSERTED and DELETED are created based on the trigger type. All row datasets affected by the DML operation are mounted under the corresponding ENR. For DML statements within the INSTEAD OF TRIGGER function, INSERTED and DELETED are two tables that can be queried and operated on.

[0026] Data collection mechanism: The data collection method for the INSERTED and DELETED tables will be described in detail in the Examples section.

[0027] (2) An alternative execution scheme based on the openGauss database executor to implement INSTEAD OF TRIGGER: The core logic of this invention is to implement the function of replacing DML execution with INSTEAD OFTRIGGER by modifying the executor of the openGauss database. The specific implementation is as follows: Skip actual DML execution: In the executor, skip the actual DML operation (insert, delete, update) to avoid direct modification of data.

[0028] Call the trigger function: At the end of the statement execution, call the trigger function corresponding to INSTEAD OF TRIGGER once.

[0029] Adapting the Volcano Model: Because the openGausss database uses the Volcano Model as its execution mechanism, the original design executed insert, delete, or update operations row by row, and only considered the statement complete after all qualifying rows had been processed. This invention adapts this model in the executor to implement an "INSTEAD OF TRIGGER" execution logic.

[0030] In order to achieve the above objectives, this application provides the following technical solutions: A first aspect of the present application provides an INSTEAD OF trigger type implementation method based on an openGauss database, the method comprising: S1. Receive a database table modification operation request; S2. Determine whether the table modification operation requires triggering an INSTEAD OF trigger. S3. Check if there are any tuples to be processed. S4. If there are tuples to be processed, for each tuple, based on the result of step S2, if an INSTEAD OF trigger needs to be triggered, the process calculates the tuple to be processed and saves it in the tuplestore, then returns to the previous step. If an INSTEAD OF trigger does not need to be triggered, the process calculates the tuple to be processed, performs the actual addition, deletion, and modification operations, and returns to the previous step. S5. If there are no tuples to be processed, based on the result of step S2, if an INSTEAD OF trigger needs to be triggered, the trigger function corresponding to the INSTEAD OF trigger is executed and the operation result is returned. If an INSTEAD OF trigger does not need to be triggered, the AFTER trigger function is executed and the operation result is returned.

[0031] Furthermore, in the method of the present application, determining whether the table modification operation needs to trigger an INSTEAD OF trigger includes: Determine whether there is a DML type INSTEAD OF trigger on the table corresponding to the table modification operation; If so, determine in advance whether the INSTEAD OF trigger can be triggered; If it can be triggered, further determine whether there is a repeated triggering of the same INSTEAD OF trigger.

[0032] Furthermore, in the method of the present application, the determination of whether there is repeated triggering of the same INSTEAD OF trigger is achieved by recording the OID of the trigger.

[0033] Furthermore, the method of the present application also includes: before executing the INSTEAD OF trigger function, registering two ENRs, INSERTED and DELETED, by type in the SPI, and specifying tupleDesc and reldata so that the INSERTED and DELETED temporary tables can be used in the trigger function.

[0034] Furthermore, in the method of the present application, the tupleDesc is completely consistent with the target table structure of the DML, and the reldata is the tuplestore collected in this method.

[0035] Furthermore, in the method of the present application, the INSERTED and DELETED are two tables that can be queried and operated.

[0036] Furthermore, in the method of the present application, executing the trigger function corresponding to the INSTEAD OF trigger includes: Skip the actual DML operation; At the end of statement execution, the trigger function corresponding to the INSTEAD OF trigger is called once.

[0037] A second aspect of the present application provides an INSTEAD OF trigger type implementation device based on an openGauss database, the device comprising: Request receiving module: used to receive database modification table operation requests; Judgment module: used to determine whether the table modification operation needs to trigger the INSTEAD OF trigger; Check module: used to check whether there are tuples to be processed; Calculation and processing module: If there are tuples to be processed, for each tuple to be processed, determine whether the INSTEAD OF trigger needs to be triggered; if so, calculate the tuple to be processed and save it in the tuplestore and return to the previous step; if not, calculate the tuple to be processed and perform the actual addition, deletion, and modification operations and return to the previous step; Function execution module: If there is no tuple to be processed, it determines whether the INSTEAD OF trigger needs to be triggered; if so, it executes the trigger function corresponding to the INSTEAD OF trigger and returns the operation result; if not, it executes the AFTER trigger function and returns the operation result.

[0038] The device implements the steps of the aforementioned method for implementing the INSTEAD OF trigger type based on the openGauss database when running.

[0039] A third aspect of the present application provides an electronic device, comprising: a memory and a processor; Memory: used to store computer programs; Processor: used to execute the computer program to implement the steps of the aforementioned INSTEAD OF trigger type implementation method based on the openGauss database.

[0040] A fourth aspect of the present application provides a computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, the steps of the aforementioned method for implementing the INSTEAD OF trigger type based on the openGauss database are implemented.

[0041] In summary, this paper proposes a method for implementing statement-level INSTEAD OF triggers based on the openGauss database. By introducing statement-level INSTEAD OF triggers at the database level, this method can effectively meet the needs of data operation interception and rewriting in complex business scenarios, thereby significantly improving the flexibility and practicality of the database. Specifically, this method has the following advantages: (1) Enhanced business logic processing capabilities: Through statement-level INSTEAD OF triggers, users can flexibly intercept and rewrite DML operations without modifying the original database architecture, implementing complex business logic such as logical deletion, data verification and repair, and data routing under sharding, to meet changing business needs.

[0042] (2) Improve the flexibility of database operations: This method provides users with more powerful database operation methods, enabling the database to better adapt to complex business scenarios, especially showing higher flexibility in dealing with data integrity, consistency, and data routing requirements.

[0043] (3) Optimize database performance and management: By flexibly handling DML operations, unnecessary data operations and redundant processing are reduced, thereby optimizing database performance to a certain extent, simplifying the data management process, and improving the maintainability of the database.

[0044] (4) Improve user experience and development efficiency: This method reduces the difficulty of developing complex business logic, allowing developers to focus more on the implementation of business logic without having to pay too much attention to the details of underlying data operations, thereby improving development efficiency and user experience.

[0045] Other features and advantages of the present invention will be described in detail in the following description, or may be understood through implementation of the relevant technical solutions of this application. The objectives and other advantages of this application may be achieved through the technical features and technical means clearly indicated in the description, claims, and drawings, and may be obtained through the implementation of these technical contents. BRIEF DESCRIPTION OF THE DRAWINGS

[0046] To more clearly illustrate the technical solutions of the embodiments of the present application, the following briefly introduces the drawings involved in the description of the embodiments. It should be noted that the drawings only illustrate some embodiments of the present application. Those skilled in the art can deduce other relevant drawings based on these drawings without engaging in creative work.

[0047] Figure 1 This is an overall implementation flow chart of the INSTEAD OF trigger type implementation method based on the openGauss database of the present invention.

[0048] Figure 2 Detailed implementation flow chart of the method according to the embodiment of the present invention.

[0049] Figure 3 It is a structural diagram of the composition of the device of the present invention.

[0050] Figure 4 A schematic structural diagram of an electronic device provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0051] In order to make the purpose, technical solutions and advantages of the embodiments of the present application clearer, the technical solutions in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. It should be understood that the described embodiments are only some embodiments of the present application, not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0052] In this document, the term "including" and any variations thereof (such as "including," "comprising," etc.) are open-ended expressions and should be understood as meaning "including but not limited to," meaning that the listed contents are not exhaustive and may include other contents not explicitly mentioned. The term "based on" should be understood as meaning "based at least in part on," meaning that the basis or condition referred to may not be the only factor and may also involve other relevant factors. The term "one embodiment" should be understood as meaning "at least one embodiment," meaning that the described embodiment is not the only possible implementation method and that other similar embodiments may exist.

[0053] In this application, the terms "a" and "a plurality" are used to modify related elements or features in an illustrative, non-restrictive manner. Unless the context clearly indicates otherwise, "a" should be understood as meaning "at least one," and "a plurality" should be understood as meaning "at least two." Those skilled in the art should interpret these terms appropriately based on the semantics and logical relationships of the context to ensure that they encompass the possibility of "one or more."

[0054] Figure 1 The following is the overall implementation process of the INSTEAD OF trigger type implementation method based on the openGauss database provided by this application, including the following steps: S1. Receive a database table modification operation request; S2. Determine whether the table modification operation requires triggering an INSTEAD OF trigger. S3. Check if there are any tuples to be processed. S4. If there are tuples to be processed, for each tuple, based on the result of step S2, if an INSTEAD OF trigger needs to be triggered, the process calculates the tuple to be processed and saves it in the tuplestore, then returns to the previous step. If an INSTEAD OF trigger does not need to be triggered, the process calculates the tuple to be processed, performs the actual addition, deletion, and modification operations, and returns to the previous step. S5. If there are no tuples to be processed, based on the result of step S2, if an INSTEAD OF trigger needs to be triggered, the trigger function corresponding to the INSTEAD OF trigger is executed and the operation result is returned. If an INSTEAD OF trigger does not need to be triggered, the AFTER trigger function is executed and the operation result is returned.

[0055] Figure 2 This method demonstrates the operation process of this method and describes the logical process of how to trigger an INSTEAD OF trigger based on conditions when performing the modify table operation (ExecModifyTable) in the database. The specific steps include: 1. Start: The process starts with "ExecModifyTable", which means executing the operation of modifying the table.

[0056] 2. Record whether the INSTEAD OF trigger needs to be triggered: Determine whether there is an INSTEAD OF trigger of the DML type on the table.

[0057] Determine in advance whether the INSTEAD OF trigger can be triggered (enable / disable).

[0058] Determine whether there is repeated triggering of the same INSTEAD OF trigger (by recording the trigger's OID).

[0059] 3. Determine whether there are tuples to be processed: If there are still tuples to be processed, proceed to the next step.

[0060] If there are no tuples to be processed, go directly to step 5.

[0061] 4. If there are still tuples to be processed, determine whether the INSTEAD OF trigger needs to be triggered: If an INSTEAD OF trigger needs to be triggered, the tuple processed this time is calculated and saved in the tuplestore, and then return to step 3.

[0062] If the INSTEAD OF trigger does not need to be triggered, the tuple processed this time is calculated and the actual addition, deletion, and modification operations are performed, and then the process returns to step 3.

[0063] 5. Determine whether the INSTEAD OF trigger needs to be triggered If an INSTEAD OF trigger needs to be triggered, the INSTEAD OF trigger function is executed and the operation result is returned.

[0064] 6. Execute the AFTER trigger function: If the INSTEAD OF trigger does not need to be triggered, the AFTER trigger function is executed and the operation result is returned.

[0065] The entire process uses a series of judgments and operations to ensure that when modifying a table in the database, the INSTEAD OF trigger is correctly triggered and executed, thereby intercepting and rewriting the DML operation.

[0066] In order to more clearly illustrate the technical solution of the present application, the following will further illustrate it through embodiments of specific scenarios.

[0067] The specific implementation steps of this embodiment are as follows: 1. In ExecModifyTable, first determine whether an INSTEAD OF trigger needs to be triggered. This determination must be made in advance (primarily by checking whether the trigger is set to disabled), rather than at the time of the actual trigger. If no INSTEAD OF trigger is required for this DML statement, the original execution flow is followed; otherwise, proceed to step 2.

[0068] 2. Four new TupleStoreStates are added to the execution state estate to record the table data of four temporary tables: inserted, deleted, inserted, and deleted. They are named new_ins_tuplestore, old_del_tuplestore, new_upd_tuplestore, and old_upd_tuplestore respectively.

[0069] 3. In the actual execution function of a single data entry, execInsert / execDelete / execUpdate, a new parameter is added to control whether to skip the actual execution.

[0070] (1) For deleted data (delete / update), get the deleted tuple according to the ctid to be deleted and store the data in the corresponding old_tuplestore, then exit the function without actual execution.

[0071] (2) For inserted data (update / insert), you need to wait for the inserted data to be filled (column default values and virtual columns need to be calculated in execInser / execUpdate), store the tuple to be inserted into the corresponding new_tuplestore, and then exit the function. No actual execution is performed.

[0072] 4. After processing all tuples, find the corresponding INSTEAD OF TRIGGER based on the current DML type and execute the corresponding trigger function. Before executing the trigger function, register the INSERTED and DELETED ENRs by type in the SPI. By specifying the tupleDesc and the actual reldata, the INSERTED and DELETED temporary tables can be used in the trigger function. The tupleDesc structure is identical to the target table structure of the DML, and the reldata is the tuplestore collected in the previous step.

[0073] The following example illustrates the effects of the present invention: a parent table, Person, and two child tables, Customers and Providers, are created. By creating an "instead of insert" trigger on the Person table, data can be routed to the two child tables based on certain characteristics.

[0074] --Create subtable CREATE TABLE Customers ( CustomerId INT IDENTITY(1,1), CustomerCode varchar(100), CustomerName VARCHAR(100), CustomerAddress VARCHAR(100) ); CREATE TABLE Providers ( ProviderId INT IDENTITY(1,1), ProviderCode varchar(100), ProviderName VARCHAR(100), ProviderAddress VARCHAR(100) ); -- Create the parent table CREATE table Person AS SELECT CustomerCode AS PersonCode , CustomerName AS PersonName , CustomerAddress AS PersonAddress, 'Customer' AS [Type] FROM Customers UNION ALL SELECT ProviderCode AS PersonCode , ProviderName AS PersonName , ProviderAddress AS PersonAddress, 'Provider' AS [Type] FROM Providers; -- Create a trigger function based on the type field create or replace function func_I_Person() returns trigger as $emp_old_new$ BEGIN INSERT INTO Customers ( CustomerName, CustomerAddress ) SELECT I.PersonName, I.PersonAddress FROM Inserted I WHERE I.Type = 'Customer'; INSERT INTO Providers ( ProviderName, ProviderAddress ) SELECT I.PersonName , I.PersonAddress FROM Inserted I WHERE I.Type = 'Provider'; return null; end; $emp_old_new$ language plpgsql; --Create trigger create trigger TR_I_Person instead of insert on Person for each statement EXECUTE PROCEDURE func_I_Person(); SELECT * FROM Customers; SELECT * FROM Providers; SELECT * FROM Person; INSERT INTO Person ( PersonName , PersonAddress , [Type]) VALUES ( 'Christian Gomez', '12th Street 125', 'Provider' ); INSERT INTO Person ( PersonName , PersonAddress , [Type]) VALUES ( 'Jenny Diaz', 'Riverside 123', 'Customer' ); SELECT * FROM Customers; SELECT * FROM Providers; SELECT * FROM Person; Figure 3 The figure shows an INSTEAD OF trigger type implementation device based on the openGauss database proposed in this application, which includes: Request receiving module: used to receive database modification table operation requests; Judgment module: used to determine whether the table modification operation needs to trigger the INSTEAD OF trigger; Check module: used to check whether there are tuples to be processed; Calculation and processing module: If there are tuples to be processed, for each tuple to be processed, determine whether the INSTEAD OF trigger needs to be triggered; if so, calculate the tuple to be processed and save it in the tuplestore and return to the previous step; if not, calculate the tuple to be processed and perform the actual addition, deletion, and modification operations and return to the previous step; Function execution module: If there is no tuple to be processed, it determines whether the INSTEAD OF trigger needs to be triggered; if so, it executes the trigger function corresponding to the INSTEAD OF trigger and returns the operation result; if not, it executes the AFTER trigger function and returns the operation result.

[0075] When the above device is running, the steps of the method for implementing the INSTEAD OF trigger type based on the openGauss database disclosed in this application are implemented.

[0076] The flowcharts and block diagrams in the accompanying drawings illustrate possible implementations of the apparatus, methods, and computer program products according to various embodiments of the present application, including architecture, functions, and operations. In these figures, each box may represent a module, a program segment, or a portion of a code, which contains one or more executable instructions for implementing a specified logical function. It should be noted that each box in the block diagram and / or flowchart, and the combination of these boxes, can be implemented using a dedicated hardware-based system to implement the specified function or operation, or can be implemented by a combination of dedicated hardware and computer instructions.

[0077] like Figure 4 As shown, an embodiment of the present application further discloses an electronic device, comprising: a processor 310, a communication interface 320, a memory 330 for storing a computer program executable by the processor, and a communication bus 340. The processor 310, the communication interface 320, and the memory 330 communicate with each other via the communication bus 340. The processor 310 executes the executable computer program to implement the steps of the above-mentioned method for implementing the INSTEAD OF trigger type based on the openGauss database.

[0078] It is understood that, in addition to the memory and processor, the electronic device may also include an input device (e.g., a keyboard), an output device (e.g., a display), and other communication modules. These input devices, output devices, and other communication modules all communicate with the processor via an I / O interface (i.e., an input / output interface).

[0079] The operation of the present application can be implemented by writing computer program code using one or more programming languages or a combination thereof. The programming languages include but are not limited to the following types: Object-oriented programming languages, such as Java, Smalltalk, C++, etc.; A conventional procedural programming language, such as "C" or a similar programming language.

[0080] The execution methods of the program code include but are not limited to: Executes entirely on the user's computer; Partially executed on the user's computer and partially on a remote computer; Executed as a standalone software package; Executes entirely on the remote computer or server.

[0081] In scenarios involving a remote computer, the remote computer can be connected to the user's computer via any type of network, including but not limited to a local area network (LAN) or a wide area network (WAN). Additionally, the remote computer can be connected to an external computer via an Internet service provider, such as the Internet.

[0082] Furthermore, the present application also discloses a computer-readable storage medium. When the instructions in the computer-readable storage medium are executed by a processor of an electronic device, the electronic device is enabled to execute the various steps of the INSTEAD OF trigger type implementation method based on the openGauss database disclosed in the present application.

[0083] In the context of this application, computer-readable storage media refers to tangible media that can store computer program code and related data. Specific examples include, but are not limited to, the following: (1) Portable computer disk: A removable magnetic storage medium such as a floppy disk.

[0084] (2) Hard disk: includes fixed storage devices such as mechanical hard disks and solid-state hard disks.

[0085] (3) Random Access Memory (RAM): Volatile storage medium used for temporary storage of data and program code.

[0086] (4) Read-only memory (ROM): A non-volatile storage medium used to store fixed programs and data.

[0087] (5) Erasable Programmable Read-Only Memory (EPROM) or Flash Memory: A non-volatile storage medium that supports multiple erasing and programming.

[0088] (6) Fiber optic storage device: storage medium based on fiber optic technology.

[0089] (7) Compact Disc Read-Only Memory (CD-ROM): A read-only medium that stores data in the form of an optical disc.

[0090] (8) Optical storage devices: storage media based on optical principles, such as DVDs and Blu-ray discs.

[0091] (9) Magnetic storage devices: storage media based on magnetic principles, such as magnetic tapes and disks.

[0092] (10) Any suitable combination of the above: for example, combining multiple storage media to meet different storage requirements.

[0093] These computer-readable storage media can be used to store the program code and related data described in this application to support the operation of the program and the persistent storage of data.

[0094] In particular, according to embodiments of the present application, the process described in the flowchart can be implemented as a computer software program. For example, embodiments of the present application relate to a computer program product comprising a computer program carried on a non-transitory computer-readable medium. The computer program includes program code for executing the openGauss database-based INSTEAD OF trigger type implementation method disclosed in the present application. When the computer program is executed by a processing device, the above-mentioned functions defined in the embodiments of the present application can be implemented.

[0095] Although the above discussion contains several specific implementation details, these details should not be interpreted as limiting the scope of this application. The above description is only a preferred embodiment of the present application and an illustration of the technical principles used. Those skilled in the art should understand that the scope of disclosure involved in this application is not limited to the technical solutions formed by the specific combination of the above technical features. At the same time, this application should also cover other technical solutions formed by any combination of the above technical features or their equivalent features without departing from the above disclosed concepts.

[0096] Those skilled in the art should also understand that they may modify the technical solutions described in the aforementioned embodiments, or replace some of the technical features therein with equivalents, without departing from the spirit and scope of the technical solutions of the embodiments of the present application. Such modifications or replacements will not cause the essence of the corresponding technical solutions to deviate from the core spirit and scope of the technical solutions of the embodiments of the present application.

Claims

1. A method for implementing an INSTEAD OF trigger type based on the openGauss database, characterized in that: The method comprises: S1. Receive a database table modification operation request; S2. Determine whether the table modification operation requires triggering an INSTEAD OF trigger. S3. Check if there are any tuples to be processed. S4. If there are tuples to be processed, for each tuple, based on the result of step S2, if an INSTEAD OF trigger needs to be triggered, the process calculates the tuple to be processed and saves it in the tuplestore, then returns to the previous step. If an INSTEAD OF trigger does not need to be triggered, the process calculates the tuple to be processed, performs the actual addition, deletion, and modification operations, and returns to the previous step. S5. If there are no tuples to be processed, based on the result of step S2, if an INSTEAD OF trigger needs to be triggered, the trigger function corresponding to the INSTEAD OF trigger is executed and the operation result is returned. If an INSTEAD OF trigger does not need to be triggered, the AFTER trigger function is executed and the operation result is returned.

2. The method according to claim 1, characterized in that The determination of whether the table modification operation needs to trigger an INSTEAD OF trigger includes: Determine whether there is a DML type INSTEAD OF trigger on the table corresponding to the table modification operation; If so, determine in advance whether the INSTEAD OF trigger can be triggered; If it can be triggered, further determine whether there is a repeated triggering of the same INSTEAD OF trigger.

3. The method according to claim 2, characterized in that The determination of whether there is repeated triggering of the same INSTEAD OF trigger is achieved by recording the OID of the trigger.

4. The method according to claim 1, wherein The method further includes: before executing the INSTEAD OF trigger function, registering two ENRs, INSERTED and DELETED, in the SPI by type, and specifying tupleDesc and reldata so that the INSERTED and DELETED temporary tables can be used in the trigger function.

5. The method according to claim 4, characterized in that The tupleDesc is completely consistent with the target table structure of the DML, and the reldata is the tuplestore collected in this method.

6. The method according to claim 4, characterized in that The INSERTED and DELETED tables are two tables that can be queried and operated.

7. The method according to claim 1, characterized in that The executing the trigger function corresponding to the INSTEAD OF trigger includes: Skip the actual DML operation; At the end of statement execution, the trigger function corresponding to the INSTEAD OF trigger is called once.

8. A device for implementing INSTEAD OF trigger type based on openGauss database, characterized in that: The device comprises: Request receiving module: used to receive database modification table operation requests; Judgment module: used to determine whether the table modification operation needs to trigger the INSTEAD OF trigger; Check module: used to check whether there are tuples to be processed; Calculation and processing module: If there are tuples to be processed, for each tuple to be processed, determine whether the INSTEAD OF trigger needs to be triggered; if so, calculate the tuple to be processed and save it in the tuplestore and return to the previous step; if not, calculate the tuple to be processed and perform the actual addition, deletion, and modification operations and return to the previous step; Function execution module: If there is no tuple to be processed, it determines whether the INSTEAD OF trigger needs to be triggered; if so, it executes the trigger function corresponding to the INSTEAD OF trigger and returns the operation result; if not, it executes the AFTER trigger function and returns the operation result.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method for implementing an INSTEAD OF trigger type based on an openGauss database as described in any one of claims 1 to 7 are implemented.

10. An electronic device, characterized in that: include: memory and processor; Memory: used to store computer programs; Processor: used for executing the computer program to implement the steps of the method for implementing the INSTEAD OF trigger type based on the openGauss database as described in any one of claims 1 to 7.

Images