Trigger - zhamri/MyClass-MySQL GitHub Wiki

Use Triggers to Detect Updated Columns

Create a Table to Log Changes:

CREATE TABLE update_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    matrik_no VARCHAR(20),
    column_name VARCHAR(45),
    old_value VARCHAR(45),
    new_value VARCHAR(45),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Create a Trigger to Track Changes:

DELIMITER //

CREATE TRIGGER track_updates BEFORE UPDATE ON test_student
FOR EACH ROW
BEGIN
    -- Check if std_name is updated
    IF OLD.std_name != NEW.std_name THEN
        INSERT INTO update_log (matrik_no, column_name, old_value, new_value)
        VALUES (NEW.matrik_no, 'std_name', OLD.std_name, NEW.std_name);
    END IF;

    -- Check if Address is updated
    IF OLD.Address != NEW.Address THEN
        INSERT INTO update_log (matrik_no, column_name, old_value, new_value)
        VALUES (NEW.matrik_no, 'Address', OLD.Address, NEW.Address);
    END IF;

    -- You can add similar IF statements for other columns as needed
END //

DELIMITER ;

List All Triggers:

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT, ACTION_TIMING 
FROM INFORMATION_SCHEMA.TRIGGERS 
WHERE TRIGGER_SCHEMA = 'apiDB';