I have a DB e.g "dbAuditLogs" inside created tables like as mstUser,mstEmployee,mstDepartment and auditHistory. when any CUD operation performed on mstUser/mstEmployee/mstDepartment then want to register an entry inside auditHistory table. I can do this with the help of triggers but in future when number of tables increase or columns removed from existing table then we will need to make changes into TRIGGER as well. Also by creating multiple TRIGGERS db performance will become slow. As per my understanding below are my queries

  1. Is there any design pattern available like as "UnitOfWork" in MYSQL/NODEJS.
  2. Is there any npm available which can handle Audit log from API.
  3. Only trigger is the best option suited for above condition?

Trigger created by me

BEGIN
SET @json = "{";
SET @first = true;
IF (OLD.firstName!=NEW.firstName) THEN
  SET @first = false;
  SET @json = CONCAT(@json, "\"firstName\"", ":", "\"", NEW.firstName, "\"");
END IF;
IF (OLD.lastName!=NEW.lastName) THEN
  IF (!@first) THEN
    SET @json = CONCAT(@json, ",");
  END IF;
SET @first = false;
SET @json = CONCAT(@json, "\"lastName\"", ":", "\"", NEW.lastName, "\"");
END IF;
SET @json = CONCAT(@json, "}");
INSERT INTO mstAuditHistory (tableName,jsonData,recordId,userId,eventType) 
    VALUES ('contactDetails',@json, NEW.id, NEW.userId,'Update');    
END
  • in future when number of tables increase or columns removed from existing table then we will need to make changes into TRIGGER as well. And what's a problem? each data structure change will include trigger change appropriate to it. It's normally. Of course, you can make "universal" trigger which will obtain the info about current table structure, check each field for a change and log if... but performance will extremely degrade, because each record change will fire all of trigger's actions (get structure, compare each field...)... – Akina Jun 14 at 7:56
  • Only trigger is the best option suited for above condition? I think the best option is to go from single queries to stored procedures which realize all logic you need - including logging. – Akina Jun 14 at 7:58
  • @Akina How can i make universal trigger? For insert i get NEW.columnName, for Update i get NEW.columnName and OLD.columnName. you have any example then please share with me – chetan mekha Jun 14 at 9:02
  • How can i make universal trigger? It is universal in terms of table's structure change - no change needs trigger modifying. For insert i get NEW.columnName, for Update i get NEW.columnName and OLD.columnName. It's two different triggers - what's a problem? AFTER INSERT trigger logs NEW.columnName only, AFTER UPDATE trigger logs both NEW.columnName and OLD.columnName... – Akina Jun 14 at 9:07
  • @Akina Above comment i added on trigger created by me. Which works perfect for contactDetails table but the problem is for multiple table i can't copy paste the same trigger. As every table have it's different table structure. that's reason I ask how can i make it generalize so I can use it on other table as well – chetan mekha Jun 14 at 9:20

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.