Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The task: write a trigger to log all updates within tbl1 table for future audit users' actions.

The tbl1 table:

id INT(11) 
y SMALLINT(6)
country SMALLINT(6)
-- really here ~20 fields but only some of them have to be monitored

I'm going to store both old & new values, one row for each field that was changed. The place for log (simplified for this example):

 CREATE TABLE z_log(
   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   table_name CHAR(20) NOT NULL,
   row_id INT(11) UNSIGNED NOT NULL,
   field CHAR(20) NOT NULL,
   old_val VARCHAR(255) NOT NULL DEFAULT '',
   new_val VARCHAR(255) NOT NULL DEFAULT '',
   PRIMARY KEY (id)
 )

The trigger I have created:

CREATE TRIGGER trigger1
    AFTER UPDATE
    ON tbl1
    FOR EACH ROW
BEGIN
  -- I'm interested in two fields only and if they were changed only
  IF new.y != old.y THEN
    SET @y = "('','tbl1',new.id,'y',old.y,new.y)";
  END IF;

  IF new.country != old.country THEN
    SET @country = "('','tbl1',new.id,'country',old.country,new.country)";
  END IF;

  if @y != '' or @country != '' THEN
    set @my_sql = concat('insert into z_lot values ', concat_ws(',', @y, @country), ';');
    prepare stmt1 from @my_sql;
    EXECUTE stmt1;
  end if;

END

But I got error "dynamic sql is not allowed in stored function or trigger". I can avoid 'execute' by using a lot separated inserts: one for field 'y', one for field 'country' and ~10 inserts more for other fields but there will be performance penalty.

Is there any other fast way to log changes only? Thank you.

share|improve this question
1  
Not really an answer, but this post outlines something to be aware of for replication: order of transaction completions. –  Derek Downey Apr 6 '12 at 14:32
    
@DTest thank you.I think there will be negligible difference in timestamps tor each transaction, it is enoygh for my project. –  Putnik Apr 6 '12 at 14:57

1 Answer 1

up vote 0 down vote accepted

You could alter the trigger with brute force INSERTs as follows:

CREATE TRIGGER trigger1 
    AFTER UPDATE 
    ON tbl1 
    FOR EACH ROW 
BEGIN 
  DECLARE insert_style INT;

  -- I'm interested in two fields only and if they were changed only 

  SET insert_style = 0;
  IF new.y != old.y THEN 
      SET insert_style = 1;
  END IF;
  IF new.country != old.country THEN 
      SET insert_style = insert_style + 2;
  END IF; 

  CASE insert_style
      WHEN 1 THEN
          insert into z_lot values
          ('','tbl1',new.id,'y',old.y,new.y);
      WHEN 2 THEN
          insert into z_lot values
          ('','tbl1',new.id,'country',old.country,new.country);
      WHEN 3 THEN
          insert into z_lot values
          ('','tbl1',new.id,'y',old.y,new.y),
          ('','tbl1',new.id,'country',old.country,new.country);
  END CASE;      

END 
share|improve this answer
    
Ugh, And for 10 fields we will have 2^10-1 cases... It looks crazy enough to test at least partialy :) –  Putnik Apr 9 '12 at 10:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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