1

I am trying to update columns based on the value of the inserted column of the table 'my_table'. I want to be able to record the timestamp of the values changed. I am using the triggers but I can't find out how to choose the column dynamically

CREATE TRIGGER `record_time` AFTER UPDATE ON `my_table`
 FOR EACH ROW INSERT INTO `record_changes` (dynamic_column) values (CUR_DATE())

here you see that I want to choose the column dynamically(dynamic_column) based on the value of the column in the table 'my_table'.

table structure for 'record_changes' is something like this, a,b,c,d.... upto 9 columns. The value being updated in 'my_table' is one of these 9 values. So I want to select the column to update based on the value of the 'my_table'. Can someone help?

0

Unfortunately, Dynamic SQL is not permitted in Triggers. Notwithstanding, what you are asking for is still possible, but you will have to apply some elbow grease.

I wrote an answer to the post Disable trigger for just one table. What I did was create a user variable @TRIGGER_DISABLED as a flag to control if the code within the trigger was to be executed.

In your particular case, you will need 9 flags. You will also need a BEFORE UPDATE trigger:

DELIMITER $$
CREATE TRIGGER `setup_record_time` BEFORE UPDATE ON `my_table`
FOR EACH ROW 
BEGIN
  SET @rec_col1 = IF(NEW.col1=OLD.col1,0,1);
  SET @rec_col2 = IF(NEW.col2=OLD.col2,0,1);
  SET @rec_col3 = IF(NEW.col3=OLD.col3,0,1);
  SET @rec_col4 = IF(NEW.col4=OLD.col4,0,1);
  SET @rec_col5 = IF(NEW.col5=OLD.col5,0,1);
  SET @rec_col6 = IF(NEW.col6=OLD.col6,0,1);
  SET @rec_col7 = IF(NEW.col7=OLD.col7,0,1);
  SET @rec_col8 = IF(NEW.col8=OLD.col8,0,1);
  SET @rec_col9 = IF(NEW.col9=OLD.col9,0,1);
END $$
DELIMITER $$

The AFTER UPDATE trigger looks like this:

DELIMITER $$
CREATE TRIGGER `record_time` AFTER UPDATE ON `my_table`
FOR EACH ROW 
BEGIN
  IF @rec_col1 = 1 THEN INSERT INTO `record_changes` (col1) values (CUR_DATE()); END IF;
  IF @rec_col2 = 1 THEN INSERT INTO `record_changes` (col2) values (CUR_DATE()); END IF;
  IF @rec_col3 = 1 THEN INSERT INTO `record_changes` (col3) values (CUR_DATE()); END IF;
  IF @rec_col4 = 1 THEN INSERT INTO `record_changes` (col4) values (CUR_DATE()); END IF;
  IF @rec_col5 = 1 THEN INSERT INTO `record_changes` (col5) values (CUR_DATE()); END IF;
  IF @rec_col6 = 1 THEN INSERT INTO `record_changes` (col6) values (CUR_DATE()); END IF;
  IF @rec_col7 = 1 THEN INSERT INTO `record_changes` (col7) values (CUR_DATE()); END IF;
  IF @rec_col8 = 1 THEN INSERT INTO `record_changes` (col8) values (CUR_DATE()); END IF;
  IF @rec_col9 = 1 THEN INSERT INTO `record_changes` (col9) values (CUR_DATE()); END IF;
END $$
DELIMITER $$

Just to be on the safe side, you should initialize these 9 user variables with 0.

SET @rec_col1 = 0;
SET @rec_col2 = 0;
SET @rec_col3 = 0;
SET @rec_col4 = 0;
SET @rec_col5 = 0;
SET @rec_col6 = 0;
SET @rec_col7 = 0;
SET @rec_col8 = 0;
SET @rec_col9 = 0;
UPDATE mytable ... ;

That way, your session dictates the trigger's behavior.

Give it a Try !!!

  • Hi, rolando. I'm a beginner web developer so not so advanced with mysql. So I was thinking maybe there was a quicker way of doing this. Seeing this much of code, I'm thinking of adding another statement in php from where the data will be inserted, together with the update on 'my_table' . That way I can use the variable easily. Not to dishonor your answer. This is very useful for me as I build my knowledge. – Karan Jan 27 '15 at 17:15

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.