Wasp•3w ago

Best way of creating a trigger to update a table

I want to create a trigger so I can update a row on a different table when a new row is inserted or update on another table. What's the best way of doing that in a wasp app?

3 Replies

kapa.ai•3w ago

Hi @CamBlackwood. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave: ---------------------- In Wasp, there's no direct concept of database triggers like you might find in traditional SQL databases. However, you can achieve similar functionality using Wasp's Actions, which allow you to perform operations on your database entities. Here's how you can update a row in one table when a row is inserted or updated in another table: Using Actions The most straightforward approach is to handle this logic within your Actions. When you create or update an entity, you can also update related entities in the same operation.

export const createTask = async (args, context) => {
  // First, create the new task
  const newTask = await context.entities.Task.create({
    data: {
      description: args.description,
      isDone: false,
    },
  })
  
  // Then, update a row in another table
  await context.entities.AnotherEntity.update({
    where: { id: someId },
    data: { someField: someValue },
  })
  
  return newTask
}

export const createTask = async (args, context) => {
  // First, create the new task
  const newTask = await context.entities.Task.create({
    data: {
      description: args.description,
      isDone: false,
    },
  })
  
  // Then, update a row in another table
  await context.entities.AnotherEntity.update({
    where: { id: someId },
    data: { someField: someValue },
  })
  
  return newTask
}

This approach ensures that both operations happen within the same transaction, which is important for data consistency. Using Prisma's API Since Wasp uses Prisma under the hood, you can leverage Prisma's API to perform more complex operations. For example, you can use Prisma's updateMany to update fields by referencing other fields:

await context.entities.User.updateMany({
  where: {
    // Your condition here
  },
  data: {
    credits: { set: context.entities.User.creditAllowance }
  }
})

await context.entities.User.updateMany({
  where: {
    // Your condition here
  },
  data: {
    credits: { set: context.entities.User.creditAllowance }
  }
})

Automatic CRUD Operations If you're using Wasp's Automatic CRUD operations, you might need to override the default implementation to include your custom logic:

crud Tasks {
  entity: Task,
  operations: {
    create: {
      overrideFn: import { createTaskWithUpdate } from "@src/actions"
    },
    // other operations...
  },
}

crud Tasks {
  entity: Task,
  operations: {
    create: {
      overrideFn: import { createTaskWithUpdate } from "@src/actions"
    },
    // other operations...
  },
}

Then in your implementation:

export const createTaskWithUpdate = async (args, context) => {
  // Create the task
  const task = await context.entities.Task.create({ data: args.data })
  
  // Update the other table
  await context.entities.OtherTable.update({
    where: { someField: someValue },
    data: { updatedField: newValue }
  })
  
  return task
}

export const createTaskWithUpdate = async (args, context) => {
  // Create the task
  const task = await context.entities.Task.create({ data: args.data })
  
  // Update the other table
  await context.entities.OtherTable.update({
    where: { someField: someValue },
    data: { updatedField: newValue }
  })
  
  return task
}

Note that Wasp's CRUD operations are currently in early preview, and there are plans to enhance them in the future to be more aware of business logic and authorization rules. Remember that when you modify entities in your actions, Wasp will automatically invalidate any queries that use those entities, keeping your UI in sync with your data changes. GitHub Issue: Wasp is working on reducing boilerplate for similar operations, which might make this kind of pattern even easier in the future. You can follow this GitHub issue for updates. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ---------------------- Sorry @CamBlackwood, I seem to be having a bit of trouble. Don't worry though, I've pinged my support and they will try to fix me up! :technologist: Perhaps try again in 15mins?

Carlos•3w ago

You have some options: - Handling at the application level: That' basically what the bot recommended, making sure to always make the update in the second table manually after you do the first one. - DB Triggers: Postgres allows this, but Prisma doesn't. Fortunately, you can just write the SQL for it in a new migration: run wasp db migrate-dev --create-only and it will create a new SQL file you can edit and add there the code to set up your trigger. However, Prisma will not be aware of it. - DB View: If the data in the second table is 100% derived from other tables, you might want to use instead a Postgres VIEW or MATERIALIZED VIEW. Again Prisma does not have explicit support for it, but they do have a preview extension and a tutorial on how to make it play nice: https://www.prisma.io/docs/orm/prisma-schema/data-model/views

CamBlackwoodOP•3w ago

Thanks @Carlos ! Yeah, I think it is probably most simple to handle at the application level (I already am doing it in a few places, but this is a clean slate so I wondered what alternative solutions I could use) - I'm doing a bit of a rewrite of the backend and it's making my head hurt 😂

Gaming

Programming

Best way of creating a trigger to update a table

Did you find this page helpful?