/// <summary>
/// Creates the delete trigger to keep track of the row count.
/// Basically every delete command subtracts +1 to the row count.
/// <para/>
/// SQL query Example: <para/>
/// create trigger if not exists table_trigger_sub after delete on table BEGIN update table_count set rowCount = rowCount - 1 where id = 1; END
/// </summary>
/// <param name="tableName">The name of the table.</param>
/// <returns>The finished sql string.</returns>
private string CreateCounterSubTriger(string tableName)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
sqb.Create().Trigger().IfNotExists().AddValue(tableName + "_trigger_sub").After().Delete().On().AddValue(tableName);
sqb.Begin().Update().AddValue(tableName + "_count").Set().AddValue("rowCount").Equal().AddValue("rowCount - 1");
sqb.Where().AddValue("id").Equal().AddValue("1").CommaPoint(true).End();
return(sqb.ToString());
}
/// <summary>
/// Creates the insert trigger to keep track of the row count.
/// Basically every insert command adds +1 to the row count.
/// <para/>
/// SQL query Example: <para/>
/// create trigger if not exists table_trigger_add after insert on table BEGIN update table_count set rowCount = rowCount + 1 where id = 1; END
/// </summary>
/// <param name="tableName">The name of the table.</param>
/// <returns>The finished sql string.</returns>
private string CreateCounterAddTriger(string tableName)
{
try
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
sqb.Create().Trigger().IfNotExists().AddValue(tableName + "_trigger_add").After().Insert().On().AddValue(tableName);
sqb.Begin().Update().AddValue(tableName + "_count").Set().AddValue("rowCount").Equal().AddValue("rowCount + 1");
sqb.Where().AddValue("id").Equal().AddValue("1").CommaPoint(true).End();
return(sqb.ToString());
}
catch (Exception)
{
throw;
}
}