MySQL Forums
Forum List  »  Triggers

limit logging to once a second
Posted by: Darren White
Date: March 05, 2012 07:04AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
limit logging to once a second
1948
March 05, 2012 07:04AM
1136
March 05, 2012 07:19AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.