5.33. PostgreSQL trigger

The PostgreSQL trigger is the callback function of the database, which is automatically executed / called when the specified database event occurs.

Here are a few important points about PostgreSQL triggers:

PostgreSQL triggers can be triggered in the following situations:
- Before performing the operation (before checking the constraint and attempting to insert, update, or delete).
- After performing the operation (after checking the constraint and inserting, updating, or deleting).
- Update operation (when inserting, updating, or deleting a view).
The FOR EACH ROW attribute of the trigger is optional and, if selected, is called once per line when the operation is modified; instead, FOR EACH STATEMENT is selected, and the trigger marked by each statement executes once, no matter how many rows are modified.
WHEN Clause and trigger operations refer to the NEW.column-name And OLD.column-name Each row of elements can be accessed when the form is inserted, deleted, or updated. Among them column-name Is the name of the column in the table associated with the trigger.
If it exists WHEN Clause, the PostgreSQL statement will only execute WHEN The line in which the clause holds, if not WHEN Clause, the PostgreSQL statement is executed on each line.
BEFORE Or AFTER Keyword determines when to execute the trigger action, and determines whether to perform the trigger action before or after the insertion, modification, or deletion of the associated row.
The table to be modified must exist in the same database, the table or view that is attached as a trigger, and must only use the tablename Instead of database.tablename .
Constraint options are specified when a constraint trigger is created. This is the same as a regular trigger, except that you can use this constraint to adjust the time when the trigger fires. When a constraint implemented by a constraint trigger is violated, it throws an exception.

5.33.1. Grammar ¶

The basic syntax for creating a trigger is as follows:

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- 触发器逻辑....
];

Here, event_name It can be in the table mentioned. table_name On INSERT 、 DELETE And UPDATE Database operation. You can choose to specify FOR EACH ROW after the table name.

The following is in UPDATE Syntax for creating triggers on one or more specified columns of a table on an operation:

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
 -- 触发器逻辑....
];

5.33.2. Example ¶

Let’s assume a situation in which we want to be inserted into the newly created COMPANY Each record-keeping audit experiment in the table (delete and recreate if it already exists):

runoobdb=# CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

To maintain the audit experiment, we will create a file named AUDIT It’s a new watch. Whenever COMPANY When there is a new entry in the table, the log message is inserted into it:

runoobdb=# CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

在这里，ID 是 AUDIT 记录的 ID， EMP_ID 是来自 COMPANY 表的 ID， DATE 将保持 COMPANY 中记录被创建时的时间戳。所以，现在让我们在 COMPANY 表上创建一个触发器，如下所示：

runoobdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

auditlogfunc() Is a PostgreSQL program that is defined as follows:

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;

Now, let’s go COMPANY Insert data into the table:

runoobdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );

At this time COMPANY A record is inserted into the table:

meanwhile, AUDIT A record is also inserted into the table because we are inserting COMPANY A trigger is created when the table is called. Similarly, we can also create triggers when updating and deleting as needed:

emp_id |          entry_date
--------+-------------------------------
      1 | 2013-05-05 15:49:59.968+05:30
(1 row)

5.33.3. List triggers ¶

You can transfer it from pg_trigger List all triggers in the current database in the table:

runoobdb=# SELECT * FROM pg_trigger;

If you want to list the triggers for a particular table, the syntax is as follows:

runoobdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';

The results are as follows:

 tgname
-----------------
 example_trigger
(1 row)

5.33.4. Delete trigger ¶

The basic syntax for deleting a trigger is as follows:

drop trigger ${trigger_name} on ${table_of_trigger_dependent};

Delete the table above this article company Trigger on the example_trigger The instructions are:

drop trigger example_trigger on company;