Question 88959 in PostgreSQL Question posted in
The official documentation can be found here.

Error: P1014 The underlying table for model `customers` does not exist – Postgresql

I’m trying to execute the triggers.sql file. Below is my prisma.schema and triggers.sql file code. I’ve already migrate the prisma schema and generated it as well.
So when I’m running the command npx prisma db execute --file ./prisma/triggers.sql in the terminal I get the above error.
From the error perspective, it seems like I did not have the customers table. But when I checked it on the pg admin, it was showing there with all the required columns.

schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Customers {
  cust_id     Int    @id @default(autoincrement())
  name        String
  address     String
  phoneNumber String
  Items       Int
}

model CustomersAuditTable {
  audit_id  Int      @id @default(autoincrement())
  tableName String
  rowId     Int
  operation String
  timestamp DateTime @default(now())
  userId    Int
}

triggers.sql

-- 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.id, 'delete', current_user_id);

    ELSE IF (TG_OP = 'UPDATE') THEN
        INSERT INTO CustomersAuditTable (tableName, rowId, operation, userId)
        VALUES ('customer', NEW.id, 'update', current_user_id);

    ELSE
        INSERT INTO CustomersAuditTable (tableName, rowId, operation, userId)
        VALUES('customer', NEW,id, 'insert', current_user_id);
    END IF;
    END IF;

    RETURN NEW;
    END;
    $$
    LANGUAGE 'plpgsql';


-- Creating a trigger
CREATE TRIGGER customers_audit
    AFTER INSERT OR UPDATE OR DELETE ON Customers
    FOR EACH ROW
    EXECUTE FUNCTION audit_customers();
Tags:

2

Answers


  1. 0

    If you don’t select the schema (SET search_path TO –schema–;) you must reference for the full name.

    CREATE TRIGGER --schema--.customers_audit
    AFTER INSERT OR UPDATE OR DELETE ON --schema--.Customers
    FOR EACH ROW
    EXECUTE FUNCTION --schema--.audit_customers();

    Greetings, Alejandro

    Login or Signup to reply.
  2. 0

    Try changing the id to cust_id in the triggers.sql file and running the

    npx prisma db execute --file ./prisma/triggers.sql command again.

    Additionally, you might want to uncomment the line

    CREATE EXTENSION IF NOT EXISTS "pg_triggers";

    in the triggers.sql file, as it may be required for trigger functionality.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search