Mysql – Want to write a trigger after UPDATE

logMySQLtrigger

I have writen a trigger which will log every data updation and will log previous_value, new_value, field_name and many more. But the problem is i have 77 fields in my table. So, it's difficult to write IF ENDIF for each field so i want to know is it possible to write using loop?

I have tried so far::

BEGIN

IF(OLD.company_name != NEW.company_name) THEN
 INSERT INTO elm_activity_log (user_id, action_on, action, action_col, action_old_value, action_new_value, action_at, action_on_id) VALUES (NEW.updated_by, "company", "update", "company_name", OLD.company_name, NEW.company_name, CURRENT_TIMESTAMP(), NEW.company_id);
END IF;
IF(OLD.company_first_name != NEW.company_first_name) THEN
 INSERT INTO elm_activity_log (user_id, action_on, action, action_col, action_old_value, action_new_value, action_at, action_on_id) VALUES (NEW.updated_by, "company", "update", "company_first_name", OLD.company_first_name, NEW.company_first_name, CURRENT_TIMESTAMP(), NEW.company_id);
END IF;

END

Please help, or any suggestion will be very helpfull.

Thanks in advance.

Best Answer

Since dynamic sql is not allowed in the trigger body,you can use a statement which runs through all the columns and builds the IF statements

SELECT CONCAT('IF(OLD.',COLUMN_NAME, '=NEW.',COLUMN_NAME,' THEN
 INSERT INTO elm_activity_log (user_id, action_on, action, action_col, action_old_value, action_new_value, action_at, action_on_id) VALUES (NEW.updated_by, "company", "update", "company_name", OLD.' ,COLUMN_NAME,', NEW.',COLUMN_NAME,', CURRENT_TIMESTAMP(), NEW.company_id; END IF;')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='yourTable' AND column_key!='PRI';

You can narrow the results even more, take a look at COLUMNS table if you want to filter based on column type or remove columns with keys and so on.I removed the primary key columns in the last condition for example.

Related Solutions

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

What you presented is essentially the correct way for interrupting triggers in MySQL 5.1

I wrote about this before

Dec 23, 2011 : check constraint does not work?
Apr 25, 2011 : Trigger in MySQL to prevent insertion

You cannot do Dynamic SQL in triggers.

You may have to resort to coding the my_signal like this:

CREATE PROCEDURE `my_signal`(error_type INT)
BEGIN
    IF error_type = 1 THEN
        UPDATE `Error: can not insert new OWNER !` set x=1;
    END IF;
    IF error_type = 2 THEN
        UPDATE `Error: Row can not reference itself!` set x=1;
    END IF;
    .
    .
END//

then change the trigger to look like this:

DELIMITER $$
CREATE
  TRIGGER `employee_before_insert` BEFORE INSERT
    ON `Employee`
    FOR EACH ROW BEGIN
      CASE
       WHEN NEW.designation = 'OWNER'  THEN
          CALL my_signal(1);
       WHEN NEW.SSN = NEW.MSSN THEN
          CALL my_signal(2);
      END CASE; 
  END$$   
DELIMITER ;

Sorry for the clumsy programming but Signal Processing is MySQL 5.1 is horrible.

MySQL Trigger – Insert Rows via Row-Level Trigger After Insertion

To perform what you are looking for based on this example:

INSERT INTO synonym_bucket VALUES (23,37);

SUGGESTION #1

DROP TABLE IF EXISTS synonym_bucket;
CREATE TABLE synonym_bucket LIKE synonyms;
ALTER TABLE synonym_bucket ENGINE=BLACKHOLE;
DELIMITER $$   
CREATE TRIGGER MakeTwoSynonyms
AFTER INSERT ON synonym_bucket 
FOR EACH ROW
BEGIN
    IF new.w1 <> new.w2 THEN
        INSERT IGNORE INTO synonyms VALUES (new.w1, new.w2),(new.w2, new.w1);
    END IF;
END $$
DELIMITER ;

Using synonym_bucket takes up just 2K (table definition plus trigger)

Bottom Line: For a table's before and after trigger, you cannot perform additional DML to the same table. However, you can do DML from a trigger to another table. This is why I made a BLACKHOLE table. It takes up to 2K (table definition and the trigger code). You use it to write to a separate table. In this case, synonym_bucket does the double INSERT into synonyms.

SUGGESTION #2

Do the the double INSERT yourself

INSERT IGNORE INTO synonyms VALUES (23,37),(37,23);

No trigger required