CODEWITHSUNDEEP
mysqlsqltriggers
user1032531
user1032531

Reputation: 26281

Use trigger to insert row if value was changed

I have the following "dogs" table.

+----+------+-------+--------+-------+-----+
| id | Name | Color |  Size  | Hair  | Age |
+----+------+-------+--------+-------+-----+
|  1 | Fido | Red   | Big    | Long  |   4 |
|  2 | Bud  | Red   | Small  | Short |   7 |
|  3 | Sox  | Blue  | Medium | Short |   3 |
+----+------+-------+--------+-------+-----+

If one or more value is changed such as UPDATE dogs SET Name="Bud", Color="redish", Age=8 WHERE id=2;, I would like to insert several rows in two other tables:

INSERT INTO audits(id,auditTable,auditPK,dateTime) VALUES (0,'dogs',2,NOW());
INSERT INTO auditFields(id,auditsId,changeField,oldValue,newValue) VALUES (0,123,'Color','Red','Redish');
INSERT INTO auditFields(id,auditsId,changeField,oldValue,newValue) VALUES (0,123,'Age',7,8);

auditsId is the autoincrement ID of the first insert. Note that there was not an insert for the Name changing since it remained as "Bud". I am considering normalizing auditTable and changeField, but am not certain whether I should.

Is this possible to do with triggers? If so, how?

Upvotes: 1

Views: 317

Answers (1)

peterm
peterm

Reputation: 92785

Try this

DELIMITER $$
CREATE TRIGGER tg_dogs_upd AFTER UPDATE ON dogs
FOR EACH ROW 
BEGIN
    SET @AID = 0;
    IF NEW.name <> OLD.name THEN
        IF @AID = 0 THEN
            INSERT INTO audits(auditTable,auditPK,`dateTime`) VALUES ('dogs', NEW.id, NOW());
            SELECT LAST_INSERT_ID() INTO @AID;
        END IF;
        INSERT INTO auditFields(auditsId,changeField,oldValue,newValue) VALUES (@AID,'Name',OLD.`name`,NEW.`name`);
    END IF;
    IF NEW.color <> OLD.color THEN
        IF @AID = 0 THEN
            INSERT INTO audits(auditTable,auditPK,`dateTime`) VALUES ('dogs', NEW.id, NOW());
            SELECT LAST_INSERT_ID() INTO @AID;
        END IF;
        INSERT INTO auditFields(auditsId,changeField,oldValue,newValue) VALUES (@AID,'Color',OLD.color,NEW.color);
    END IF;
    IF NEW.size <> OLD.size THEN
        IF @AID = 0 THEN
            INSERT INTO audits(auditTable,auditPK,`dateTime`) VALUES ('dogs', NEW.id, NOW());
            SELECT LAST_INSERT_ID() INTO @AID;
        END IF;
        INSERT INTO auditFields(auditsId,changeField,oldValue,newValue) VALUES (@AID,'Size',OLD.size,NEW.size);
    END IF;
    IF NEW.hair <> OLD.hair THEN
        IF @AID = 0 THEN
            INSERT INTO audits(auditTable,auditPK,`dateTime`) VALUES ('dogs', NEW.id, NOW());
            SELECT LAST_INSERT_ID() INTO @AID;
        END IF;
        INSERT INTO auditFields(auditsId,changeField,oldValue,newValue) VALUES (@AID,'Hair',OLD.hair,NEW.hair);
    END IF;
    IF NEW.age <> OLD.age THEN
        IF @AID = 0 THEN
            INSERT INTO audits(auditTable,auditPK,`dateTime`) VALUES ('dogs', NEW.id, NOW());
            SELECT LAST_INSERT_ID() INTO @AID;
        END IF;
        INSERT INTO auditFields(auditsId,changeField,oldValue,newValue) VALUES (@AID,'Age',OLD.age,NEW.age);
    END IF;
END$$

DELIMITER ;

UPDATE: A more succinct version utilizing a temp table

DELIMITER $$
CREATE TRIGGER tg_dogs_upd AFTER UPDATE ON dogs
FOR EACH ROW 
BEGIN
    DROP TEMPORARY TABLE IF EXISTS tempAuditFields;
    CREATE TEMPORARY TABLE tempAuditFields(changeField VARCHAR(256),oldValue VARCHAR(256),newValue VARCHAR(256));
    IF NEW.name <> OLD.name THEN
        INSERT INTO tempAuditFields VALUES ('Name',OLD.`name`,NEW.`name`);
    END IF;
    IF NEW.color <> OLD.color THEN
        INSERT INTO tempAuditFields VALUES ('Color',OLD.color,NEW.color);
    END IF;
    IF NEW.size <> OLD.size THEN
        INSERT INTO tempAuditFields VALUES ('Size',OLD.size,NEW.size);
    END IF;
    IF NEW.hair <> OLD.hair THEN
        INSERT INTO tempAuditFields VALUES ('Hair',OLD.hair,NEW.hair);
    END IF;
    IF NEW.age <> OLD.age THEN
        INSERT INTO tempAuditFields VALUES ('Age',OLD.age,NEW.age);
    END IF;

    IF (SELECT COUNT(*) FROM tempAuditFields) > 0 THEN
        INSERT INTO audits(auditTable,auditPK,`dateTime`) VALUES ('dogs', NEW.id, NOW());
        SELECT LAST_INSERT_ID() INTO @AID;
        INSERT INTO auditFields(auditsId,changeField,oldValue,newValue)
        SELECT @AID,changeField,oldValue,newValue FROM tempAuditFields;
        DROP TEMPORARY TABLE tempAuditFields;
    END IF;
END$$

DELIMITER ;

Upvotes: 1

Related Questions