Introduction and Application Examples of KlustronDB Trigger Function

Note:

Unless otherwise specified, the version numbers in the text can be replaced with the version numbers of any released version. For all released versions, see: Release notes

Overview

The main content is first to introduce an overview of triggers, then introduce row-level (FOR EACH ROW) and statement-level (FOR EACH STATEMENT) triggers, followed by the basic statements and precautions for creating triggers, and finally demonstrate the usage of triggers based on FOR EACH ROW supported by KlustronDB and the usage of triggers based on FOR EACH STATEMENT.

01 Introduction to Triggers

First, let's mention trigger functions. Trigger functions are similar to user-defined functions; they are also user-defined functions. The difference is that trigger functions do not need to be explicitly called and executed in SQL, but are automatically executed by the database system when specific events occur (such as insert, delete, or update operations on a table/view). Therefore, when users create this special function, the trigger function, they need to not only define the function body but also specify the timing of its execution—that is, to specify on which events (insert/delete/update) occurring on which objects (table/view) the trigger function should be executed before/after. In this way, a complete trigger is created.

Therefore, a trigger consists of two parts: 1. the trigger function; 2. the definition of the trigger (that is, defining the timing of the trigger function execution).

In KlustronDB, triggers are special functions used to automatically execute when specific operations (such as insert, update, delete) occur in the database. Triggers can be triggered at the row level (FOR EACH ROW) or statement level (FOR EACH STATEMENT), depending on the definition of the trigger.

The following are the differences between FOR EACH ROW and FOR EACH STATEMENT triggers:

1. FOR EACH ROW trigger

• A FOR EACH ROW trigger is triggered for each affected row. That is to say, when an operation affects each row in the table, the trigger is executed.
• A FOR EACH ROW trigger can access and manipulate the data of the row being modified because it is triggered before or after each row is inserted, deleted, or updated.
• 'FOR EACH ROW' triggers are usually used in situations where a specific action needs to be performed for each affected row, such as updating other related rows when a particular row is updated.

2. FOR EACH STATEMENT Trigger

• A FOR EACH STATEMENT trigger is triggered after the execution of the statement is completed, rather than between each row being inserted, deleted, or updated.
• FOR EACH STATEMENT triggers cannot directly access or manipulate the data of the rows being modified, because they are triggered at the statement level.
• FOR EACH STATEMENT triggers are usually used in situations where operations need to be performed on the result of the entire statement, such as performing some aggregate calculations or logging when inserting large batches of data.

When writing triggers, it is necessary to determine whether to use FOR EACH ROW or FOR EACH STATEMENT triggers based on specific requirements and operations. If you need to perform individual operations on each row or need to access the modified row data, you should choose a FOR EACH ROW trigger. If you only need to operate on the result of the entire statement without accessing specific row data, you should choose a FOR EACH STATEMENT trigger.

02 Trigger Creation Syntax

Based on the previous introduction, we can distinguish trigger functions from ordinary functions: using predefined variable names to reference data change information in the context; special return types. Apart from that, there is not much difference from other ordinary functions. Here, the main focus is on the creation of triggers, and the following is the syntax rule for creating triggers:

CREATE [ CONSTRAINT ] TRIGGER name -- triggers名称
        { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] } -- 触发的时机(BEFORE/AFTER)和触发事件（例如增删改等）
    ON table_name -- triggers针对的表
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ] -- 是否延迟执行
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] name } [ ... ] ] -- 见上文的'triggers参数'
    [ FOR [ EACH ] { ROW | STATEMENT } ] -- triggers的级别
    [ WHEN ( condition ) ] -- triggers时需要额外满足的条件
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments ) -- triggers函数

where event can be one of:

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE

The addition of remarks in the grammar rules is already very detailed, and readers can refer to the examples given when explaining trigger parameters to understand it. Here, we will add the following key points:

• Currently, KlustronDB does not support deferred execution triggers (i.e., triggers that execute when the transaction is committed)
• Although you can specify 'parameters' for a trigger function when creating a trigger, the trigger function is still required to be a function without parameters; these passed-in 'parameters' are actually stored in the context mentioned above and can be referenced through the system-defined name TG_ARGV.
• When the concurrency of update operations is very high or the amount of modified data is large, row triggers have a significant impact on performance. They not only prevent the pushdown of DML statements, but also cause the trigger functions to be executed a large number of times. If we only care about modifications to certain data, we can specify a WHEN clause when creating the trigger, so that the trigger function is only executed when the conditions of the clause are met, reducing the impact on performance.
• Currently, KlustronDB requires that the table for creating triggers must have a primary key (this restriction may be removed in future versions).
• If a statement-level trigger is created on a partitioned table, it will only be triggered when the user explicitly updates the partitioned table, and updating the partitioned table's subtables individually will not trigger it; if a row-level trigger is created on the partitioned table, it will be triggered both when the user updates the partitioned table and when the subtables of the partitioned table are updated individually.

03 FOR EACH ROW Trigger Usage Example

This is an example of the usage of a FOR EACH ROW trigger. One is the business table 'product' and the other is the audit table 'product_record'. For each row operation performed on the 'product' table, the trigger will be triggered to record every operation on the 'product' table into the audit table 'product_record'.

3.1 Connect to the database

[root@kunlun1 ~]# su - kunlun
[kunlun@kunlun1 ~]$ psql -h 192.168.56.112 -p 47001 postgres

3.2 Create the business table 'product' and the audit table 'product_record'

CREATE TABLE "public"."product" (
  "pro_id" int8 primary key,
  "pro_name" varchar(100),
  "pro_type" varchar(100),
  "price" int8
);

CREATE TABLE "public"."product_record" (
  "pro_id" int8 ,
  "pro_name" varchar(100) ,
  "pro_type" varchar(100),
  "price" int8,
  "data_type" varchar(50),
  "update_time" varchar(50) 
);

3.3 Create trigger function

create or replace function product_record_fun() 
returns trigger as $$
  BEGIN
    IF TG_OP = 'INSERT' then
    INSERT INTO "public"."product_record"("pro_id", "pro_name", "pro_type", "price", "data_type", "update_time") 
    VALUES (new.pro_id, new.pro_name, new.pro_type, new.price, tg_op, to_char(now(),'YYYYMMDD'));
    ELSIF TG_OP = 'UPDATE' then
    INSERT INTO "public"."product_record"("pro_id", "pro_name", "pro_type", "price", "data_type", "update_time") 
    VALUES (new.pro_id, new.pro_name, new.pro_type, new.price, tg_op, to_char(now(),'YYYYMMDD'));
    ELSIF TG_OP = 'DELETE' then
    INSERT INTO "public"."product_record"("pro_id", "pro_name", "pro_type", "price", "data_type", "update_time") 
    VALUES (old.pro_id, old.pro_name, old.pro_type, old.price, tg_op, to_char(now(),'YYYYMMDD'));
    END IF;
    return new;
  END;
$$
LANGUAGE plpgsql;

3.4 Create trigger

create trigger product_trigger after insert or delete or update on product for each row
execute function product_record_fun();

3.5 Insert data into the business table 'product'

insert into product (pro_id,pro_name,pro_type,price) values (10001,'ipad','padnote',4500);
insert into product (pro_id,pro_name,pro_type,price) values (10002,'ipad8','padnote',6000);
insert into product (pro_id,pro_name,pro_type,price) values (10003,'ipone','phone',8000);
insert into product (pro_id,pro_name,pro_type,price) values (10004,'ipone14','phone',8800);
insert into product (pro_id,pro_name,pro_type,price) values (10005,'notebook','computer',10000);
insert into product (pro_id,pro_name,pro_type,price) values (10006,'notebook2','computer',12000);

3.6 Check the product business table and the audit table product_record

select * from product;
select * from product_record;

You can see that the product_record audit table has recorded 6 newly inserted records.

3.7 Perform an update operation on the records in the product business table

update product set price=9000 where pro_id=10002;
update product set price=8500 where pro_id=10003;
update product set price=9800 where pro_id=10004;

3.8 Check the records in the product business table and the product_record audit table after the update operation

select * from product;
select * from product_record;

You can see that the product_record audit table has recorded the 3 newly updated records.

3.9 Perform a delete operation on the records in the product business table

delete from product where pro_id=10001;
delete from product where pro_id=10005;

3.10 Check the records in the product business table and the product_record audit table after the delete operation

select * from product;
select * from product_record;

You can see that the product_record audit table has recorded the 2 newly deleted records.

The above are the test cases for completing the FOR EACH ROW based trigger.

04 FOR EACH STATEMENT Trigger Case

This is a test case based on a FOR EACH STATEMENT trigger. One is the business table emp, and the other is the audit table product_record. For each row operation performed on the product table, the trigger will record each operation on the product table into the audit table product_record.

4.1 Connect to the database

[root@kunlun1 ~]# su - kunlun
[kunlun@kunlun1 ~]$ psql -h 192.168.56.112 -p 47001 postgres

4.2 Create the business table emp and the audit table emp_audit

CREATE TABLE emp (
    empid   int primary key,
    empname varchar(50),
    salary int
);

CREATE TABLE emp_audit(
    operation  char(10),
    stamp timestamp,
    userid  varchar(50),
    empid   int,
    empname  varchar(50),
    salary int
);

4.3 Create trigger function

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit
                SELECT 'DELETE', now(), user, o.* FROM old_table o;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit
                SELECT 'UPDATE', now(), user, n.* FROM new_table n;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit
                SELECT 'INSERT', now(), user, n.* FROM new_table n;
        END IF;
        RETURN NULL; 
    END;
$emp_audit$ LANGUAGE plpgsql;

4.4 Create trigger

CREATE TRIGGER emp_audit_ins
    AFTER INSERT ON emp
    REFERENCING NEW TABLE AS new_table
    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();

CREATE TRIGGER emp_audit_upd
    AFTER UPDATE ON emp
    REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();

CREATE TRIGGER emp_audit_del
    AFTER DELETE ON emp
    REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();

4.5 Insert data into the business table emp

insert into emp (empid,empname,salary) values(1001,'test',5000);
insert into emp (empid,empname,salary) values(1002,'admin',8000);
insert into emp (empid,empname,salary) values(1003,'operator',10000);
insert into emp (empid,empname,salary) values(1004,'auditor',12000);
insert into emp (empid,empname,salary) values(1005,'viewer',9000);
insert into emp (empid,empname,salary) values(1006,'test2',6000);

4.6 Check the emp business table and the audit table emp_audit

select * from emp;
select * from emp_audit;

You can see that the emp_audit audit table recorded 6 newly inserted records.

4.7 Perform an update operation on the records in the emp business table

update emp set salary=9000 where empid=1002;
update emp set salary=11000 where empid=1003;
update emp set salary=12500 where empid=1004;

4.8 Check the records in the emp business table and the emp_audit audit table after the update operation

select * from emp;
select * from emp_audit;

You can see that the emp_audit audit table recorded 3 newly updated records.

4.9 Perform a delete operation on the records in the emp business table

delete from emp where empid=1001;
delete from emp where empid=1005;
delete from emp where empid=1006;

4.10 Check the records in the emp business table and the emp_audit audit table after the delete operation

select * from emp;
select * from emp_audit;

You can see that the emp_audit audit table recorded 3 newly deleted records.

The above completes the test cases for the trigger based on FOR EACH STATEMENT.

END