B4J Tutorial SQLite Triggers: Creating Audit Trails For Dummies

Discussion in 'B4J Tutorials' started by Mashiane, Aug 16, 2018.

  1. Mashiane

    Mashiane Expert Licensed User

    Ola

    What is an audit trail? As per Wikipedia...

    An audit trail (also called audit log) is a security-relevant chronological record, set of records, and/or destination and source of records that provide documentary evidence of the sequence of activities that have affected at any time a specific operation, procedure, or event.[1][2] Audit records typically result from activities such as financial transactions,[3] scientific research and health care data transactions,[4] or communications by individual people, systems, accounts, or other entities.

    The process that creates an audit trail is typically required to always run in a privileged mode, so it can access and supervise all actions from all users; a normal user should not be allowed to stop/change it. Furthermore, for the same reason, trail file or database table with a trail should not be accessible to normal users.


    What is an SQLite Trigger?

    An SQLite trigger is a named database object that is executed automatically when an INSERT, UPDATE, or DELETE statement is issued against the associated table. You often use triggers to enable sophisticated auditing. For example, you want to log the changes of the sensitive data such as salary, address, etc., whenever it changes. In addition, you use triggers to enforce complex business rules centrally at the database level and prevent invalid transactions.

    The purpose of this tutorial?

    This tutorial is a step by step process of how one can implement audit trails for changes in their tables, whether INSERT,UPDATE,DELETE statements as explained below. I will attempt to use TRIGGERS to achieve this.

    SQLite Triggers have an OLD and a NEW object. The OLD object will store the values of the fields per record before any changes are made and NEW will store the updated field values.

    • For INSERTS, only the NEW object is valid
    • FOR UPDATES, the OLD and NEW objects are available
    • FOR DELETE, only the OLD object is valid

    Assumptions:

    1. a database table exists that need to be audited
    2. a clone of this table can be used to keep track of audit related transactions
    3. each time a record is INSERTED, UPDATED and DELETED, the cloned table will be updated

    There are about 9 variants of triggers that can be created using the events.

    Code:
    Public const TRIGGER_WHEN_BEFORE As String = "BEFORE"
        
    Public const TRIGGER_WHEN_AFTER As String = "AFTER"
        
    Public const TRIGGER_WHEN_INSTEADOF As String = "INSTEAD OF"
        
    Public const TRIGGER_EVENT_INSERT As String = "INSERT"
        
    Public const TRIGGER_EVENT_UPDATE As String = "UPDATE"
        
    Public const TRIGGER_EVENT_DELETE As String = "DELETE"
    These are translated as

    Inserts

    • BEFORE INSERT
    • AFTER INSERT
    • INSTEAD OF INSERT

    Updates

    • BEFORE UPDATE
    • AFTER UPDATE
    • INSTEAD OF UPDATE

    Deletes
    • BEFORE DELETE
    • AFTER DELETE
    • INSTEAD OF DELETE

    The Subs below will use methods already in DBUtils. These will take care of

    1. creating a clone of an existing table and make the cloned table ready for audit inserts
    2. create the triggers that will be fired each time INSERT,UPDATE and DELETE on the master records happen. Here we go...

    Step 1:

    For each table that we need to audit, we need to create a clone. We can create a clone that will have old_ and new_ values for EACH ROW.

    To begin, we need to get the structure of an existing table. I have defined a GetStructure method to do this. This returns a map from a PRAGMA statement to get the name of each name and type of column.

    Code:
    'get the table structure from the pragma statement
    Sub GetTableStructure(jSQL As SQL, tblName As StringAs Map
        
    Dim fld As Map
        fld.Initialize
        
    Dim fields As List = ExecuteMaps(jSQL,"PRAGMA table_info ('" & tblName & "')",Null)
        
    For Each fldm As Map In fields
            
    Dim fldname As String = fldm.GetDefault("name","")
            
    Dim fldtype As String = fldm.GetDefault("type","")
            fldname = fldname.tolowercase
            fld.put(fldname,fldtype)
        
    Next
        
    Return fld
    End Sub
    Step 2:

    We have the structure of the table to audit read from the database, we need to create a new table with the same structure but having prefixes for each field. For each field in the table to be audited we will have two fields old_xxx and new_xxx

    To be continued...
     

    Attached Files:

    Last edited: Aug 16, 2018
  2. Mashiane

    Mashiane Expert Licensed User

    Some of the useful functions we will use will be...

    Code:
    Private Sub EscapeField(f As StringAs String
        
    Return $"[${f}]"$
    End Sub

    'check to see if SQLite table exists
    Sub SQLiteTableExists(jSQL As SQL, tblName As StringAs Boolean
        
    Dim res As String
        
    Try
            
    Dim qry As String = $"select tbl_name from sqlite_master where type = 'table' and lower(tbl_name) = ?"$
            res = jSQL.ExecQuerySingleResult2(qry, 
    Array As String(tblName.tolowercase))
        
    Catch
            
    Return False
        
    End Try
        
    If res = Null Then
            
    Return False
        
    Else
            
    Return True
        
    End If
    End Sub

    'create an table with auto increment id
    Sub SQLiteCreateTable(jSQL As SQL, tblName As String, newFields As Map, pkName As String, aiName As StringAs Boolean
        
    Dim fldName As String
        
    Dim fldType As String
        
    Dim fldTot As Int
        
    Dim fldCnt As Int
        
    'create the audit trail table
        fldTot = newFields.Size - 1
        
    Dim sb As StringBuilder
        sb.Initialize
        sb.Append(
    "(")
        
    For fldCnt = 0 To fldTot
            fldName = newFields.GetKeyAt(fldCnt)
            fldType = newFields.GetValueAt(fldCnt)
            
    If fldCnt > 0 Then sb.Append(", ")
            sb.Append(EscapeField(fldName)).Append(
    " ").Append(fldType)
            
    If fldType = DB_TEXT Then sb.Append(" COLLATE NOCASE")
            
    If fldName.EqualsIgnoreCase(pkName) Then sb.Append(" NOT NULL PRIMARY KEY")
            
    If fldName.EqualsIgnoreCase(aiName) Then sb.Append(" AUTOINCREMENT")
        
    Next
        sb.Append(
    ")")
        
    'define the qry to execute
        Dim query As String = "CREATE TABLE IF NOT EXISTS " & EscapeField(tblName) & " " & sb.ToString
        
    'run the query
        jSQL.ExecNonQuery(query)
        
    Return SQLiteTableExists(jSQL,tblName)
    End Sub
    Step 2:

    In step two we clone the existing table, for example if out table name is "Mashy", a new table with name "Mashy_Audit" will be created. This will have and auto increment id field and the Audit_Date of the transaction date.

    Code:
    'create an audit trail
    Sub SQLiteCreateAuditTrail(jSQL As SQL, tblName As StringAs Boolean
        
    'define the audit trail table name
        Dim auditTrailTable As String = $"${tblName}_Audit"$
        
    'define the primary key
        Dim auditTrailPK As String = "id"
        
    'define the field to autoincrement
        Dim auditTrailAI As String = "id"
        
    Dim fldName As String
        
    Dim fldType As String
        
    'first get the structure of an existing table, lets leave the indexes out
        Dim existingTable As Map = GetTableStructure(jSQL,tblName)
        
    'for each field, create a new table with the cloned fields for old_ and new_
        Dim newFields As Map
        newFields.Initialize
        
    Dim fldTot As Int = existingTable.Size - 1
        
    Dim fldCnt As Int
        
    For fldCnt = 0 To fldTot
            
    'get the fldname and fldtype to create the old_x and new_x
            fldName = existingTable.GetKeyAt(fldCnt)
            fldType = existingTable.GetValueAt(fldCnt)
            
    Dim old_fldName As String = $"old_${fldName}"$
            
    Dim new_fldName As String = $"new_${fldName}"$
            newFields.Put(old_fldName,fldType)
            newFields.Put(new_fldName,fldType)
        
    Next
        
    'just ensure we have an id field
        newFields.Put(auditTrailPK,DB_INTEGER)
        
    'ensure we have an audit date field
        newFields.Put("Audit_Date", DB_TEXT)
        
    'create the audit trail table
        Return SQLiteCreateTable(jSQL,auditTrailTable,newFields,auditTrailPK,auditTrailAI)
    End Sub
     
    Last edited: Aug 16, 2018
  3. Mashiane

    Mashiane Expert Licensed User

    Step 2: Input and output...

    Let's say we have a structure of our existing table as..

    Code:
    CREATE TABLE [people] (
        [
    id] integer NOT NULL PRIMARY KEY AUTOINCREMENT,
        [fullname] text,
        [companyid] text,
        [emailaddress] text,
        [mobilephone] text,
        [jobtitle] text,
        [projectrole] text,
        [profileimg] text,
        [profileupl] text,
        [institution] text,
        [faxnumber] text,
        [contactperson] integer,
        [telephone] text,
        [active] integer,
        [forums] text
    );

    CREATE INDEX [peoplefullname]
        ON [people] ([fullname]);

    CREATE INDEX [peoplecompanyid]
        ON [people] ([companyid]);

    CREATE INDEX [peopleemailaddress]
        ON [people] ([emailaddress]);

    CREATE INDEX [peoplemobilephone]
        ON [people] ([mobilephone]);

    CREATE INDEX [peoplejobtitle]
        ON [people] ([jobtitle]);

    CREATE INDEX [peopleprojectrole]
        ON [people] ([projectrole]);

    CREATE INDEX [peopleinstitution]
        ON [people] ([institution]);
    To create this table on an existing SQLite database, we would call..

    Code:
    dim fldsMap As Map = CreateMap("id":DB_INTEGER, _
    "fullname": DB_TEXT, _
    ...)

    return SQLiteCreateTable(sql,"People", fldsMap, "id""id")
    Then running the audit trail code for the People Table as

    Code:
    return SQLiteCreateAuditTrail(sql,"People")
    will create an audit_trail table called People_Audit, with this structure...

    Code:
    CREATE TABLE [people_Audit] (
        [old_id] integer,
        [new_id] integer,
        [old_fullname] text,
        [new_fullname] text,
        [old_companyid] text,
        [new_companyid] text,
        [old_emailaddress] text,
        [new_emailaddress] text,
        [old_mobilephone] text,
        [new_mobilephone] text,
        [old_jobtitle] text,
        [new_jobtitle] text,
        [old_projectrole] text,
        [new_projectrole] text,
        [old_profileimg] text,
        [new_profileimg] text,
        [old_profileupl] text,
        [new_profileupl] text,
        [old_institution] text,
        [new_institution] text,
        [old_faxnumber] text,
        [new_faxnumber] text,
        [old_contactperson] integer,
        [new_contactperson] integer,
        [old_telephone] text,
        [new_telephone] text,
        [old_active] integer,
        [new_active] integer,
        [old_forums] text,
        [new_forums] text,
        [
    id] integer NOT NULL PRIMARY KEY AUTOINCREMENT,
        [Audit_Date] text
    );
    Step 3: This will talk about the relationship between the people and the people_audit tables that is established by using triggers. Coming soon... ;)
     
    Claudio Oliveira and amaxco like this.
  4. Mashiane

    Mashiane Expert Licensed User

    Step 1 and 2 were about preparing your database to work with the audit trail table. The following steps will deal with the triggers for the three methods, INSERT, UPDATE and DELETE. These are just basic examples.

    Step 3

    To create the trigger, we will call and SQL statement to create it. As these are not like SQL SELECTS statements, these are managed by the database engine internally. Here is a script that will build a simple trigger with a WHEN condition.

    Code:
    'create a trigger when an event happens
    Sub SQLiteCreateTrigger(triggerName As String, tblName As String, triggerWhen As String, triggerEvent As String, triggerWhenSQL As String, triggerSQL As StringAs String
        triggerName = triggerName.Replace(
    " ","")
    Dim script As String = $"CREATE TRIGGER IF NOT EXISTS ${triggerName} ${triggerWhen} ${triggerEvent} ON ${tblName}
    WHEN ${
    triggerWhenSQL}
    BEGIN
    ${
    triggerSQL};
    END;"$

    Return script
    End Sub
    We can this update our constants to be more informative, e.g.

    Code:
    Public const TRIGGER_BEFORE_INSERT As String = "BEFORE INSERT"
        
    Public const TRIGGER_BEFORE_UPDATE As String = "BEFORE UPDATE"
        
    Public const TRIGGER_BEFORE_DELETE As String = "BEFORE DELETE"
     
        
    Public const TRIGGER_AFTER_INSERT As String = "AFTER INSERT"
        
    Public const TRIGGER_AFTER_UPDATE As String = "AFTER UPDATE"
        
    Public const TRIGGER_AFTER_DELETE As String = "AFTER DELETE"
     
        
    Public const TRIGGER_INSTEADOF_INSERT As String = "INSTEAD OF INSERT"
        
    Public const TRIGGER_INSTEADOF_UPDATE As String = "INSTEAD OF UPDATE"
        
    Public const TRIGGER_INSTEADOF_DELETE As String = "INSTEAD OF DELETE"
    and update the SQLiteCreateTrigger method to be

    Code:
    'create a trigger when an event happens
    Sub SQLiteCreateTrigger(triggerName As String, tblName As String, triggerWhenEvent As String, triggerWhenSQL As String, triggerSQL As StringAs String
        triggerName = triggerName.Replace(
    " ","")
    Dim script As String = $"CREATE TRIGGER IF NOT EXISTS ${triggerName} ${triggerWhenEvent} ON ${tblName}
    WHEN ${
    triggerWhenSQL}
    BEGIN
    ${
    triggerSQL}
    END;"$

    Return script
    End Sub
    1. INSERT TRIGGERS

    Here we want each time a record is inserted in the master table, the audit trail is created.

    2. UPDATE TRIGGERS

    Here we want each time a record is updated in the master table, the audit trail is created.

    2.1 BEFORE UPDATE
    2.2 AFTER UPDATE

    The purpose of the after update in this case is to log all the changes identified in the record being updated. This trigger can look like this.

    Code:
    CREATE TRIGGER IF NOT EXISTS people_after_update AFTER UPDATE ON people
    WHEN old.id <> new.id
    OR old.fullname <> new.fullname
    OR old.companyid <> new.companyid
    OR old.emailaddress <> new.emailaddress
    OR old.mobilephone <> new.mobilephone
    OR old.jobtitle <> new.jobtitle
    OR old.projectrole <> new.projectrole
    OR old.profileimg <> new.profileimg
    OR old.profileupl <> new.profileupl
    OR old.institution <> new.institution
    OR old.faxnumber <> new.faxnumber
    OR old.contactperson <> new.contactperson
    OR old.telephone <> new.telephone
    OR old.active <> new.active
    OR old.forums <> new.forums
    BEGIN
    INSERT INTO people_Audit (
    old_id,
    new_id,
    old_fullname,
    new_fullname,
    old_companyid,
    new_companyid,
    old_emailaddress,
    new_emailaddress,
    old_mobilephone,
    new_mobilephone,
    old_jobtitle,
    new_jobtitle,
    old_projectrole,
    new_projectrole,
    old_profileimg,
    new_profileimg,
    old_profileupl,
    new_profileupl,
    old_institution,
    new_institution,
    old_faxnumber,
    new_faxnumber,
    old_contactperson,
    new_contactperson,
    old_telephone,
    new_telephone,
    old_active,
    new_active,
    old_forums,
    new_forums,
    Audit_Action,
    Audit_Date
    )
    VALUES
    (
    old.id,
    new.id,
    old.fullname,
    new.fullname,
    old.companyid,
    new.companyid,
    old.emailaddress,
    new.emailaddress,
    old.mobilephone,
    new.mobilephone,
    old.jobtitle,
    new.jobtitle,
    old.projectrole,
    new.projectrole,
    old.profileimg,
    new.profileimg,
    old.profileupl,
    new.profileupl,
    old.institution,
    new.institution,
    old.faxnumber,
    new.faxnumber,
    old.contactperson,
    new.contactperson,
    old.telephone,
    new.telephone,
    old.active,
    new.active,
    old.forums,
    new.forums,
    'UPDATE',
    DATETIME('NOW')
    );
    END;
    What this means is, whenever any of the fields in the row being added has changed in any of the fields, create an audit trail record with old and new values in the people_Audit trail table.

    people_after_update.png

    As seen in this image, the people_Audit table was created, and after running this command as generated by the code in this article, it "executed successfully to create the after_update" trigger.

    2.3 INSTEAD OF UPDATE

    3. DELETE TRIGGERS

    Here we want each time a record is deleted in the master table, the audit trail is created
     
    Last edited: Aug 16, 2018
    Claudio Oliveira and amaxco like this.
  5. Mashiane

    Mashiane Expert Licensed User

    Above, the AFTER UPDATE SQLite trigger was created easily based on some few lines of code to do so. To reach that stage, two things happened

    1. The code to create the AFTER UPDATE trigger was generated and

    Code:
    'create an update trigger
    Sub SQLiteCreateAfterUpdateTrigger(jSQL As SQL, tblName As StringAs Boolean
        
    'define the trigger name
        Dim triggerName As String = $"${tblName}_after_update"$
        
    'define the audit trail table name
        Dim auditTrailTable As String = $"${tblName}_Audit"$
        
    'first get the structure of an existing table, lets leave the indexes out
        Dim existingTable As Map = GetTableStructure(jSQL,tblName)
        
    'for each existing field, lets build when
        Dim sbWhen As StringBuilder
        sbWhen.Initialize
        
    'for each existing field, lets build inserts
        Dim sbInsert As StringBuilder
        sbInsert.Initialize
        sbInsert.Append(
    $"INSERT INTO ${auditTrailTable} ("$).Append(CRLF)
        
    'for each existing field, lets build values
        Dim sbValues As StringBuilder
        sbValues.Initialize
        
    For Each fldName As String In existingTable.Keys
            
    'when any of the fields are not the same, create an audit trail
            Dim notSame As String = $"old.${fldName} <> new.${fldName}"$
            sbWhen.Append(notSame).Append(
    CRLF).Append("OR ")
            
    'build the insert fiels
            Dim old_fldName As String = $"old_${fldName}"$
            
    Dim new_fldName As String = $"new_${fldName}"$
            sbInsert.Append(old_fldName).Append(
    ",").Append(CRLF)
            sbInsert.Append(new_fldName).Append(
    ",").Append(CRLF)
            
    'build values
            old_fldName = $"old.${fldName}"$
            new_fldName = 
    $"new.${fldName}"$
            sbValues.Append(old_fldName).Append(
    ",").Append(CRLF)
            sbValues.Append(new_fldName).Append(
    ",").Append(CRLF)
        
    Next
        
    'clean when last 'OR '
        sbWhen.Remove(sbWhen.Length-3,sbWhen.Length)
        
    'update the insert to include audit action and audit date
        sbInsert.Append("Audit_Action,").Append(CRLF)
        sbInsert.Append(
    "Audit_Date").Append(CRLF)
        
    'do the same for the values
        sbValues.Append("'UPDATE',").Append(CRLF)
        sbValues.Append(
    "DATETIME('NOW')").Append(CRLF)
        sbInsert.Append(
    ")").Append(CRLF)
        sbInsert.Append(
    "VALUES").Append(CRLF)
        sbInsert.Append(
    "(").Append(CRLF)
        sbInsert.Append(sbValues.ToString)
        sbInsert.Append(
    ");").Append(CRLF)
        
    'lets define the trigger.
        Dim trigger As String = SQLiteCreateTrigger(triggerName,tblName,TRIGGER_AFTER_UPDATE,sbWhen.ToString,sbInsert.ToString)
        jSQL.ExecNonQuery(trigger)
        
    Return SQLiteTriggerExists(jSQL,triggerName)
    End Sub
    2. Code to check if the trigger exists had to be called.

    Code:
    'check to see if SQLite trigger exists
    Sub SQLiteTriggerExists(jSQL As SQL, tblName As StringAs Boolean
        
    Dim res As String
        
    Try
            
    Dim qry As String = $"select name from sqlite_master where type = 'trigger' and lower(name) = ?"$
            res = jSQL.ExecQuerySingleResult2(qry, 
    Array As String(tblName.tolowercase))
        
    Catch
            
    Return False
        
    End Try
        
    If res = Null Then
            
    Return False
        
    Else
            
    Return True
        
    End If
    End Sub
    So in summary...

    1. Open an SQLite connection to your database
    2. Call SQLiteCreateAuditTrail for each table that needs to be audited
    3. For an AFTER UPDATE trigger, call SQLiteCreateAfterUpdateTrigger above for each table that will have AFTER UPDATE trigger. You can then run updates to your database and open the _Audit table to view the results.

    For now, that's all folks.
     
    Claudio Oliveira likes this.
  6. Mashiane

    Mashiane Expert Licensed User

    To drop an existing trigger from a table, you can run this method.

    Code:
    'Deletes the given trigger.
    Public Sub DropTrigger(jSQL As SQL, TableName As StringAs Boolean
        jSQL.ExecNonQuery(
    "DROP TRIGGER IF EXISTS " & TableName)
        
    Return Not(SQLiteTriggerExists(jSQL,TableName))
    End Sub
    Deleting a table will delete the associated trigger for that table.
     
    Claudio Oliveira likes this.
  7. Mashiane

    Mashiane Expert Licensed User

    You might want to also trap the AFTER INSERT trigger so that when a record is added, its recorded to the audit log. In this instance, only the NEW object is available and not OLD.

    An example of such could be...

    Code:
    CREATE TRIGGER IF NOT EXISTS people_after_insert AFTER INSERT ON people
    BEGIN
    INSERT INTO people_Audit (
    new_id,
    new_fullname,
    new_companyid,
    new_emailaddress,
    new_mobilephone,
    new_jobtitle,
    new_projectrole,
    new_profileimg,
    new_profileupl,
    new_institution,
    new_faxnumber,
    new_contactperson,
    new_telephone,
    new_active,
    new_forums,
    Audit_Action,
    Audit_Date
    )
    VALUES
    (
    new.id,
    new.fullname,
    new.companyid,
    new.emailaddress,
    new.mobilephone,
    new.jobtitle,
    new.projectrole,
    new.profileimg,
    new.profileupl,
    new.institution,
    new.faxnumber,
    new.contactperson,
    new.telephone,
    new.active,
    new.forums,
    'CREATE',
    DATETIME('NOW')
    );
    END;
    This code is created easily by this method...

    Code:
    'create an after insert trigger
    Sub SQLiteCreateAfterInsertTrigger(jSQL As SQL, tblName As StringAs Boolean
        
    'define the trigger name
        Dim triggerName As String = $"${tblName}_after_insert"$
        
    'define the audit trail table name
        Dim auditTrailTable As String = $"${tblName}_Audit"$
        
    'first get the structure of an existing table, lets leave the indexes out
        Dim existingTable As Map = GetTableStructure(jSQL,tblName)
        
    'for each existing field, lets build inserts
        Dim sbInsert As StringBuilder
        sbInsert.Initialize
        sbInsert.Append(
    $"INSERT INTO ${auditTrailTable} ("$).Append(CRLF)
        
    'for each existing field, lets build values
        Dim sbValues As StringBuilder
        sbValues.Initialize
        
    For Each fldName As String In existingTable.Keys
            
    'build the insert fiels
            Dim new_fldName As String = $"new_${fldName}"$
            sbInsert.Append(new_fldName).Append(
    ",").Append(CRLF)
            
    'build values
            new_fldName = $"new.${fldName}"$
            sbValues.Append(new_fldName).Append(
    ",").Append(CRLF)
        
    Next
        
    'update the insert to include audit action and audit date
        sbInsert.Append("Audit_Action,").Append(CRLF)
        sbInsert.Append(
    "Audit_Date").Append(CRLF)
        
    'do the same for the values
        sbValues.Append("'CREATE',").Append(CRLF)
        sbValues.Append(
    "DATETIME('NOW')").Append(CRLF)
        sbInsert.Append(
    ")").Append(CRLF)
        sbInsert.Append(
    "VALUES").Append(CRLF)
        sbInsert.Append(
    "(").Append(CRLF)
        sbInsert.Append(sbValues.ToString)
        sbInsert.Append(
    ");").Append(CRLF)
        
    'lets define the trigger.
        Dim trigger As String = SQLiteCreateTrigger(triggerName,tblName,TRIGGER_AFTER_INSERT,sbInsert.ToString)
        jSQL.ExecNonQuery(trigger)
        
    Return SQLiteTriggerExists(jSQL,triggerName)
    End Sub
    The difference here is that there is NO WHEN clause just like the previous AFTER UPDATE, there we were checking if anything has changed and added it to the _audit file if something changed, if not, nothing would be added to the _audit. So this one, as soon as the record is added, it will be added to the audit log.

    I will look into AFTER DELETE later on...
     
    Claudio Oliveira likes this.
  8. Mashiane

    Mashiane Expert Licensed User

    As you might have guessed, AFTER DELETE is an INVERSE of AFTER INSERT but it uses the OLD object. The result could be something like this..

    Code:
    CREATE TRIGGER IF NOT EXISTS people_after_delete AFTER DELETE ON people
    BEGIN
    INSERT INTO people_Audit (
    old_id,
    old_fullname,
    old_companyid,
    old_emailaddress,
    old_mobilephone,
    old_jobtitle,
    old_projectrole,
    old_profileimg,
    old_profileupl,
    old_institution,
    old_faxnumber,
    old_contactperson,
    old_telephone,
    old_active,
    old_forums,
    Audit_Action,
    Audit_Date
    )
    VALUES
    (
    old.id,
    old.fullname,
    old.companyid,
    old.emailaddress,
    old.mobilephone,
    old.jobtitle,
    old.projectrole,
    old.profileimg,
    old.profileupl,
    old.institution,
    old.faxnumber,
    old.contactperson,
    old.telephone,
    old.active,
    old.forums,
    'DELETE',
    DATETIME('NOW')
    );
    END;
    This code is also generated by calling this code..

    Code:
    Sub SQLiteCreateAfterDeleteTrigger(jSQL As SQL, tblName As StringAs Boolean
        
    'define the trigger name
        Dim triggerName As String = $"${tblName}_after_delete"$
        
    'define the audit trail table name
        Dim auditTrailTable As String = $"${tblName}_Audit"$
        
    'first get the structure of an existing table, lets leave the indexes out
        Dim existingTable As Map = GetTableStructure(jSQL,tblName)
        
    'for each existing field, lets build inserts
        Dim sbInsert As StringBuilder
        sbInsert.Initialize
        sbInsert.Append(
    $"INSERT INTO ${auditTrailTable} ("$).Append(CRLF)
        
    'for each existing field, lets build values
        Dim sbValues As StringBuilder
        sbValues.Initialize
        
    For Each fldName As String In existingTable.Keys
            
    'build the insert fiels
            Dim new_fldName As String = $"old_${fldName}"$
            sbInsert.Append(new_fldName).Append(
    ",").Append(CRLF)
            
    'build values
            new_fldName = $"old.${fldName}"$
            sbValues.Append(new_fldName).Append(
    ",").Append(CRLF)
        
    Next
        
    'update the insert to include audit action and audit date
        sbInsert.Append("Audit_Action,").Append(CRLF)
        sbInsert.Append(
    "Audit_Date").Append(CRLF)
        
    'do the same for the values
        sbValues.Append("'DELETE',").Append(CRLF)
        sbValues.Append(
    "DATETIME('NOW')").Append(CRLF)
        sbInsert.Append(
    ")").Append(CRLF)
        sbInsert.Append(
    "VALUES").Append(CRLF)
        sbInsert.Append(
    "(").Append(CRLF)
        sbInsert.Append(sbValues.ToString)
        sbInsert.Append(
    ");").Append(CRLF)
        
    'lets define the trigger.
        Dim trigger As String = SQLiteCreateTrigger(triggerName,tblName,TRIGGER_AFTER_DELETE,sbInsert.ToString)
        jSQL.ExecNonQuery(trigger)
        
    Return SQLiteTriggerExists(jSQL,triggerName)
    End Sub
     
    Claudio Oliveira likes this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice