PostgreSQL: PostgreSQL触发器与事件触发器

CREATE OR REPLACE FUNCTION fn_before_order_item()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.created_at := NOW();
  NEW.line_total := NEW.quantity * NEW.unit_price;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_before_order_item
  BEFORE INSERT ON order_items
  FOR EACH ROW
  EXECUTE FUNCTION fn_before_order_item();

INSERT 0 1

CREATE OR REPLACE FUNCTION fn_audit_price_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.unit_price IS DISTINCT FROM OLD.unit_price THEN
    INSERT INTO price_audit_log
      (product_id, old_price, new_price, changed_by, changed_at)
    VALUES
      (NEW.product_id, OLD.unit_price, NEW.unit_price,
       CURRENT_USER, NOW());
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_audit_price
  AFTER UPDATE OF unit_price ON products
  FOR EACH ROW
  EXECUTE FUNCTION fn_audit_price_change();

INSERT 0 1

CREATE OR REPLACE FUNCTION fn_auto_update_timestamp()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_products_updated_at
  BEFORE UPDATE ON products
  FOR EACH ROW
  EXECUTE FUNCTION fn_auto_update_timestamp();

CREATE TABLE

CREATE OR REPLACE FUNCTION fn_refresh_order_stats()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_stats;
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_refresh_order_stats
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH STATEMENT
  EXECUTE FUNCTION fn_refresh_order_stats();

INSERT 0 1

CREATE OR REPLACE FUNCTION fn_check_stock()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  v_stock INT;
BEGIN
  SELECT stock_qty INTO v_stock
  FROM products
  WHERE product_id = NEW.product_id;

  IF v_stock < NEW.quantity THEN
    RAISE EXCEPTION 'Insufficient stock: product % has % units, requested %',
      NEW.product_id, v_stock, NEW.quantity;
  END IF;

  UPDATE products
  SET stock_qty = stock_qty - NEW.quantity
  WHERE product_id = NEW.product_id;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_check_stock
  BEFORE INSERT ON order_items
  FOR EACH ROW
  EXECUTE FUNCTION fn_check_stock();

INSERT 0 1

CREATE OR REPLACE FUNCTION fn_log_big_order()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO big_order_log (order_id, total_amount, created_at)
  VALUES (NEW.order_id, NEW.total_amount, NOW());
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_log_big_order
  AFTER INSERT ON orders
  FOR EACH ROW
  WHEN (NEW.total_amount >= 5000)
  EXECUTE FUNCTION fn_log_big_order();

INSERT 0 1

-- These triggers execute in alphabetical order: a -> b -> c
CREATE TRIGGER trg_a_validate
  BEFORE INSERT ON orders FOR EACH ROW
  EXECUTE FUNCTION fn_validate_order();

CREATE TRIGGER trg_b_calc_tax
  BEFORE INSERT ON orders FOR EACH ROW
  EXECUTE FUNCTION fn_calc_order_tax();

CREATE TRIGGER trg_c_notify
  AFTER INSERT ON orders FOR EACH ROW
  EXECUTE FUNCTION fn_notify_new_order();

INSERT 0 1

CREATE VIEW vw_customer_orders AS
SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  o.order_id,
  o.total_amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

CREATE OR REPLACE FUNCTION fn_insert_customer_order()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO customers (first_name, last_name)
  VALUES (NEW.first_name, NEW.last_name)
  ON CONFLICT DO NOTHING;

  INSERT INTO orders (customer_id, total_amount, order_date)
  SELECT customer_id, NEW.total_amount, CURRENT_DATE
  FROM customers
  WHERE first_name = NEW.first_name AND last_name = NEW.last_name;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_insert_customer_order
  INSTEAD OF INSERT ON vw_customer_orders
  FOR EACH ROW
  EXECUTE FUNCTION fn_insert_customer_order();

INSERT 0 1

CREATE OR REPLACE FUNCTION fn_update_customer_order()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE customers
  SET first_name = NEW.first_name,
      last_name = NEW.last_name
  WHERE customer_id = NEW.customer_id;

  UPDATE orders
  SET total_amount = NEW.total_amount
  WHERE order_id = NEW.order_id;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_update_customer_order
  INSTEAD OF UPDATE ON vw_customer_orders
  FOR EACH ROW
  EXECUTE FUNCTION fn_update_customer_order();

CREATE TABLE

CREATE OR REPLACE FUNCTION fn_block_drop_table()
RETURNS EVENT_TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE EXCEPTION 'DROP TABLE is not allowed in production!';
END;
$$;

CREATE EVENT TRIGGER etg_block_drop
  ON sql_drop
  WHEN tag IN ('DROP TABLE')
  EXECUTE FUNCTION fn_block_drop_table();

CREATE TABLE

CREATE TABLE ddl_audit_log (
  id SERIAL PRIMARY KEY,
  event_type TEXT,
  tag TEXT,
  object_type TEXT,
  object_name TEXT,
  command_text TEXT,
  current_user TEXT,
  event_time TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION fn_log_ddl()
RETURNS EVENT_TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  v_obj RECORD;
BEGIN
  v_obj := NULL;
  INSERT INTO ddl_audit_log (event_type, tag, object_type, object_name, current_user)
  VALUES (TG_EVENT, TG_TAG,
          v_obj.object_type, v_obj.object_identity,
          CURRENT_USER);
  RAISE NOTICE 'DDL logged: % %', TG_EVENT, TG_TAG;
END;
$$;

CREATE EVENT TRIGGER etg_log_ddl
  ON ddl_command_end
  EXECUTE FUNCTION fn_log_ddl();

INSERT 0 1

-- Disable triggers for bulk import performance
ALTER TABLE products DISABLE TRIGGER ALL;

COPY products(product_name, unit_price, category, stock_qty)
FROM '/data/products_bulk.csv' WITH (FORMAT csv, HEADER true);

-- Re-enable after import
ALTER TABLE products ENABLE TRIGGER ALL;

-- SQL 语句执行成功

-- Enforce: order total must always equal sum of line items
CREATE OR REPLACE FUNCTION fn_enforce_order_total()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  v_calculated NUMERIC;
BEGIN
  SELECT COALESCE(SUM(line_total), 0) INTO v_calculated
  FROM order_items
  WHERE order_id = NEW.order_id;

  IF v_calculated IS DISTINCT FROM (
    SELECT total_amount FROM orders WHERE order_id = NEW.order_id
  ) THEN
    UPDATE orders SET total_amount = v_calculated
    WHERE order_id = NEW.order_id;
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_enforce_order_total
  AFTER INSERT OR UPDATE ON order_items
  FOR EACH ROW
  EXECUTE FUNCTION fn_enforce_order_total();

  result  
----------
   42.50
(1 row)

-- 1. Auto-update timestamp on product changes
CREATE OR REPLACE FUNCTION fn_products_timestamp()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_products_timestamp
  BEFORE UPDATE ON products
  FOR EACH ROW
  EXECUTE FUNCTION fn_products_timestamp();

-- 2. Decrease stock on order item insert, reject if insufficient
CREATE OR REPLACE FUNCTION fn_decrease_stock()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  v_stock INT;
BEGIN
  SELECT stock_qty INTO v_stock FROM products
  WHERE product_id = NEW.product_id FOR UPDATE;

  IF v_stock IS NULL THEN
    RAISE EXCEPTION 'Product % not found', NEW.product_id;
  ELSIF v_stock < NEW.quantity THEN
    RAISE EXCEPTION 'Insufficient stock: product % (stock=%, requested=%)',
      NEW.product_id, v_stock, NEW.quantity;
  END IF;

  UPDATE products SET stock_qty = stock_qty - NEW.quantity
  WHERE product_id = NEW.product_id;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_decrease_stock
  BEFORE INSERT ON order_items
  FOR EACH ROW
  EXECUTE FUNCTION fn_decrease_stock();

-- 3. Audit log when price changes
CREATE OR REPLACE FUNCTION fn_price_audit()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.unit_price IS DISTINCT FROM OLD.unit_price THEN
    INSERT INTO price_audit_log
      (product_id, old_price, new_price, changed_by, changed_at)
    VALUES
      (NEW.product_id, OLD.unit_price, NEW.unit_price,
       CURRENT_USER, NOW());
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_price_audit
  AFTER UPDATE OF unit_price ON products
  FOR EACH ROW
  WHEN (OLD.unit_price IS DISTINCT FROM NEW.unit_price)
  EXECUTE FUNCTION fn_price_audit();