audit

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;