Triggers

​

Structure

CREATE TRIGGER trigger_name
  [ BEFORE | AFTER ] [ INSERT | UPDATE | DELETE ] ON table_name
  FOR EACH ROW
  [ WHEN boolean_condition ]
  EXECUTE FUNCTION function_name(arguments)

• The trigger can activate BEFORE or AFTER any combination of INSERT, UPDATE, or DELETE statements is issued on a given table.
  • FOR EACH ROW specifies a row-level trigger, which activates once for each row that is affected by the statements.
  • WHEN specifies an optional boolean condition that determines whether the trigger activates for a given row.
  • For details on the preceding behaviors, refer to Trigger conditions.
• The trigger function, written in , is executed each time the trigger activates. A comma-separated list of constant string arguments can be included.

​

Trigger conditions

CREATE TRIGGER check_value
  BEFORE INSERT OR UPDATE ON users
  ...

CREATE TRIGGER audit_address_change
  AFTER UPDATE ON users
  FOR EACH ROW
  WHEN ((OLD).address IS DISTINCT FROM (NEW).address)
  ...

​

Trigger ordering

1. All BEFORE triggers activate before all AFTER triggers.
2. BEFORE INSERT triggers activate before BEFORE UPDATE triggers.
3. The triggers activate in alphabetical order by trigger name.

​

Trigger function

• The function must return type TRIGGER.
• The function must be declared without arguments.
• The function must be written in .
• The function for a BEFORE trigger must return one of the following values:
  • The NEW table row resulting from the SQL operation that activated the trigger. This variable applies only to INSERT and UPDATE triggers, and also allows the BEFORE trigger to modify the row before it is written.
  • The OLD table row affected by the SQL operation that activated the trigger. This variable applies only to UPDATE and DELETE triggers.
  • NULL, which stops the SQL operation that activated the BEFORE trigger.
• The function for an AFTER trigger typically returns NULL by convention, because its return value will be ignored.
• The function must be defined before creating the trigger.

CREATE OR REPLACE FUNCTION function_name()
  RETURNS TRIGGER AS $$
  BEGIN
	...
  END
  $$ LANGUAGE PLpgSQL;

​

Trigger variables

​

Examples

​

Create an audit log

1. Run to start a temporary, in-memory cluster with the sample dataset preloaded:
   cockroach demo
   
2. Create a table that stores audit records. Each record includes the table that was affected, the SQL operation that was performed on the table, the old and new table rows, and the timestamp when the change was made:
   CREATE TABLE audit_log (
       id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
       table_name TEXT NOT NULL,
       operation TEXT NOT NULL,
       old_data JSONB,
       new_data JSONB,
       changed_at TIMESTAMP DEFAULT current_timestamp
   );
   
3. Create a that inserts the corresponding values into the audit_log table:
   CREATE OR REPLACE FUNCTION audit_changes()
   RETURNS TRIGGER AS $$
   BEGIN
       INSERT INTO audit_log (table_name, operation, old_data, new_data, changed_at)
       VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW), current_timestamp);
       RETURN NULL;
   END;
   $$ LANGUAGE PLpgSQL;
   
   This function inserts the following trigger variables:
   • TG_TABLE_NAME: The table associated with the trigger. In this example, this will be users.
   • TG_OP: The SQL operation that was performed on the table, thus activating the trigger.
   • OLD: The old table row affected by UPDATE and DELETE operations.
   • NEW: The new table row resulting from INSERT and UPDATE operations.
   current_timestamp generates a new timestamp each time the function is executed by the trigger.
4. Create a trigger that executes the audit_changes function after an INSERT, UPDATE, or DELETE is issued on the users table:
   CREATE TRIGGER audit_trigger
   AFTER INSERT OR UPDATE OR DELETE ON users
   FOR EACH ROW EXECUTE FUNCTION audit_changes();
   

1. Test the trigger by inserting, updating, and deleting a row in the users table of the movr database:
   INSERT INTO users (id, city, name) VALUES (uuid_generate_v4(), 'new york', 'Max Roach');
   UPDATE users SET address = '541 Greene Avenue' WHERE name = 'Max Roach';
   DELETE FROM users WHERE name = 'Max Roach';
   
   The trigger activates after each of the preceding 3 statements.
2. View the results in the audit_log table:
   SELECT * FROM audit_log ORDER BY changed_at;
   
                      id                  | table_name | operation |                                                                   old_data                                                                   |                                                                   new_data                                                                   |         changed_at
   ---------------------------------------+------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------
     70faffe0-6137-4138-9f7d-e34cf29af925 | users      | INSERT    | NULL                                                                                                                                         | {"address": null, "city": "new york", "credit_card": null, "id": "8459c2dd-bee0-4661-a8dd-f0e240d34326", "name": "Max Roach"}                | 2024-11-12 16:39:49.726472
     612f0dd0-f772-409c-bc48-265b7c0c2555 | users      | UPDATE    | {"address": null, "city": "new york", "credit_card": null, "id": "8459c2dd-bee0-4661-a8dd-f0e240d34326", "name": "Max Roach"}                | {"address": "541 Greene Avenue", "city": "new york", "credit_card": null, "id": "8459c2dd-bee0-4661-a8dd-f0e240d34326", "name": "Max Roach"} | 2024-11-12 16:39:55.53091
     903d1954-cb8a-4f36-aa4c-e34baebf098e | users      | DELETE    | {"address": "541 Greene Avenue", "city": "new york", "credit_card": null, "id": "8459c2dd-bee0-4661-a8dd-f0e240d34326", "name": "Max Roach"} | NULL                                                                                                                                         | 2024-11-12 16:40:00.899737
   (3 rows)
   
   Because OLD does not apply to INSERT operations, and NEW does not apply to DELETE operations, their corresponding old_data and new_data values are NULL, respectively. For details, refer to Trigger variables.

​

Create a summary table

1. Create the following two sample tables. products contains a list of products, and orders contains a list of orders on those products:
   CREATE TABLE products (
   	product_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
   	product_name VARCHAR(255) NOT NULL
   );
   
   CREATE TABLE orders (
       order_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
       product_id UUID NOT NULL,
       quantity INT NOT NULL,
       price NUMERIC(10, 2) NOT NULL,
       order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
       FOREIGN KEY (product_id) REFERENCES products (product_id)
   );
   
2. Create a product_sales_summary table that stores summary records. Each record includes the total number of orders and the total value of sales for each product:
   CREATE TABLE product_sales_summary (
       product_id UUID PRIMARY KEY,
       total_orders INT NOT NULL DEFAULT 0,
       total_sales NUMERIC(10, 2) NOT NULL DEFAULT 0.00,
       FOREIGN KEY (product_id) REFERENCES products (product_id)
   );
   
3. Create a that updates existing summary records, or inserts a new summary record, to reflect each order that is placed:
   CREATE OR REPLACE FUNCTION update_product_sales_summary()
   RETURNS TRIGGER AS $$
   BEGIN
       -- Check if the product already exists in the summary table
       IF EXISTS (SELECT 1 FROM product_sales_summary WHERE product_id = (NEW).product_id) THEN
           -- Update the existing summary record
           UPDATE product_sales_summary
           SET total_orders = total_orders + 1,
               total_sales = total_sales + ((NEW).quantity * (NEW).price)
           WHERE product_id = (NEW).product_id;
       ELSE
           -- Insert a (NEW) summary record
           INSERT INTO product_sales_summary (product_id, total_orders, total_sales)
           VALUES ((NEW).product_id, 1, (NEW).quantity * (NEW).price);
       END IF;
       RETURN NULL;
   END;
   $$ LANGUAGE PLpgSQL;
   
   (NEW).quantity * (NEW).price is the total value of each new order. This value is aggregated into the total_sales value in the product_sales_summary table.
4. Create a trigger that executes the update_product_sales_summary function after an INSERT is issued on the orders table (i.e., an order is placed):
   CREATE TRIGGER trg_update_product_sales_summary
   AFTER INSERT ON orders
   FOR EACH ROW
   EXECUTE FUNCTION update_product_sales_summary();
   
   Because this trigger executes the update_product_sales_summary function directly after each row is affected by a SQL operation, it spares you from having to run a potentially expensive query on those values in the orders table (e.g., SUM(quantity * price)).
5. Set up the example scenario by inserting two sample product names and creating a function to randomly generate orders on those product names:
   INSERT INTO products (product_name) VALUES ('Product A'), ('Product B');
   
   CREATE OR REPLACE FUNCTION generate_orders(num_orders INT)
   RETURNS VOID AS $$
   DECLARE
       product_id_a UUID;
       product_id_b UUID;
       order_count INT := 0;
       random_product_id UUID;
       random_quantity INT;
       random_price NUMERIC(10, 2);
   BEGIN
       -- Retrieve product IDs
       SELECT product_id INTO product_id_a FROM products WHERE product_name = 'Product A';
       SELECT product_id INTO product_id_b FROM products WHERE product_name = 'Product B';
       -- Insert orders
       WHILE order_count < num_orders LOOP
           -- Determine random product ID
           random_product_id := CASE WHEN random() < 0.5 THEN product_id_a ELSE product_id_b END;
           -- Generate random quantity and price
           random_quantity := (random() * 10)::INT + 1;
           random_price := (random() * 100)::NUMERIC(10, 2);
           -- Insert order
           INSERT INTO orders (product_id, quantity, price)
           VALUES (random_product_id, random_quantity, random_price);
           -- Increment order count
           order_count := order_count + 1;
       END LOOP;
   END;
   $$ LANGUAGE PLpgSQL;
   
6. Run the example function, generating 100 orders:
   SELECT generate_orders(100);
   
7. View some of the orders that were generated:
   SELECT * FROM orders limit 5;
   
                   order_id               |              product_id              | quantity | price |         order_date
   ---------------------------------------+--------------------------------------+----------+-------+-----------------------------
     02684068-6ff7-4f48-a1e6-c837375bb2f4 | 09b1e8d1-ed15-4777-acaa-384852f51793 |        8 | 85.60 | 2024-11-12 18:33:35.064328
     049b33b5-9db3-4748-839e-a4af2bbfe2fb | 7780c52f-9d54-4098-a824-c19efdf1b390 |        2 | 32.12 | 2024-11-12 18:33:35.064328
     05806296-442d-4dc3-84f4-c6f629fbabb9 | 7780c52f-9d54-4098-a824-c19efdf1b390 |        6 | 43.01 | 2024-11-12 18:33:35.064328
     0b362545-3e08-4c14-b42a-7d3d8013f2b6 | 09b1e8d1-ed15-4777-acaa-384852f51793 |        9 | 35.60 | 2024-11-12 18:33:35.064328
     0d6d299d-ff06-4ac2-a924-8f704f2cf916 | 7780c52f-9d54-4098-a824-c19efdf1b390 |       10 | 51.84 | 2024-11-12 18:33:35.064328
   
8. View the aggregated results on the summary table:
   SELECT * FROM product_sales_summary;
   
                  product_id              | total_orders | total_sales
   ---------------------------------------+--------------+--------------
     09b1e8d1-ed15-4777-acaa-384852f51793 |           49 |    13618.14
     7780c52f-9d54-4098-a824-c19efdf1b390 |           51 |    15594.56
   (2 rows)
   

​

Demonstrate BEFORE and AFTER trigger ordering

1. Create a sample table of employees and their wages:
   CREATE TABLE employees (
       employee_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
       name VARCHAR(100),
       wage NUMERIC(10, 2),
       created_at TIMESTAMP DEFAULT current_timestamp
   );
   
2. Create a that checks whether a new wage is below the minimum:
   CREATE OR REPLACE FUNCTION ensure_minimum_wage()
   RETURNS TRIGGER AS $$
   BEGIN
   	RAISE NOTICE 'Starting wage for employee %: %', (NEW).employee_id, (NEW).wage;
   IF (NEW).wage < 15 THEN
       RAISE EXCEPTION 'Wage cannot be below minimum';
   END IF;
   RETURN NEW;
   END;
   $$ LANGUAGE PLpgSQL;
   
   The function prints the wage that is initially assigned to the employee. If the new wage is below minimum, the function to abort the SQL operation that changes the wage. Otherwise, it returns the NEW row resulting from the SQL operation.
3. Create a trigger that executes the ensure_minimum_wage function before an INSERT or UPDATE is issued on the employees table:
   CREATE TRIGGER trg_ensure_minimum_wage
   BEFORE INSERT OR UPDATE ON employees
   FOR EACH ROW
   EXECUTE FUNCTION ensure_minimum_wage();
   
4. Create a trigger function that adds an initial starting bonus of 5 to each new wage:
   CREATE OR REPLACE FUNCTION give_bonus()
   RETURNS TRIGGER AS $$
   BEGIN
   	RAISE NOTICE 'Modifying wage for employee %: % + 5', (NEW).employee_id, (NEW).wage;
   	NEW.wage := (NEW).wage + 5;
   	RETURN NEW;
   END;
   $$ LANGUAGE PLpgSQL;
   
5. Create a trigger that executes the give_bonus function before an INSERT or UPDATE is issued on the employees table:
   CREATE TRIGGER trg_give_bonus
   BEFORE INSERT OR UPDATE ON employees
   FOR EACH ROW
   EXECUTE FUNCTION give_bonus();
   
   Both trg_ensure_minimum_wage and trg_give_bonus are BEFORE triggers that activate before any INSERT or UPDATE is issued on employees. Because trg_give_bonus comes alphabetically after trg_ensure_minimum_wage, it activates second. For details on this behavior, refer to Trigger conditions.
6. Create a trigger function that prints an employee’s final wage with the bonus applied.
   CREATE OR REPLACE FUNCTION print_final_wage()
   RETURNS TRIGGER AS $$
   BEGIN
       RAISE NOTICE 'Final wage for employee %: %', (NEW).employee_id, (NEW).wage;
       RETURN NEW;
   END;
   $$ LANGUAGE PLpgSQL;
   
7. Create a trigger that executes the print_final_wage function after an INSERT or UPDATE is issued on the employees table:
   CREATE TRIGGER trg_print_final_wage
   AFTER INSERT OR UPDATE ON employees
   FOR EACH ROW
   EXECUTE FUNCTION print_final_wage();
   
   This AFTER trigger activates after the SQL operation and both BEFORE triggers are written to employees, ensuring that it prints the final value of the row.
8. Test the triggers by adding a new employee with a wage of 20:
   INSERT INTO employees (name, wage) VALUES ('John Doe', 20);
   
   NOTICE: Starting wage for employee 7a80a11a-51ad-4acf-815f-3f5d4b820e17: 20.00
   NOTICE: Modifying wage for employee 7a80a11a-51ad-4acf-815f-3f5d4b820e17: 20.00 + 5
   NOTICE: Final wage for employee 7a80a11a-51ad-4acf-815f-3f5d4b820e17: 25.00
   INSERT 0 1
   
   This output demonstrates the following order of events:
   1. trg_ensure_minimum_wage activates before trg_give_bonus, so the “Starting wage” message is printed before the “Modifying wage” message.
   2. trg_give_bonus receives the NEW row value (20.00) returned by trg_ensure_minimum_wage, which is unmodified from the INSERT operation. After printing the “Modifying wage” message, the function adds 5 to the row value and returns a modified NEW value.
   3. The NEW value is written to the row.
   4. trg_print_final_wage prints the “Final wage” message with the committed row value (25.00).
9. Add a new employee with a wage of 10:
   INSERT INTO employees (name, wage) VALUES ('Jane Doe', 10);
   
   NOTICE: Starting wage for employee f0035967-2123-493b-9e9e-83b568fe61c4: 10.00
   ERROR: Wage cannot be below minimum
   SQLSTATE: P0001
   
   This output demonstrates the following order of events:
   1. trg_ensure_minimum_wage prints the “Starting wage” message.
   2. The row value fails the conditional check in ensure_minimum_wage, and raises an exception.
   3. The ERROR message is printed and the SQL operation is aborted before the give_bonus function is executed.

​

Video demo

​

Known limitations

• Statement-level triggers are not supported.
• INSTEAD OF triggers are not supported.
• Hidden columns are not visible to triggers.
• The REFERENCING clause for CREATE TRIGGER is not supported.
• UPDATE triggers with a column list (using UPDATE OF column_name syntax) are not supported.
• Statement-level triggers for TRUNCATE events are not supported.

​

See also