执行Prisma触发器SQL遇P1014错误：customers表不存在但实际存在

修正后的triggers.sql #

-- 按需取消注释并设置用户ID
-- SET LOCAL current_user_id = 'malay_jeena';
-- CREATE EXTENSION IF NOT EXISTS "pg_triggers";

CREATE OR REPLACE FUNCTION audit_customers() RETURNS TRIGGER AS
$$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO "CustomersAuditTable" (tableName, rowId, operation, userId)
        VALUES ('customer', OLD.cust_id, 'delete', current_user); -- 替换为current_user或恢复current_user_id
    ELSIF (TG_OP = 'UPDATE') THEN -- 注意是ELSIF，避免嵌套IF的语法问题
        INSERT INTO "CustomersAuditTable" (tableName, rowId, operation, userId)
        VALUES ('customer', NEW.cust_id, 'update', current_user);
    ELSE
        INSERT INTO "CustomersAuditTable" (tableName, rowId, operation, userId)
        VALUES('customer', NEW.cust_id, 'insert', current_user); -- 修正NEW,id的语法错误
    END IF;

    RETURN NEW;
END;
$$
LANGUAGE plpgsql; -- 无需给语言名加引号

CREATE TRIGGER customers_audit
    AFTER INSERT OR UPDATE OR DELETE ON customers -- 匹配实际表名，大写则用"Customers"
    FOR EACH ROW
    EXECUTE FUNCTION audit_customers();   

额外验证步骤 #

1. 执行npx prisma migrate deploy确保表结构正确同步到数据库
2. 在pg admin中确认customers和customersaudittable（或"CustomersAuditTable"）表存在
3. 重新执行npx prisma db execute --file ./prisma/triggers.sql

内容的提问来源于stack exchange，提问作者Malay Jeena