MySQL triggers fires too many times

I have a view in mysql which is made of three tables unioned together:

CREATE VIEW program_operator_jct_view AS
select
        program_id, 
        operator_code,
        'PROGRAM_OPERATOR' AS type
    from 
        program_operator_jct
UNION
    (select 
        program_id, 
        operator_code,
        'PROGRAM_GROUP' AS type
    from 
        program_operator_group_jct pg_jct,
        operator_group_jct og_jct
    where
        pg_jct.group_id = og_jct.group_id)

From this view, I create a summary table for increased performance, which is indexed so my results from this summary table can be returned via covering indexes:

CREATE TABLE `program_operator_jct_summary` (
  `program_id` int(7) NOT NULL,
  `operator_code` varchar(6) NOT NULL,
  PRIMARY KEY (`program_id`,`operator_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


//BUILD SUMMARY PROCEDURE
delimiter //
CREATE PROCEDURE update_program_operator_jct_summary ()
BEGIN
DELETE FROM program_operator_jct_summary;
INSERT INTO program_operator_jct_summary select DISTINCT program_id, operator_code from program_operator_jct_view;
INSERT INTO trigger_record (name) VALUES ('update_program_operator_jct_summary');
END
//

I attached this procedure to the insert, update and delete triggers of the underlining tables which make up the summary table:

-program_operator_jct

-program_operator_group_jct

-operator_group_jct

EXAMPLE:

delimiter //
CREATE TRIGGER trigger_program_operator_jct_insert AFTER INSERT ON program_operator_j开发者_StackOverflowct
FOR EACH ROW 
BEGIN
CALL update_program_operator_jct_summary ();
END
//

Here's my problem when I add (5) operators to the program_operator_jct:

INSERT INTO program_operator_jct (program_id, operator_code) VALUES 
('112', '000500'), 
('112', '000432'), 
('112', '000501'), 
('112', '000264'), 
('112', '000184')

This trigger runs (5) times, if I add 100 operators this trigger runs 100 times. This is a nice place to use triggers because I don't have to worry about the summary table being out of date with the underlining tables.

However rebuilding a summary table for each value in an extended inserts is way too much of a performance hit (sometimes I add hundreds of operators to programs at a time). I want the trigger to run once after the extended inserts are performed on the underlining tables. Is this possible?

The trigger is doing its job, e.g. 'FOR EACH ROW'.

I don't believe that mysql gives you the option of running a trigger once at the end.

I'd call the stored procedure from your code after the INSERT has successfully completed.

If you're worried about forgetting, setup a cron job to run it every once in a while.

Good luck.