Topic: audit
Based on code snippets taken from the forum and what I learned here, below is the script I am using to audit changes to a given database table. Works 100%
procedure triggers_employee;
//*** Proc 845 - auditar alterações em tabela
begin
SQLExecute('DROP TRIGGER IF EXISTS log_insert_employee'); // how to remove trigger
SQLExecute('DROP TRIGGER IF EXISTS log_update_employee'); // how to remove trigger
SQLExecute('DROP TRIGGER IF EXISTS log_delete_employee'); // how to remove trigger
// trigger when insert new a record
SQLExecute(
'CREATE TRIGGER IF NOT EXISTS log_insert_employee AFTER INSERT ON employees '+
'BEGIN '+
' INSERT INTO tbl_auditoria_employee(employee_id, new_lastname, new_firstname, new_dateofbirth, ocorrencia, status, nome_user) VALUES '+
' (NEW.id, NEW.lastname, NEW.firstname, NEW.dateofbirth,datetime(''now'', ''localtime''), ''New'',"'+nome_user+'"); '+
'END;'
);
// trigger when update a record
SQLExecute(
'CREATE TRIGGER IF NOT EXISTS log_update_employee AFTER UPDATE ON employees '+
'BEGIN '+
' INSERT INTO tbl_auditoria_employee(employee_id, old_lastname, old_firstname, old_dateofbirth, new_lastname, new_firstname, new_dateofbirth, ocorrencia,status, nome_user) VALUES '+
' (NEW.id, OLD.lastname, OLD.firstname, OLD.dateofbirth, NEW.lastname, NEW.firstname, NEW.dateofbirth,datetime(''now'', ''localtime''), ''Update'',"'+nome_user+'"); '+
'END;'
);
// trigger when delete a record
SQLExecute(
'CREATE TRIGGER IF NOT EXISTS log_delete_employee AFTER DELETE ON employees '+
'BEGIN '+
' INSERT INTO tbl_auditoria_employee(employee_id, old_lastname, old_firstname, old_dateofbirth, ocorrencia,status, nome_user) VALUES '+
' (OLD.id, OLD.lastname, OLD.firstname,OLD.dateofbirth, datetime(''now'', ''localtime''), ''Delete'',"'+nome_user+'"); '+
'END;'
);
end;
procedure Form1_OnShow (Sender: TObject; Action: string);
begin
triggers_employee; //*** Proc 845 - auditar alterações em tabela
end;
Roberto Alencar