Updated on 2025-09-22 GMT+08:00

Trigger

Overview

A trigger is a special function that is automatically executed when a data change event (INSERT, UPDATE, DELETE, or TRUNCATE) or a database event (DDL statement) occurs. It is not called by users or applications.

Table 1 Types of triggers supported on tables and views

Trigger Time

Trigger Event

Row-Level

Statement-Level

BEFORE

INSERT/UPDATE/DELETE

Tables

Tables and views

TRUNCATE

Not supported

Tables

AFTER

INSERT/UPDATE/DELETE

Tables

Tables and views

TRUNCATE

Not supported

Tables

INSTEAD OF

INSERT/UPDATE/DELETE

Views

Not supported

TRUNCATE

Not supported

Not supported

Managing Triggers

  • Create a trigger.
    -- Create the company table.
    gaussdb=# CREATE TABLE COMPANY(
       ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);
    NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "company_pkey" for table "company"
    CREATE TABLE
    
    -- Create the audit table.
    gaussdb=# CREATE TABLE AUDIT(
       EMP_ID INT NOT NULL,
       ENTRY_DATE TEXT NOT NULL);
    CREATE TABLE
    
    -- Use the CREATE FUNCTION statement to create a trigger function.
    gaussdb=#  CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
       BEGIN
          INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
          RETURN NEW;
       END;
    $example_table$ LANGUAGE plpgsql;
    CREATE FUNCTION
    
    -- Use the CREATE TRIGGER statement to associate the function with a table.
    gaussdb=# CREATE TRIGGER example_trigger AFTER INSERT ON company FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();
    CREATE TRIGGER
    
    -- Insert data to the company table.
    gaussdb=# INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Zhang San', 32, 'Jinan', 20000.00 );
    INSERT 0 1
    
    -- View data in the audit table.
    gaussdb=# SELECT * FROM audit;
     emp_id |          entry_date           
    --------+-------------------------------
          1 | 2024-12-09 11:25:55.992644+08
    (1 row)
  • Modify a trigger.
    GaussDB provides the ALTER TRIGGER statement to modify a trigger.
    -- Rename a trigger.
    gaussdb=# ALTER TRIGGER example_trigger ON company RENAME TO example_trigger1;
    ALTER TRIGGER

    By default, a trigger is enabled. You can run the following statements to disable or enable a trigger:

    -- Disable a trigger.
    gaussdb=# ALTER TABLE company DISABLE TRIGGER example_trigger1;
    ALTER TABLE
    
    -- Insert data into the company table and view the audit table.
    gaussdb=# INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Li Si', 30, 'Anhui', 30000.00 );
    INSERT 0 1
    
    gaussdb=# SELECT * FROM audit;
     emp_id |          entry_date           
    --------+-------------------------------
          1 | 2024-12-09 11:25:55.992644+08
    (1 row)
    
    
    -- Enable a trigger.
    gaussdb=# ALTER TABLE company ENABLE TRIGGER example_trigger1 ;
  • View a trigger.
    The information_schema.triggers view stores information about triggers.
    -- Run the following meta-command to display the query result in a vertical line:
    gaussdb=# \x
    Expanded display is on.
    
    gaussdb=# SELECT * FROM information_schema.triggers WHERE trigger_name = 'example_trigger1';
    -[ RECORD 1 ]--------------+---------------------------------
    trigger_catalog            | postgres
    trigger_schema             | public
    trigger_name               | example_trigger1
    event_manipulation         | INSERT
    event_object_catalog       | postgres
    event_object_schema        | public
    event_object_table         | company
    action_order               | 
    action_condition           | 
    action_statement           | EXECUTE PROCEDURE auditlogfunc()
    action_orientation         | ROW
    action_timing              | AFTER
    action_reference_old_table | 
    action_reference_new_table | 
    action_reference_old_row   | 
    action_reference_new_row   | 
    created                    | 
    
    gaussdb=# \x
    Expanded display is off.
  • Drop a trigger.
    gaussdb=# DROP TRIGGER example_trigger1 ON company;
    DROP TRIGGER
    
    -- Drop tables.
    gaussdb=# DROP TABLE company;
    DROP TABLE
    gaussdb=# DROP TABLE audit;
    DROP TABLE