← Back to PostgreSQL Menu | 🏠 Hub
-- Step 1: Create trigger function
-- Returns TRIGGER type, uses special variables: NEW, OLD
CREATE OR REPLACE FUNCTION fn_set_timestamps()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
NEW.created_at := NOW();
NEW.updated_at := NOW();
ELSIF TG_OP = 'UPDATE' THEN
NEW.updated_at := NOW();
-- Cannot change created_at on update
NEW.created_at := OLD.created_at;
END IF;
RETURN NEW; -- BEFORE trigger must return NEW (or NULL to abort)
END;
$$ LANGUAGE plpgsql;
-- Step 2: Attach trigger to table
CREATE TRIGGER trg_users_timestamps
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION fn_set_timestamps();
-- Now test:
INSERT INTO users(name, email) VALUES ('Ko Ko', 'ko@example.com');
-- created_at and updated_at are auto-set!
UPDATE users SET name = 'Ko Ko Updated' WHERE id = 1;
-- updated_at changes, created_at stays the same
-- Audit log table
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
table_name TEXT NOT NULL,
operation TEXT NOT NULL, -- INSERT/UPDATE/DELETE
old_data JSONB,
new_data JSONB,
changed_by TEXT, -- app user
changed_at TIMESTAMPTZ DEFAULT NOW()
);
-- Audit trigger function
CREATE OR REPLACE FUNCTION fn_audit_log()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO audit_log(table_name, operation, old_data, new_data, changed_by)
VALUES (
TG_TABLE_NAME, -- automatic: table name
TG_OP, -- automatic: INSERT/UPDATE/DELETE
CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) ELSE NULL END,
CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) ELSE NULL END,
current_user -- DB user (or app can set session var)
);
RETURN NEW; -- AFTER trigger: return value ignored, but use NEW/OLD
END;
$$ LANGUAGE plpgsql;
-- Attach to orders table
CREATE TRIGGER trg_orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION fn_audit_log();
-- Test
INSERT INTO orders(user_id, total, status) VALUES (1, 99.99, 'pending');
UPDATE orders SET status = 'confirmed' WHERE id = 1;
DELETE FROM orders WHERE id = 1;
-- View audit trail
SELECT * FROM audit_log WHERE table_name = 'orders' ORDER BY changed_at;
-- Normalize email before save
CREATE OR REPLACE FUNCTION fn_normalize_user_data()
RETURNS TRIGGER AS $$
BEGIN
-- Normalize email to lowercase
NEW.email := LOWER(TRIM(NEW.email));
-- Trim whitespace from name
NEW.name := TRIM(NEW.name);
-- Validate email format
IF NEW.email !~ '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$' THEN
RAISE EXCEPTION 'Invalid email format: %', NEW.email
USING ERRCODE = 'check_violation';
END IF;
-- Auto-set username if not provided
IF NEW.username IS NULL THEN
NEW.username := SPLIT_PART(NEW.email, '@', 1);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_users_normalize
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION fn_normalize_user_data();
-- Stock validation trigger
CREATE OR REPLACE FUNCTION fn_check_stock()
RETURNS TRIGGER AS $$
DECLARE
v_stock INTEGER;
BEGIN
SELECT stock INTO v_stock FROM products WHERE id = NEW.product_id;
IF v_stock < NEW.quantity THEN
RAISE EXCEPTION 'Insufficient stock: available=%, requested=%',
v_stock, NEW.quantity
USING ERRCODE = 'check_violation';
END IF;
-- Reduce stock
UPDATE products SET stock = stock - NEW.quantity WHERE id = NEW.product_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_order_items_stock
BEFORE INSERT ON order_items
FOR EACH ROW
EXECUTE FUNCTION fn_check_stock();
-- Set session-level user context (from application)
-- In Node.js:
-- await pool.query("SET app.current_user = $1", [userId]);
CREATE OR REPLACE FUNCTION fn_audit_with_app_user()
RETURNS TRIGGER AS $$
DECLARE
v_app_user TEXT;
BEGIN
-- Get app-level user from session variable
BEGIN
v_app_user := current_setting('app.current_user');
EXCEPTION WHEN OTHERS THEN
v_app_user := current_user; -- fallback to DB user
END;
INSERT INTO audit_log(table_name, operation, old_data, new_data, changed_by)
VALUES (
TG_TABLE_NAME, TG_OP,
CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) ELSE NULL END,
CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) ELSE NULL END,
v_app_user
);
RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;
-- List triggers SELECT trigger_name, event_manipulation, event_object_table, action_timing FROM information_schema.triggers WHERE trigger_schema = 'public' ORDER BY event_object_table, trigger_name; -- Disable/Enable trigger ALTER TABLE orders DISABLE TRIGGER trg_orders_audit; ALTER TABLE orders ENABLE TRIGGER trg_orders_audit; -- Disable ALL triggers on table (bulk data load) ALTER TABLE orders DISABLE TRIGGER ALL; -- ... bulk insert ... ALTER TABLE orders ENABLE TRIGGER ALL; -- Drop trigger DROP TRIGGER IF EXISTS trg_users_timestamps ON users; DROP FUNCTION IF EXISTS fn_set_timestamps(); -- STATEMENT level trigger (once per SQL, not per row) CREATE TRIGGER trg_bulk_import_log AFTER INSERT ON products FOR EACH STATEMENT EXECUTE FUNCTION fn_log_bulk_import();