MySQL Log/History Triggers for Audit Trail

triggers.sql

DELIMITER //

# Campaign modified timestamps: modified_description, modified_where, modified_conditions, modified_includes
CREATE TRIGGER before_campaigns_update BEFORE UPDATE ON campaigns
FOR EACH ROW
BEGIN

IF (NEW.description <> OLD.description) OR (NEW.description IS NOT NULL AND OLD.description IS NULL) OR (NEW.description IS NULL AND OLD.description IS NOT NULL)
OR (NEW.infolink_text <> OLD.infolink_text) OR (NEW.infolink_text IS NOT NULL AND OLD.infolink_text IS NULL) OR (NEW.infolink_text IS NULL AND OLD.infolink_text IS NOT NULL)
OR (NEW.infolink_url <> OLD.infolink_url) OR (NEW.infolink_url IS NOT NULL AND OLD.infolink_url IS NULL) OR (NEW.infolink_url IS NULL AND OLD.infolink_url IS NOT NULL)
OR (NEW.is_product <> OLD.is_product) OR (NEW.is_product IS NOT NULL AND OLD.is_product IS NULL) OR (NEW.is_product IS NULL AND OLD.is_product IS NOT NULL)
THEN
SET NEW.modified_description = NOW();
END IF;

IF (NEW.country_id <> OLD.country_id) OR (NEW.country_id IS NOT NULL AND OLD.country_id IS NULL) OR (NEW.country_id IS NULL AND OLD.country_id IS NOT NULL)
OR (NEW.city <> OLD.city) OR (NEW.city IS NOT NULL AND OLD.city IS NULL) OR (NEW.city IS NULL AND OLD.city IS NOT NULL)
OR (NEW.address <> OLD.address) OR (NEW.address IS NOT NULL AND OLD.address IS NULL) OR (NEW.address IS NULL AND OLD.address IS NOT NULL)
OR (NEW.postal_code <> OLD.postal_code) OR (NEW.postal_code IS NOT NULL AND OLD.postal_code IS NULL) OR (NEW.postal_code IS NULL AND OLD.postal_code IS NOT NULL)
OR (NEW.delivered_to <> OLD.delivered_to) OR (NEW.delivered_to IS NOT NULL AND OLD.delivered_to IS NULL) OR (NEW.delivered_to IS NULL AND OLD.delivered_to IS NOT NULL)
THEN
SET NEW.modified_where = NOW();
END IF;

IF (NEW.deadline <> OLD.deadline) OR (NEW.deadline IS NOT NULL AND OLD.deadline IS NULL) OR (NEW.deadline IS NULL AND OLD.deadline IS NOT NULL)
OR (NEW.affiliate_url <> OLD.affiliate_url) OR (NEW.affiliate_url IS NOT NULL AND OLD.affiliate_url IS NULL) OR (NEW.affiliate_url IS NULL AND OLD.affiliate_url IS NOT NULL)
OR (NEW.photos <> OLD.photos) OR (NEW.photos IS NOT NULL AND OLD.photos IS NULL) OR (NEW.photos IS NULL AND OLD.photos IS NOT NULL)
OR (NEW.videos <> OLD.videos) OR (NEW.videos IS NOT NULL AND OLD.videos IS NULL) OR (NEW.videos IS NULL AND OLD.videos IS NOT NULL)
OR (NEW.hashtag <> OLD.hashtag) OR (NEW.hashtag IS NOT NULL AND OLD.hashtag IS NULL) OR (NEW.hashtag IS NULL AND OLD.hashtag IS NOT NULL)
OR (NEW.conditions <> OLD.conditions) OR (NEW.conditions IS NOT NULL AND OLD.conditions IS NULL) OR (NEW.conditions IS NULL AND OLD.conditions IS NOT NULL)
THEN
SET NEW.modified_conditions = NOW();
END IF;

IF (NEW.includes <> OLD.includes) OR (NEW.includes IS NOT NULL AND OLD.includes IS NULL) OR (NEW.includes IS NULL AND OLD.includes IS NOT NULL)
OR (NEW.includes_text <> OLD.includes_text) OR (NEW.includes_text IS NOT NULL AND OLD.includes_text IS NULL) OR (NEW.includes_text IS NULL AND OLD.includes_text IS NOT NULL)
THEN
SET NEW.modified_includes = NOW();
END IF;

END//

# Campaign modified timestamps: modified_when
CREATE TRIGGER before_campaigns_dates_insert BEFORE INSERT ON campaigns_dates
FOR EACH ROW
BEGIN

UPDATE campaigns
SET modified_when = NOW()
WHERE id = NEW.campaign_id;

END//

DELIMITER ;