====== DB Triggers ======

Author: Milos Zubal


===== Introduction =====

  * The following trigger has been created to support investigation of [[https://errigal.atlassian.net/browse/SUPPORT-371|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).

----

===== The Trigger =====

  * Once the problem noted in [[https://errigal.atlassian.net/browse/SUPPORT-371|SUPPORT-371]] happens again, the modifications of the //thread_config.is_active// can be discovered by running following query on the extdb2:
<code sql>
select * from snmp_manager.tab_modification_log;
</code>

The trigger code:
<code sql>
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 ;
</code>