Create Journaling on a table for mysql database

If you have some tables with very sensitive data in it you would create backups.

that is 1 thing, but if somethings wrong you would like to now when and what data is changed.

 

This is a nice solution that create a copy of table that you would like to monitor.

and add a 3 triggers –> 1 for Insert, update and delete.

If some data is inserted it will be in the journaling table 2.

If Updated or deleted you wil find what is changed in that table.

I created 2 Stored procedures:

SP1 –> to create the second table(copy) with 2 extra colums.

SP2 –> to create 3 triggers

This 2 SP will write the script to a file where you would like to be.

These are my SP code:

CREATE JN_TABLE

DELIMITER $$

USE `YOUR TABLESCHEMA`$$

DROP PROCEDURE IF EXISTS `CreateJNTable`$$

CREATE PROCEDURE `CreateJNTable`(IN tabelnaamostr VARCHAR(64),IN tabelschemastr VARCHAR(64))
BEGIN
-- 1. aanmaken temp table
CREATE TEMPORARY TABLE TMPJN (ID DOUBLE AUTO_INCREMENT KEY,stringval VARCHAR(21844) NOT NULL);
-- 2. aanmaken commentaar
INSERT INTO TMPJN (stringval) VALUES (CONCAT('-- We maken een Journaling tabel aan voor tabel ',tabelnaamostr ,' in tabelschema ',tabelschemastr));
-- 3. 1e lijn stuk text toevoegen
INSERT INTO TMPJN (stringval) VALUES (CONCAT('Create table ',tabelnaamostr,'_JN('));
-- 4. We gaan de Kolom namen toevoegen + hun datatype en een comma er achter
INSERT INTO TMPJN (stringval)
SELECT CONCAT(column_name,' ', Column_type,',') AS result
FROM information_schema.columns WHERE table_name= tabelnaamostr AND TABLE_Schema = tabelschemastr ;
-- 5. We gaan de andere 2 kolomen toevoegen die nodig zijn voor de Journaling
INSERT INTO TMPJN (stringval) VALUES ('RowAction varchar(1),');
INSERT INTO TMPJN (stringval) VALUES ('Tijdstip DateTime);');
-- 6. We schrijven dit alles weg naar en file op onderstaande locatie
SELECT stringval FROM TMPJN INTO OUTFILE '\\\\\192.168.0.13\\Divers\\CreateJNTable.sql';
-- 7. Tot slot deleten we de tmp tabel
DROP TEMPORARY TABLE TMPJN;
END$$

DELIMITER ;

And

CREATE JN_TRIGGERS

 

DELIMITER $$

USE `YOUR TABLESCHEMA`$$

DROP PROCEDURE IF EXISTS `CreateJNTriggers`$$

CREATE PROCEDURE `CreateJNTriggers`(IN tabelnaamostr VARCHAR(64),IN tabelschemastr VARCHAR(64))
BEGIN
-- 1. aanmaken temp table
CREATE TEMPORARY TABLE TMPJN (ID DOUBLE AUTO_INCREMENT KEY,stringval VARCHAR(21844) NOT NULL);
-- 2. Toevoegen van vaste script lijnen
INSERT INTO TMPJN (stringval) VALUES ('DELIMITER $$');
INSERT INTO TMPJN (stringval) VALUES (CONCAT('USE ',tabelschemastr,' $$'));
-- ************************************************************************************************************************
-- *                                                INSERT Trigger                                                        *
-- ************************************************************************************************************************
INSERT INTO TMPJN (stringval) VALUES ('--');
INSERT INTO TMPJN (stringval) VALUES ('--');
INSERT INTO TMPJN (stringval) VALUES (CONCAT('-- We maken een insert trigger aan voor tabel ',tabelnaamostr ,' in tabelschema ',tabelschemastr));
INSERT INTO TMPJN (stringval) VALUES (CONCAT('DROP TRIGGER IF EXISTS TR_',tabelnaamostr,'_Insert $$'));
INSERT INTO TMPJN (stringval) VALUES (CONCAT('CREATE TRIGGER TR_',tabelnaamostr,'_Insert AFTER INSERT ON ', tabelnaamostr));
INSERT INTO TMPJN (stringval) VALUES ('FOR EACH ROW BEGIN');
INSERT INTO TMPJN (stringval) VALUES (CONCAT('INSERT INTO ',tabelnaamostr,'_JN('));
-- 3. Toevoegen van colomnamen van de Journaling tabel waar we de data gaan in stoppen
INSERT INTO TMPJN (stringval)
SELECT CONCAT(column_name,',') AS result
FROM information_schema.columns WHERE table_name = CONCAT(tabelnaamostr,'_JN') AND TABLE_Schema = tabelschemastr ;
-- 4. variable om tijdelijk de max ID bij te houden
SET @MaxID := 0;
-- 5. we stoppen de max id er in.
SELECT @MaxID := MAX(ID) FROM TMPJN;
-- 6. we gaan de laaste waarde de , er uit halen want die hoeft daar niet te staan. (anders sintax fout)
UPDATE TMPJN SET stringval = REPLACE(stringval,',',')') WHERE id = @MaxID;
-- 7. Toevoegen van vaste script lijnen  
INSERT INTO TMPJN (stringval) VALUES ('VALUES(');
-- 8. Toevoegen van kolomnamen met een prefix van new. zonder de laatse 2 kolommen er bij
INSERT INTO TMPJN (stringval)
SELECT CONCAT('new.',column_name,',') AS result
FROM information_schema.columns WHERE table_name = CONCAT(tabelnaamostr,'_JN') AND TABLE_Schema = tabelschemastr AND  column_name NOT IN ('RowAction','Tijdstip');
-- 9. Toevoegen van vaste script lijnen
INSERT INTO TMPJN (stringval) VALUES ('\'I\',');
INSERT INTO TMPJN (stringval) VALUES ('NOW());');
INSERT INTO TMPJN (stringval) VALUES ('END;');
INSERT INTO TMPJN (stringval) VALUES ('$$');
-- ************************************************************************************************************************
-- *                                                UPDATE Trigger                                                        *
-- ************************************************************************************************************************
INSERT INTO TMPJN (stringval) VALUES ('--');
INSERT INTO TMPJN (stringval) VALUES ('--');
INSERT INTO TMPJN (stringval) VALUES (CONCAT('-- We maken een Update trigger aan voor tabel ',tabelnaamostr ,' in tabelschema ',tabelschemastr));
INSERT INTO TMPJN (stringval) VALUES (CONCAT('DROP TRIGGER IF EXISTS TR_',tabelnaamostr,'_Update $$'));
INSERT INTO TMPJN (stringval) VALUES (CONCAT('CREATE TRIGGER TR_',tabelnaamostr,'_Update AFTER Update ON ', tabelnaamostr));
INSERT INTO TMPJN (stringval) VALUES ('FOR EACH ROW BEGIN');
INSERT INTO TMPJN (stringval) VALUES (CONCAT('INSERT INTO ',tabelnaamostr,'_JN('));
INSERT INTO TMPJN (stringval)
SELECT CONCAT(column_name,',') AS result
FROM information_schema.columns WHERE table_name = CONCAT(tabelnaamostr,'_JN') AND TABLE_Schema = tabelschemastr ;
-- 4. variable om tijdelijk de max ID bij te houden
SET @MaxID := 0;
-- 5. we stoppen de max id er in.
SELECT @MaxID := MAX(ID) FROM TMPJN;
-- 6. we gaan de laaste waarde de , er uit halen want die hoeft daar niet te staan. (anders sintax fout)
UPDATE TMPJN SET stringval = REPLACE(stringval,',',')') WHERE id = @MaxID;   
INSERT INTO TMPJN (stringval) VALUES ('VALUES(');
INSERT INTO TMPJN (stringval)
SELECT CONCAT('new.',column_name,',') AS result
FROM information_schema.columns WHERE table_name = CONCAT(tabelnaamostr,'_JN') AND TABLE_Schema = tabelschemastr AND  column_name NOT IN ('RowAction','Tijdstip');
INSERT INTO TMPJN (stringval) VALUES ('\'U\',');
INSERT INTO TMPJN (stringval) VALUES ('NOW());');
INSERT INTO TMPJN (stringval) VALUES ('END;');
INSERT INTO TMPJN (stringval) VALUES ('$$');
-- ************************************************************************************************************************
-- *                                                DELETE Trigger                                                        *
-- ************************************************************************************************************************
INSERT INTO TMPJN (stringval) VALUES ('--');
INSERT INTO TMPJN (stringval) VALUES ('--');
INSERT INTO TMPJN (stringval) VALUES (CONCAT('-- We maken een Delete trigger aan voor tabel ',tabelnaamostr ,' in tabelschema ',tabelschemastr));
INSERT INTO TMPJN (stringval) VALUES (CONCAT('DROP TRIGGER IF EXISTS TR_',tabelnaamostr,'_Delete $$'));
INSERT INTO TMPJN (stringval) VALUES (CONCAT('CREATE TRIGGER TR_',tabelnaamostr,'_Delete AFTER Delete ON ', tabelnaamostr));
INSERT INTO TMPJN (stringval) VALUES ('FOR EACH ROW BEGIN');
INSERT INTO TMPJN (stringval) VALUES (CONCAT('INSERT INTO ',tabelnaamostr,'_JN('));
INSERT INTO TMPJN (stringval)
SELECT CONCAT(column_name,',') AS result
FROM information_schema.columns WHERE table_name = CONCAT(tabelnaamostr,'_JN') AND TABLE_Schema = tabelschemastr ;
-- 4. variable om tijdelijk de max ID bij te houden
SET @MaxID := 0;
-- 5. we stoppen de max id er in.
SELECT @MaxID := MAX(ID) FROM TMPJN;
-- 6. we gaan de laaste waarde de , er uit halen want die hoeft daar niet te staan. (anders sintax fout)
UPDATE TMPJN SET stringval = REPLACE(stringval,',',')') WHERE id = @MaxID;   
INSERT INTO TMPJN (stringval) VALUES ('VALUES(');
INSERT INTO TMPJN (stringval)
SELECT CONCAT('OLD.',column_name,',') AS result
FROM information_schema.columns WHERE table_name = CONCAT(tabelnaamostr,'_JN') AND TABLE_Schema = tabelschemastr AND  column_name NOT IN ('RowAction','Tijdstip');
INSERT INTO TMPJN (stringval) VALUES ('\'D\',');
INSERT INTO TMPJN (stringval) VALUES ('NOW());');
INSERT INTO TMPJN (stringval) VALUES ('END;');
INSERT INTO TMPJN (stringval) VALUES ('$$');
-- Vaste scriptlijn toevoegen
INSERT INTO TMPJN (stringval) VALUES ('DELIMITER ;');
-- weg schrijven van de gegevens
SELECT stringval FROM TMPJN INTO OUTFILE '\\\\\192.168.0.13\\Divers\\CreateJNTriggers.sql';
-- Tot slot deleten we de tmp tabel
DROP TEMPORARY TABLE TMPJN;
END$$

DELIMITER ;

 

After that you can call SP like this:

CALL CreateJNTable('TABLENAME','YOUR TABLESCHEMA');

CALL CreateJNTriggers('TABLENAME','YOUR TABLESCHEMA');