Introduction to Klustron Triggers and Case Testing

Note:

Unless otherwise specified, version numbers mentioned in the text can be replaced with version numbers from any released version. Refer to Release notesfor all published versions.

Article Objective:

The main purpose of this article is to provide an overview of triggers, introduce triggers based on row-level (FOR EACH ROW) and statement-level (FOR EACH STATEMENT), describe the basic syntax for creating triggers along with important considerations, and conduct test cases for triggers based on both FOR EACH ROW and FOR EACH STATEMENT.

01 Introduction to Triggers

First, let's discuss trigger functions. Trigger functions are similar to user-defined functions, but they differ in that trigger functions don't need to be explicitly invoked in SQL statements. Instead, the database system automatically executes them when specific events occur, such as insertions, updates, or deletions on tables/views. Therefore, when creating trigger functions, users define not only the function body but also specify when the function should execute. This involves indicating which events (insert/update/delete) on which objects (tables/views) trigger the execution of the trigger function, creating a complete trigger.

Hence, triggers consist of two main components: 1. the trigger function and 2. the trigger definition (specifying when the trigger function should execute).

In Klustron, triggers are special functions that automatically execute in a database when specific operations (such as insert, update, delete) occur. Triggers can be fired at either the row-level (FOR EACH ROW) or the statement-level (FOR EACH STATEMENT), depending on the trigger's definition.

Here are the differences between FOR EACH ROW and FOR EACH STATEMENT triggers:

FOR EACH ROW Triggers:

FOR EACH ROW triggers are fired for each row affected. This means the trigger is executed when the operation affects each row in a table.
FOR EACH ROW triggers can access and manipulate data from the rows being modified, as they are triggered for each row.
FOR EACH ROW triggers are typically used when specific actions need to be performed for each affected row, such as updating related rows when a row is updated.

FOR EACH STATEMENT Triggers:

FOR EACH STATEMENT triggers are fired after the execution of a statement, not for each row individually.
FOR EACH STATEMENT triggers cannot directly access or manipulate data from the rows being modified, as they operate at the statement level.
FOR EACH STATEMENT triggers are generally used when actions need to be performed on the overall result of a statement, such as performing aggregate calculations or logging when inserting large amounts of data.

When writing triggers, it's important to choose between FOR EACH ROW and FOR EACH STATEMENT triggers based on the specific requirements and operations. Use FOR EACH ROW triggers if individual actions are needed for each row or if access to modified row data is necessary. Choose FOR EACH STATEMENT triggers if actions should be applied to the entire result of a statement without accessing specific row data.

02 Trigger Creation Syntax

Based on the previous introduction, we already understand the differences between trigger functions and regular functions: the use of predefined variable names to reference contextual data changes and the special return value type. Besides these differences, there's not much distinction from other regular functions. Here, we'll focus on trigger creation. The following is the syntax for creating triggers:

CREATE [ CONSTRAINT ] TRIGGER name -- Trigger name
        { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] } -- Timing (BEFORE/AFTER) and event triggering (e.g., insert/update/delete)
    ON table_name -- Table targeted by the trigger
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ] -- Deferral of execution
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] name } [ ... ] ] -- Referencing 'trigger parameters' as mentioned earlier
    [ FOR [ EACH ] { ROW | STATEMENT } ] -- Trigger level
    [ WHEN ( condition ) ] -- Additional condition that must be satisfied for trigger to fire
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments ) -- Trigger function

where event can be one of:

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

The syntax contains detailed comments, and readers can refer to the examples provided when explaining trigger parameters for a better understanding. Here are a few additional points to note:

Currently, Klustron does not support deferred execution of triggers (i.e., triggering the trigger function upon committing the transaction).
Although you can specify 'parameters' for the trigger function when creating a trigger, the trigger function still needs to be a parameter-less function. The passed "parameters" are actually stored in the aforementioned context and can be referenced using the system-convention name TG_ARGV.
Row triggers have a significant impact on performance when dealing with high concurrency during update operations or when modifying large amounts of data. They not only prevent the pushdown of DML statements but also result in the execution of the trigger function on a large scale. If we're only concerned about modifying specific data, we can use the WHEN clause when creating the trigger. This way, the trigger function is executed only when the specified condition is met, reducing the performance impact.
Currently, Klustron requires that the table on which a trigger is created must have a primary key (this restriction might be lifted in future versions).
If a statement-level trigger is created on a partitioned table, it will only trigger when the partitioned table is explicitly updated. Individual updates to the subtables of the partitioned table will not trigger the statement-level trigger. However, if a row-level trigger is created on a partitioned table, it will trigger when either the partitioned table or an individual subtable is updated.

03 FOR EACH ROW Trigger Example

This is a test case for a trigger based on the FOR EACH ROW trigger type. We have a business table named "product" and an audit table named "product_record." Whenever an operation is performed on a row in the "product" table, the trigger will record each operation on the "product" table into the "product_record" audit table.

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 the 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 the 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 View the records in the "product" business table and the "product_record" audit table.

select * from product;
select * from product_record;

You can observe that the "product_record" audit table records 6 new entries corresponding to the inserted data.

3.7 Perform an update operation on 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 View 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 observe that the "product_record" audit table records 3 new entries corresponding to the update operation.

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

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

3.10 View 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 observe that the "product_record" audit table records 2 new entries corresponding to the delete operation.

The above steps complete the testing case for a trigger based on the FOR EACH ROW trigger type.

04 FOR EACH STATEMENT Trigger Example

This is a test case for a trigger based on the FOR EACH STATEMENT trigger type. We have a business table named "emp" and an audit table named "emp_audit." Whenever an operation is performed on the "emp" table, the trigger will record each operation on the "emp" table into the "emp_audit" audit table.

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 the 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 the 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 View the records in the "emp" business table and the "emp_audit" audit table.

select * from emp;
select * from emp_audit;

You can observe that the "emp_audit" audit table records 6 new entries corresponding to the inserted data.

4.7 Perform an update operation on 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 View 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 observe that the "emp_audit" audit table records 3 new entries corresponding to the update operation.

4.9 Perform a delete operation on 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 View 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 observe that the "emp_audit" audit table records 3 new entries corresponding to the delete operation.

The above steps complete the testing case for a trigger based on the FOR EACH STATEMENT trigger type.

Introduction to Klustron Triggers and Case Testing

# Introduction to Klustron Triggers and Case Testing

# 01 Introduction to Triggers

# 02 Trigger Creation Syntax

# 03 FOR EACH ROW Trigger Example

# 04 FOR EACH STATEMENT Trigger Example

# END

Introduction to Klustron Triggers and Case Testing

01 Introduction to Triggers

02 Trigger Creation Syntax

03 FOR EACH ROW Trigger Example

04 FOR EACH STATEMENT Trigger Example

END