Mysql – Create trigger to insert rows from one table to another with sum operation (group by)

MySQLtrigger

I have two tables in my database traffic_shift and traffic_daily. With following columns.

Table traffic_shift :-

day     month    year    toll_id    shift    car_single    car_return 
 1       9        2016    1          1        10            5
 1       9        2016    1          2        100           50
 1       9        2016    1          3        200           100

 1       9        2016    2          1        15            20
 1       9        2016    2          2        30            40
 1       9        2016    2          3        45            60

 2       9        2016    1          1        12            6
 2       9        2016    1          2        24            12
 2       9        2016    1          3        36            18

 2       9        2016    2          1        18            9
 2       9        2016    2          2        36            18
 2       9        2016    2          3        54            27

traffic_daily table will be like this,

Table traffic_daily

day     month    year    toll_id    car_single    car_return 
 1       9       2016     1          310          155
 1       9       2016     2          90           120
 2       9       2016     1          60           30
 2       9       2016     2          108          54

As you can see there are 3 shift in a day for any toll. So the functioning of the trigger should be :

it should sum up the car_single and car_return, date wise for each toll and should insert into the table traffic_daily as shown in above table.

This is what I have so far:

DROP TRIGGER after_insert_shift;
DELIMITER $$

CREATE TRIGGER `test`.`after_insert_shift` 
AFTER INSERT ON `test`.`shift`
FOR EACH ROW 
BEGIN
INSERT INTO daily   
SET
`day` = new.day,
`month` = new.month,
`year`= new.year,
toll_id = new.toll_id,
car_single = SELECT SUM(car_single) 
    WHERE daily.day = new.day , daily.month = new.month, daily.year = new.month ,
    daily.toll_id = new.toll_id FROM shift GROUP BY 
    shift.day,shift.month,shift.year,shift.toll_id;

car_return = SELECT SUM(car_return) WHERE daily.day = new.day , 
    daily.month = new.month, daily.year = new.month , 
    daily.toll_id = new.toll_id FROM shift GROUP BY
    shift.day,shift.month,shift.year,shift.toll_id;


END$$

DELIMITER ;

Best Answer

I would recommend against using a trigger as a solution. In general, if you are going to INSERT, UPDATE, or DELETE with a trigger, you will can experience problems with concurrency, data quality, and maintainability. I recommend you read this Ask TOM article. Consider the following scenarios:

  • One more rows in the traffic_shift table are updated. You will need another trigger as others have pointed out.
  • Rows are deleted from the traffic_shift table. Do you delete the data in traffic_daily? What if only a shift is removed? Another trigger with even more logic.
  • Inserts into the traffic_shift table could happen concurrently. Unless you lock the traffic_daily table, one of the triggers will fail.

At this point, you are duplicating some of the functionality of the underlying DBMS in your trigger. Plus you could have 3 triggers that are manipulating data behind the scenes. I would be willing to bet money that this solution will be annoying to maintain. Aside from those concerns, traffic_daily would contain incorrect data if you have less than 3 shifts of data.

If you want to go ahead with a trigger based approach, this Ask TOM article has an Oracle based solution that is similar to your design and I think it can be adapted to MySQL. What I like about this approach, is that the trigger can be written to only update traffic_daily only if the day exists in traffic_daily. That would prevent incomplete data from going into traffic_daily. Also, because the trigger is not inserting a row, you do not need to worry about concurrency. Even if two triggers update simultaneously, they will be updating with the same data (if you enforce a 3 shifts requirement).

The content of your traffic_daily table is derived from the traffic_shift table. When I see the word "derived" my first thought is to implement that as a view. A view will keep traffic_daily in sync with the underlying without needing a trigger. There are two factors that you need to be evaluated for a view-based solution:

  • Will data from the traffic_shift table be archived or purged?
  • What is the size and rate of growth of the traffic_shift table?

No Archive/Purge of traffic_shift

You can implement traffic_daily as a view. The type of view will depend on the rate of growth.

Archive/Purge of traffic_shift

Assuming you want to retain the summary data in traffic_daily, one solution would be to have a table containing the traffic_daily rows of data that has been archived. You then have a view with the current traffic_daily data. If you want, you can have a view that is the union of the archived traffic_daily and current traffic_daily.

Small traffic_shift size

If you have on the order of 100 or less unique toll_ids, the traffic_shift table will be 100,000 rows/year or less. At that size, a regular view would probably work in terms of performance.

Large traffic_shift size

If you have on the order of 1000 or more unique toll_ids, the traffic_shift table may be getting large enough to slow a view down (depending on the size of the database server). At that scale, a materialized view would probably be a better solution. Materialized views do not exist in all versions of MySQL, but you can read more in the MySQL version 8.0 documentation.

Other Thoughts

The view can enforce the requirement that there are 3 shifts of data for each toll_id. If you want to view incomplete sets of data, you could have a column in the view with the number of shifts in the sum. The view solution keeps the business requirements for traffic_daily in one place rather than spread out over multiple triggers.

Related Question