Kysely•2y ago

Triggers vs stored procedures

Hey everyone, I don't think this is related to Kysely directly, so excuse me but this is the only SQL type of server I am in. I am wondering what is the best way to automatically update a table attribute based on when another attribute changes (in the same table or another table). For example, if I want to have the attributes updatedAt and lastLogin updated on their own from the Model below

model User {
  id           Int           @id @default(autoincrement()) 
  createdAt    DateTime      @default(now()) @db.Date 
  updatedAt    DateTime      @db.Timestamp() 
  lastLogIn    DateTime?     @db.Timestamp()
...

model User {
  id           Int           @id @default(autoincrement()) 
  createdAt    DateTime      @default(now()) @db.Date 
  updatedAt    DateTime      @db.Timestamp() 
  lastLogIn    DateTime?     @db.Timestamp()
...

I need these values determines automatically. I have some sources saying use Triggers and some saying just have functions that you can invoke yourself in server side logic. Based on an article, this seems to be the main con of triggers

You don't see where the trigger is invoked by examining code alone without knowing they exist. You see their effect when you see the data changes and it is sometimes puzzling to figure out why the change occurred unless you know there is a trigger or more acting on the table(s).

You don't see where the trigger is invoked by examining code alone without knowing they exist. You see their effect when you see the data changes and it is sometimes puzzling to figure out why the change occurred unless you know there is a trigger or more acting on the table(s).

and the main pro seems to be that

it's enforced by the database so you won't have data integrity problems

it's enforced by the database so you won't have data integrity problems

whereas, functions on the server have a chance of not running due to an errorr occuring right before the attributes updatedAt, lastLogin are updated

11 Replies

NazCodelandOP•2y ago

Any input would be appreciated

koskimas•2y ago

Triggers can indeed be confusing, especially later once their creation is buried in one of the 500 migrations you have. This is obviously just a matter of preference, but I usually do this in the typescript code. I have a "database access layer" that exposes higher level functions for accessing the db. This layer takes care of using Kysely and also doing this kind of denormalization. You could also ask yourself, do you need those computed columns in the database? Are they just duplicate information? In some cases it's better to calculate the values on request and not store duplicate data. If the computation is heavy, then it might make sense to store it in the DB.

NazCodelandOP•2y ago

Thank you for help @koskimas, I like the idea of a "database access layer", I will try to implement that. I think some columns need to be computed when an event occures, such as "lastLogin". This brings up another thing I have been unsure about, say, I have 3 tables. Business table, Working Hours table, and Bookings Table. Business table contains a FK to the Working hours table and to the Bookings Table. I need access to the Working Hours table data from Bookings table. I can add a FK inside the Bookings table that points to the Working Hours table or I can query the Business table and from there query the Working Hours table. in situations like these, I've opted for including a FK in the Bookings table to the Working Hours table and my thinking is that, it will be better performance. But I am sure if that is the best practice or not

Igal•2y ago

FK or not, make sure things are indexed properly.

NazCodelandOP•2y ago

Thanks for sharing that, I looked into it a bit and it seems like @unique in prisma creates indexes automatically but I added a few more @@index([attributeName]) Thank you guys!

Unknown User•16mo ago

Message Not Public

Igal•16mo ago

Why are you doing the change in the db directly?

Unknown User•16mo ago

Message Not Public

bombillazo•16mo ago

Sounds like something migrations would be used for?

Unknown User•16mo ago

Message Not Public

koskimas•16mo ago

I think it's just that there's not much to say if you need to be able to modify the db directly. You have no option but to use triggers in that case. What else could you possibly do?

Gaming

Programming

Triggers vs stored procedures