IN many projects, there are tables which need default audit columns such as Created_By, Created_Date, Modified_By, Modified_date and other columns that need to be updated every time some actions are done against the tables and/or columns. This type of functionality can be implemented using triggers.
Why have triggers? when the application can do the updating, this is a good argument, but like all application databases eventually other users, applications and scripts will get access to the applications database and end up wanting to read from the database and write to the database. During these times its better to have triggers updating the data independent of the application to ensure audit columns and other columns are updated with appropriate values.
We can implement the triggers either by hand coding or by generating them using the database metadata and some build time scripting. Hand coding these triggers is mundane work and error prone, especially when we need to add a new column that needs to added to all the triggers.
In the ruby code shown below, we are using active record connection to get the database metadata with a list of tables and then generate triggers to intercept the UPDATE command on all the tables in our application and if the Modified_By or Modified_Date columns are null then we are populating them with the current database user logged in USER and the current system datetime SYSDATE.
namespace :db do
desc 'Generate Triggers for all auditable tables'
task triggers: :environment do
sql = 'SELECT table_name tablename FROM user_tables'
tables = ActiveRecord::Base.connection.exec_query(sql)
tables.each do |table|
name = table['tablename']
trigger_name = "TRG_#{name.truncate(19, omission: '')}_UPDATE"
File.open("generated/#{trigger_name}.sql", 'w') do |triggerfile|
triggerfile.puts "CREATE OR REPLACE TRIGGER #{trigger_name}"
triggerfile.puts "BEFORE UPDATE ON #{name} FOR EACH ROW"
triggerfile.puts 'BEGIN'
triggerfile.puts ' IF :new.modified_by IS NULL'
triggerfile.puts ' THEN'
triggerfile.puts ' :new.modified_by := USER;'
triggerfile.puts ' END IF;'
triggerfile.puts ' IF :new.modified_date IS NULL'
triggerfile.puts ' THEN'
triggerfile.puts ' :new.modified_date := SYSDATE;'
triggerfile.puts ' END IF;'
triggerfile.puts 'END;'
triggerfile.puts '/'
end
end
puts "Generated triggers for tables"
end
end
This trigger generating code can be run as part of the CI build and the generated triggers can be put in as part of the database artifacts that are generated. This generation of triggers makes it very easy to accommodate database refactoring as now we don’t need to delete trigger script file or add new trigger script file when tables are DROPPED or CREATED.
When new columns such as Created_By and Created_Date need to be tracked or we need to track new DML actions such as INSERT or DELETE we just need to change the trigger generation code and all the triggers are updated without the need to hand edit each trigger script file.