Audit Log Script using Triggers

Question

Audit Logs for 50 tables need to insert in one table called audit table whatever the event performed on the tables(insert,update,delete) occurs it should contain new_val,old_val and table name,class name,modified by like these fields into my Audit Table.50 tables data should contain only in one single table called Audit Table

It is showing that i need to write script for every table how many tables it contains

Answer 1

If you will use triggers on your other tables to insert rows into your audit log table, you will indeed need to write three triggers for each table. (ON INSERT, ON UPDATE, ON DELETE).

Pain in the xxx. Triple pain in the xxx. Sigh.

If this were my project I'd consider writing a program to query information_schema.TABLES for a list of the tables involved, and generate the CREATE TRIGGER code for the tables involved. But, depending on the complexity of your table structure, that might be more trouble than it's worth.

Maybe helpful: create trigger insert update delete in 1 syntax