0

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 ;
0
2

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.

0

First observation, FROM is usually before WHERE, i.e.

car_single = SELECT SUM(car_single) FROM shift WHERE ... 

Second observation, INSERTnormally have the form:

INSERT INTO daily (day, month, ... ) 
VALUES (...)

These are the syntactical errors I find in your trigger. Logically I think there is more to do. Your trigger will insert a new row into daily every time a new row is inserted into shift, from your description that does not sound right. Here's what I would try to do (only a sketch).

CREATE TRIGGER `test`.`after_insert_shift` 
AFTER INSERT ON `test`.`shift`
FOR EACH ROW 
BEGIN
    UPDATE daily SET (car_single, car_return) = (
        SELECT SUM(car_single), SUM(car_return)
        FROM shift
        WHERE day = new.day 
          AND daily.month = new.month
          AND daily.year = new.year 
          AND ...
    )
    WHERE daily.month = new.month
      AND daily.year = new.year
      AND ...  

    -- Check if number of rows updated = 0, if so insert a new row
    SELECT ROW_COUNT() INTO N;
    IF N = 0 THEN
        INSERT INTO daily (c1, c2, c3, ...)
        SELECT ...

Don't know if that's your problem, but I would start there.

1
  • First of all syntactical errors was mistake that i made while writing the question. And you got it right, when ran my query it was creating a new row every time when there was a insert into daily table. Sep 19 '16 at 9:09
0

Use INSERT ... ON DUPLICATE KEY UPDATE ... with a suitable unique (or primary) key. That will create a new row when necessary, otherwise increment the existing row.

A tip: Do not split dates into 3 columns; instead use the DATE datatype.

0

No SUM'ing required here - use the Triggers keep things up to date as you go along.

Create Insert and Update Triggers on traffic_shift to update traffic_daily with the values given to the trigger:

update daily set 
  car_return = car_return + new.car_return - old.car_return 
, car_single = car_single + new.car_single - old.car_single 
where day   = new.day 
  and month = new.month
  and year  = new.year ;

For the Insert Trigger, lose the references to "old.".

If you need a Delete Trigger as well, lose the references to "new.".

And, obviously, all three Triggers have to deal with needing to create a new record in traffic_daily. :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.