Is MySQL trigger the way to go?

Question

I'm creating a mysql db in which to store files of different formats and sizes like pdfs, imgs, zips and whatnot.

So I started looking for examples on the blob data type (which I think is the right data type for storing the above mentioned files) and I stumbled upon this SOquestion. Essentially what the answer suggests is not to store the blob files directly into the "main" table but create two different tables, one for the file description and the other for the blobs themselves (as these can be heavy to get). And connect these tables by a foreign key constraint to tie the file to its description and do a join operation to retrieve the wanted blob if needed.

So I've created the following tables:

create table if not exists file_description(
    id int auto_increment primary key,
    description_ varchar(200) not null,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) engine=INNODB;

create table if not exists files(
    id int auto_increment primary key,
    content longblob not null,
    format_extension varchar(10) not null,
    foreign key (id) references file_description(id)
    on update cascade
    on delete cascade
)engine=INNODB;

But how can I enforce that after each insertion into the file_description table directly follows an insertion into the files table?

I'm no expert but for what I've seen on triggers they are used in a different way than what I would like to do here. Something like

create trigger whatever 

on file_description after insert

...

I don't know, how do I do that?

My rule of thumb: files go in the file store, data in the database. — Tangentially Perpendicular, Oct 29 '21 at 21:37
files are only saved in the databse, when the have a limit as bigger tables mean slower queries — nbk, Oct 29 '21 at 22:23
Does this answer your question? [What is the best way to store media files on a database?](https://stackoverflow.com/questions/154707/what-is-the-best-way-to-store-media-files-on-a-database) — danblack, Oct 29 '21 at 23:03
@danblack thanks! Definitely saving this in my bookmarks but I was asking a different thing — IDK, Oct 30 '21 at 05:34

score 3 · Accepted Answer · answered Oct 29 '21 at 22:34

You cannot enforce through database tools that an insertion into a parent table is followed by an insertion into a child table as the data to be inserted come from outside of the database. You need to design your application in a way that it populates both tables right after each other.

What the application can do is to encapsulate the two insert statements into a single transaction ensuring that either both inserts succeed or both are rolled back leaving your database in a consistent state.

Is MySQL trigger the way to go?

1 Answers1