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

updatedAt

and

lastLogin

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

updatedAt

lastLogin

lastLogin

are updated

Triggers vs stored procedures

updatedAt

updatedAt

and

lastLogin

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()
...

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

updatedAt

lastLogin

lastLogin

are updated

Triggers vs stored procedures

Similar Threads

Triggers vs stored procedures

Similar Threads

Similar Threads

Similar Threads