Search options

I finally managed to get it working with a trick

I use a table to store the username captured by Dmitry's script and then I added a select in my trigger to get the last username_id.

Into MVDB scripts:

Setting the variables

// Global variables declarations
var

   sUserName: string;

Saving the username with current date into Table "WindowsID" when starting the application

// Load automatically information into grids in form1 + operator tracking
procedure Form1_OnShow (Sender: string; Action: string);
var
sCurrentDate: string;
begin
    sCurrentDate := '"' + FormatDateTime('yyyy-MM-DD hh:nn:ss.000', now) + '"';
    SQLExecute ('INSERT INTO WindowsID (DateTime, AccountID)' + 'VALUES (' +sCurrentDate+', '+ sUserName +')');
    Form1.LastUser_Grid.dbUpdate;
  

end;

Getting the current Windows username

Begin
// get current windows user login 

    sUserName := '"'+GetUserName+'"';

End.

And then, using SQLiteStudio, I create my triggers to log as much information I want:

Insert trigger:

INSERT INTO logs  (    
    Change_date,
    New_Id_user,            
    sqlAction         ,
    New_usr_Name       ,
    New_firstname     ,
    new_UsrID    ,
    new_dpt            ,
    New_Room      ,
    new_id_costcenter   ,
    New_id_computer ,
    src,
    Username      
        
    )

          values (DATETIME('NOW','localtime'),new.id,'INSERT',new.Name,
                  new.Firstname, new.UserID,
                  new.Dpt,new.Room,new.id_CostCenter,
                   new.id_Computers, 'Users',(select AccountID from WindowsID where id= (select max(id) from windowsID))
                 );

Update trigger:

INSERT INTO logs  (    
    Change_date,
    New_Id_user,            
    old_ID_user            ,
    sqlAction         ,
    Old_usr_Name       ,
    New_usr_Name       ,
    Old_firstname     ,
    New_firstname     ,
    old_UsrID   ,
    new_UsrID    ,
    old_dpt            ,
    new_dpt            ,
    old_Room     ,
    New_Room      ,
    old_id_costcenter   ,
    new_id_costcenter   ,
    Old_id_Computer       ,
    New_id_computer  ,
    src ,
    Username    
        
    )

          values (DATETIME('NOW','localtime'),new.id,old.id,'UPDATE',old.Name,new.Name,old.Firstname,
                  new.Firstname,old.UserID, new.UserID,old.Dpt,
                  new.Dpt,old.Room,new.Room,old.id_CostCenter,new.id_CostCenter,
    old.id_Computers,new.id_Computers,'Users',(select AccountID from WindowsID where id= (select max(id) from windowsID))
                 );

Delete trigger:

INSERT INTO logs  (    
    Change_date,            
    old_ID_user            ,
    sqlAction         ,
    Old_usr_Name       ,
    Old_firstname     ,
    old_UsrID   ,
    old_dpt            ,
    old_Room     ,
    old_id_costcenter   ,
    Old_id_Computer ,
    src ,
    Username        
        
    )

          values (DATETIME('NOW','localtime'),old.id,'DELETE',old.Name,old.Firstname,
                 old.UserID,old.Dpt,
                  old.Room,old.id_CostCenter,
    old.id_Computers, 'Users',(select AccountID from WindowsID where id= (select max(id) from windowsID))
                 );