User Tools
Writing /app/www/public/data/meta/databaseandnetworkmanagement/db_triggers.meta failed
databaseandnetworkmanagement:db_triggers
Differences
This shows you the differences between two versions of the page.
| Both sides previous revisionPrevious revision | |||
| databaseandnetworkmanagement:db_triggers [2017/05/25 15:55] – mmcc | databaseandnetworkmanagement:db_triggers [2021/06/25 10:09] (current) – external edit 127.0.0.1 | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | ====== DB Triggers ====== | ||
| + | |||
| + | Author: Milos Zubal | ||
| + | |||
| + | |||
| + | ===== Introduction ===== | ||
| + | |||
| + | * The following trigger has been created to support investigation of [[https:// | ||
| + | * It is supposed to log modification of the // | ||
| + | * 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' | ||
| + | |||
| + | ---- | ||
| + | |||
| + | ===== The Trigger ===== | ||
| + | |||
| + | * Once the problem noted in [[https:// | ||
| + | <code sql> | ||
| + | select * from snmp_manager.tab_modification_log; | ||
| + | </ | ||
| + | |||
| + | 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(), | ||
| + | " | ||
| + | NEW.id, | ||
| + | OLD.is_active, | ||
| + | NEW.is_active | ||
| + | ); | ||
| + | END IF; | ||
| + | END $$ | ||
| + | DELIMITER ; | ||
| + | </ | ||
Page Tools
