Optimize SQL databases – Triggers and logging

2011-02-25

Triggers in SQL is a form of function, or code snippet, which is executed when an event occurs. An example would be when data is inserted, updated or deleted from a table. They can also be used before or after data is inserted, updated or deleted. An excellent use of triggers is to log changes in database tables. It can be done to keep track of when various changes take place, save revisions and making backups.

You can choose to save only some of the data to know when something occurred, or you can save a complete copy of the data so you have the full history of all changes. Then you can also revert to previous versions or restore the data if it would be lost. To save a complete history, you need a copy of the table you want to save history for along with a few new columns such as user name, time stamp or type of event.

Here I present three different examples of triggers. All three triggers logs events that occurs in a particular table, but each trigger is connected to a different type of event.

CREATE TRIGGER Log_Table_Insert AFTER INSERT ON Table
FOR EACH ROW BEGIN
    INSERT INTO Table_Log (event, username, time, ID) VALUES('INS', USER(), NOW(), NEW.id);
END;

CREATE TRIGGER Log_Table_Update AFTER UPDATE ON Table
FOR EACH ROW BEGIN
    INSERT INTO Table_Log (event, username, time, ID) VALUES('UPD', USER(), NOW(), NEW.id);
END;

CREATE TRIGGER Log_Table_Delete AFTER DELETE ON Table
FOR EACH ROW BEGIN
    INSERT INTO Table_Log (event, username, time, ID) VALUES('DEL', USER(), NOW(), OLD.id);
END;

Other articles in this series:

  • Hiya! Thanks for publishing this sort of an insightful new article. Essentially you could possibly have explained those issues vice versa. But you brought it a friendly private spin. Looking foward to heading to your website with greater regularity.