Topic: [script] Logging with using triggers (Sqlite)

Logging with using triggers for SQLite


Code:

procedure Form1_OnShow (Sender: string; Action: string);
begin
    //SQLExecute('DROP TRIGGER IF EXISTS my_log_insert'); // how to remove trigger

    // trigger when insert new a record
    SQLExecute(
    'CREATE TRIGGER IF NOT EXISTS my_log_insert AFTER INSERT ON employees '+
    'BEGIN '+
    '    INSERT INTO employees_logs(ParentId, datetimeEvent, operation, lastname, firstname, salary, dateofbirth) VALUES (NEW.id, datetime(''now'', ''localtime''), ''New'', NEW.lastname, NEW.firstname, NEW.salary, NEW.dateofbirth); '+
    'END;'
    );

    // trigger when update a record
    SQLExecute(
    'CREATE TRIGGER IF NOT EXISTS my_log_update AFTER UPDATE ON employees '+
    'BEGIN '+
    '    INSERT INTO employees_logs(ParentId, datetimeEvent, operation, lastname, firstname, salary, dateofbirth) VALUES (NEW.id, datetime(''now'', ''localtime''), ''Update'', NEW.lastname, NEW.firstname, NEW.salary, NEW.dateofbirth); '+
    'END;'
    );

    // trigger when delete a record
    SQLExecute(
    'CREATE TRIGGER IF NOT EXISTS my_log_delete AFTER DELETE ON employees '+
    'BEGIN '+
    '    INSERT INTO employees_logs(ParentId, datetimeEvent, operation, lastname, firstname, salary, dateofbirth) VALUES (OLD.id, datetime(''now'', ''localtime''), ''Delete'', OLD.lastname, OLD.firstname, OLD.salary, OLD.dateofbirth); '+
    'END;'
    );
end;





procedure Form1_bUpdate_OnClick (Sender: string; var Cancel: boolean);
begin
    if Form1.cbEmp.dbItemID = -1 then Form1.GridLogs.dbFilter := '' else Form1.GridLogs.dbFilter := 'ParentId='+Form1.cbEmp.sqlValue;
    Form1.GridLogs.dbUpdate;
end;


procedure frmEmployee_OnShow (Sender: string; Action: string);
begin
    if frmEmployee.bSave.dbGeneralTableId <> -1 then
    begin
        frmEmployee.GridLogs.dbFilter := 'ParentId='+IntToStr(frmEmployee.bSave.dbGeneralTableId);
        frmEmployee.GridLogs.dbUpdate;
    end else
    begin
        frmEmployee.GridLogs.ClearRows;
    end;
end;


Project example:
http://myvisualdatabase.com/forum/misc. … download=1

Dmitry.

Re: [script] Logging with using triggers (Sqlite)

Great way to do it without going through a SQLite management software + the code is always at hand for edition. Well done, I use triggers a lot, they are so powerfull! I still prefer to keep the triggers secrets though!

Re: [script] Logging with using triggers (Sqlite)

Thank You Derek!