B4J Tutorial SQLite Triggers: Creating Audit Trails For Dummies

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.

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.

'get the table structure from the pragma statement
Sub GetTableStructure(jSQL As SQL, tblName As String) As 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...

 

[Mashiane]

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

Private Sub EscapeField(f As String) As String
    Return $"[${f}]"$
End Sub

'check to see if SQLite table exists
Sub SQLiteTableExists(jSQL As SQL, tblName As String) As 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 String) As 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.

'create an audit trail
Sub SQLiteCreateAuditTrail(jSQL As SQL, tblName As String) As 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

 

[Mashiane]

Step 2: Input and output...

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

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..

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

return SQLiteCreateAuditTrail(sql,"People")

will create an audit_trail table called People_Audit, with this structure...

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...

 

[Mashiane]

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.

'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 String) As 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.

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

'create a trigger when an event happens
Sub SQLiteCreateTrigger(triggerName As String, tblName As String, triggerWhenEvent As String, triggerWhenSQL As String, triggerSQL As String) As 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.

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.

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

 

[Mashiane]

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

'create an update trigger
Sub SQLiteCreateAfterUpdateTrigger(jSQL As SQL, tblName As String) As 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.

'check to see if SQLite trigger exists
Sub SQLiteTriggerExists(jSQL As SQL, tblName As String) As 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.

 

[Mashiane]

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

'Deletes the given trigger.
Public Sub DropTrigger(jSQL As SQL, TableName As String) As 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.

 

[Mashiane]

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...

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...

'create an after insert trigger
Sub SQLiteCreateAfterInsertTrigger(jSQL As SQL, tblName As String) As 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...

 

[Mashiane]

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..

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..

Sub SQLiteCreateAfterDeleteTrigger(jSQL As SQL, tblName As String) As 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