Database triggers not working

Questions and Answers Database-SQL
1

Hello, I’m trying to insert a new entry into a colllection that has a trigger in the database.
I was supposed to have a trigger on INSERT, UPDATE and DELETE on that table.
The only trigger which gets non null values for foreign key fields is the UPDATE trigger.
INSERT and DELETE triggers set all foreign key values as null inside the NEW or OLD.

Why is this happening and why wont strapi send a proper query for INSERT or DELETE operations, instead it looks like it’s inserting values for non fk fields and then updating the fk fields afterwards.

This behaviour makes my INSERT or DELETE triggers not do anything at all and it’s very important for my data that they execute correctly or the data will become illogical. (Trying to have a leaderboard of matches that were played, and in case of for example deletion of a entry in matches table, I want to update the teamleaderboard table for the teams that played on those matches, specifically to adjust their wins or losses to reflect the deletion of the match row).

2

@katicstefan I faced the same issue. Just wondering if you have found the solution to this problem.

3

Hello, I figured out a workaround, I don’t know if that is still the best way to implement triggers, I didn’t work on this project since May 2021 and there may have been updates to Strapi that changed that.
What I did was create the data models in Strapi and have the CMS create the database.
After that I created my triggers on top of the database that was made by the CMS.
I didn’t manually create the tables, if you do that and create foreign keys, it will not work. So I just ‘pretend’ that the properties in the tables are foreign keys and I treated them as such.
I don’t remember what happens in the case you delete a collection entry that is associated with another collection.

TLDR; just don’t use foreign keys at all, you can setup relationships in Strapi