DB Triggers

Author: Milos Zubal

• The following trigger has been created to support investigation of SUPPORT-371.
• It is supposed to log modification of the thread_config.is_active field, because the problem is that we don't know who and when modifies it.
• The trigger has been installed to extdb2 (slave) to make it more safe.
• The trigger was tested and it works, but the user is not logged, which is very likely consequence of the replication to slave (the replication doesn't know who made the modification on the master).

• Once the problem noted in SUPPORT-371 happens again, the modifications of the thread_config.is_active can be discovered by running following query on the extdb2:

SELECT * FROM snmp_manager.tab_modification_log;

The trigger code:

USE snmp_manager;
 
DROP TABLE IF EXISTS snmp_manager.tab_modification_log;
CREATE TABLE snmp_manager.tab_modification_log
(
	serialnum INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
	mod_user VARCHAR(64) DEFAULT "",
	mod_table VARCHAR(64) NOT NULL,
	mod_id INTEGER NOT NULL,
	old_val VARCHAR(512) DEFAULT "",
	new_val VARCHAR(512) DEFAULT "",
	ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
 
DROP TRIGGER IF EXISTS snmp_manager.thread_config_after_update;
DELIMITER $$
CREATE TRIGGER snmp_manager.thread_config_after_update
AFTER UPDATE ON snmp_manager.thread_config
FOR EACH ROW
BEGIN
	IF (NEW.is_active != OLD.is_active) THEN
		INSERT INTO snmp_manager.tab_modification_log
			( mod_user, mod_table, mod_id, old_val, new_val )
		VALUES
		(
			 USER(),
			 "thread_config",
			 NEW.id,
			 OLD.is_active,
			 NEW.is_active  
		);
	END IF;
END $$
DELIMITER ;