Search code examples
mysqldatabasedatabase-trigger

Is there a possibility to use multiple Before or After statements in Database trigger

I am trying to update parent tables whenever a child table is updated. I am able to create a trigger when there is only one child table.

I have a use case where I have multiple child tables and I need to update the parent table when any one of the tables is updated. Is there a possibility I can add multiple Before or After statements in the trigger? (or) should I create separate triggers for each table separately?

Below is my Table and Trigger information

CREATE TABLE books ( id integer, title varchar(100) NOT NULL, author varchar(100) NOT NULL, episode_id integer, chapter_id integer, published_date datetime NOT NULL, child_table_updated datetime );
CREATE TABLE episode ( id integer, title varchar(100) NOT NULL, episode_name varchar(100) NOT NULL, published_date datetime NOT NULL );
CREATE TABLE chapter ( id integer, title varchar(100) NOT NULL, chapter_name varchar(100) NOT NULL, published_date datetime NOT NULL );

INSERT INTO books VALUES 
(1, 'My First SQL Book', 'Mary Parker', 1, 1, now(), now()),
(2, 'My First SQL Book', 'Mary Parker', 2, 1, now(), now()),
(3, 'My First SQL Book', 'Mary Parker', 1, 2, now(), now());

INSERT INTO episode VALUES 
(1, 'Episode One', 'Simple Episode', now()),
(2, 'Episode Tow', 'Complicated Episode', now());

INSERT INTO chapter VALUES 
(1, 'Chapter One', 'Simple Chapter', now()),
(2, 'Chapter Tow', 'Complicated Chapter', now());


CREATE TRIGGER updateBooksOnEpisodeUpdate 
    BEFORE UPDATE ON episode
    FOR EACH ROW 
 UPDATE books
 SET published_date = NOW() WHERE old.id=books.episode_id;

When I update any of the existing rows in the episode table, the child_table_updated column is getting updated.

I am looking for an option to add in the trigger if any row is updated in episodes/chapter tables, I would like to update the books table. I can create a separate trigger on chapter table but looking if we can do that in a single trigger and is it a best solution?

Also one more question, does having too many triggers is going to be an issue in databases? (For Ex: If i have around 200 tables and have a trigger for around 120 tables, is it going to be any issue)

Is there any other better option we can use to achieve this functionality without triggers?

Any help is appreciated!

Solution

  • You will have to define a trigger for each child table.

    A trigger is defined for one action on one table. You cannot define a single trigger that is executed if the action occurs on any of several tables.

    does having too many triggers is going to be an issue in databases?

    I have benchmarked triggers and I find the performance cost is proportional to the number of index writes, regardless of using triggers or not.

    For example, if you do an UPDATE against a table with 6 indexes, it has to write changes for each of those indexes. The cost is greater than a table with 3 indexes.

    If you do an UPDATE against a table with 3 indexes, and that causes a trigger to run, which does another UPDATE against a different table with 3 indexes, there are a total of 6 indexes being updated.

    This is approximately the same performance cost as updating a single table with 6 indexes and no triggers.

    Count the number of index writes for the action that spawned the trigger, plus the writes executed by the trigger.

    There are some other slight overhead to running a trigger, but it's negligible compared to the number of index writes.