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.
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
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot