limit logging to once a second

[Darren White]

I have a trigger (shown below) that is logging from one table to another on updates. Now I want to limit this to no more than once a second. The problem is that I often have multiple updates on the same row, per second (columns are updated individually, not the entire row). So that if I use "AND NEW.last_update != OLD.last_update" then I never log anything. Hmm. Any ideas?

-- Trigger DDL Statements
DELIMITER $$
USE `control_sys_status`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `control_sys_status`.`main_logs`
BEFORE UPDATE ON `control_sys_status`.`status`
FOR EACH ROW
BEGIN
DECLARE rowNum INTEGER;
SET rowNum = NEW.id;
CASE rowNum
WHEN 1 THEN
IF NEW.id=1
AND (OLD.streamer_tension != NEW.streamer_tension OR
OLD.cable_speed != NEW.cable_speed OR
OLD.reel_speed_rmp != NEW.reel_speed_rmp OR
OLD.meter_out != NEW.meter_out OR
OLD.synchro_delta_deviation != NEW.synchro_delta_deviation)
THEN
INSERT INTO log_streamer_01
(streamer_tension, cable_speed, reel_speed_rmp, meter_out, synchro_delta_deviation)

VALUES (NEW.streamer_tension, NEW.cable_speed, NEW.reel_speed_rmp, NEW.meter_out, NEW.synchro_delta_deviation) ;
END IF;

WHEN 2 THEN
...



Edited 2 time(s). Last edit at 03/05/2012 07:05AM by Darren White.