public static string ScriptForMissingColumns(CodeTableAudit codeTable)
{
//if(SqlSync.DbInformation.InfoHelper.UpdateDateFields.Length == 0 || SqlSync.DbInformation.InfoHelper.UpdateIdFields.Length == 0)
if (!SqlSync.DbInformation.InfoHelper.codeTableAuditCols.IsValid)
{
SqlSync.DbInformation.InfoHelper.SetUpdateColumnNames();
}
string tableName = codeTable.TableName;
string schema;
InfoHelper.ExtractNameAndSchema(tableName, out tableName, out schema);
//Create the columns as null
StringBuilder sb = new StringBuilder();
if (codeTable.CreateIdColumn.Length == 0)
{
sb.Append(ScriptTemplates.ADD_UPDATE_ID_COLUMN.Replace("<<tableName>>", tableName)
.Replace("<<schema>>", schema)
.Replace("<<columnName>>", SqlSync.DbInformation.InfoHelper.codeTableAuditCols.CreateIdColumns[0])
.Replace("<<userName>>", System.Environment.UserName));
}
if (codeTable.CreateDateColumn.Length == 0)
{
sb.Append(ScriptTemplates.ADD_UPDATE_DATE_COLUMN.Replace("<<tableName>>", tableName)
.Replace("<<schema>>", schema)
.Replace("<<columnName>>", SqlSync.DbInformation.InfoHelper.codeTableAuditCols.CreateDateColumns[0])
.Replace("<<date>>", DateTime.Now.ToString()));
}
if (codeTable.UpdateIdColumn.Length == 0)
{
sb.Append(ScriptTemplates.ADD_UPDATE_ID_COLUMN.Replace("<<tableName>>", tableName)
.Replace("<<schema>>", schema)
.Replace("<<columnName>>", SqlSync.DbInformation.InfoHelper.codeTableAuditCols.UpdateIdColumns[0])
.Replace("<<userName>>", System.Environment.UserName));
}
if (codeTable.UpdateDateColumn.Length == 0)
{
sb.Append(ScriptTemplates.ADD_UPDATE_DATE_COLUMN.Replace("<<tableName>>", tableName)
.Replace("<<schema>>", schema)
.Replace("<<columnName>>", SqlSync.DbInformation.InfoHelper.codeTableAuditCols.UpdateDateColumns[0])
.Replace("<<date>>", DateTime.Now.ToString()));
}
return(sb.ToString());
}
public static string ScriptColumnDefaultsReset(CodeTableAudit codeTable)
{
string tableName = codeTable.TableName;
string schema;
InfoHelper.ExtractNameAndSchema(tableName, out tableName, out schema);
StringBuilder sb = new StringBuilder();
if (codeTable.UpdateIdColumn.Length > 0)
{
sb.Append(ScriptTemplates.DropExistingDefaultConstraint.Replace("<<tableName>>", tableName)
.Replace("<<schema>>", schema)
.Replace("<<columnName>>", codeTable.UpdateIdColumn)
.Replace("<<defaultValue>>", "SYSTEM_USER"));
}
if (codeTable.UpdateDateColumn.Length > 0)
{
sb.Append(ScriptTemplates.DropExistingDefaultConstraint.Replace("<<tableName>>", tableName)
.Replace("<<schema>>", schema)
.Replace("<<columnName>>", codeTable.UpdateDateColumn)
.Replace("<<defaultValue>>", "getdate()"));
}
if (codeTable.CreateIdColumn.Length > 0)
{
sb.Append(ScriptTemplates.DropExistingDefaultConstraint.Replace("<<tableName>>", tableName)
.Replace("<<schema>>", schema)
.Replace("<<columnName>>", codeTable.CreateIdColumn)
.Replace("<<defaultValue>>", "SYSTEM_USER"));
}
if (codeTable.CreateDateColumn.Length > 0)
{
sb.Append(ScriptTemplates.DropExistingDefaultConstraint.Replace("<<tableName>>", tableName)
.Replace("<<schema>>", schema)
.Replace("<<columnName>>", codeTable.CreateDateColumn)
.Replace("<<defaultValue>>", "getdate()"));
}
return(sb.ToString());
}
public static string ScriptForUpdateTrigger(CodeTableAudit auditTable, ConnectionData connData)
{
string updateDateCol = auditTable.UpdateDateColumn;
string updateIdCol = auditTable.UpdateIdColumn;
string tableName = auditTable.TableName;
string schema;
InfoHelper.ExtractNameAndSchema(tableName, out tableName, out schema);
string triggerName = String.Format(updateTrigFormat, tableName, schema);
string shortTrigger = String.Format(shortTrigFormat, tableName);
StringBuilder remove = new StringBuilder();
remove.Append("IF EXISTS (SELECT name FROM sys.objects WHERE name = '" + shortTrigger + "' AND type = 'TR')\r\n");
remove.Append("\tDROP TRIGGER " + triggerName + "\r\n");
remove.Append("GO\r\n\r\n");
if (updateDateCol == string.Empty || updateIdCol == string.Empty)
{
return(string.Empty);
}
ArrayList pkCols = GetPrimaryKeyColumnsWithType(auditTable.TableName, connData);
if (pkCols.Count == 0)
{
return(string.Empty);
}
StringBuilder sb = new StringBuilder();
sb.Append("CREATE TRIGGER " + triggerName + " ON [" + schema + "].[" + tableName + "] FOR UPDATE, INSERT\r\nAS\r\nBEGIN\r\n");
for (int i = 0; i < pkCols.Count; i++)
{
string[] col = (string[])pkCols[i];
sb.Append("\tDECLARE @" + col[0].Replace(" ", "") + " " + col[1] + "\r\n");
}
sb.Append("\r\n\tSELECT ");
for (int i = 0; i < pkCols.Count; i++)
{
string[] col = (string[])pkCols[i];
sb.Append("@" + col[0].Replace(" ", "") + "=[" + col[0] + "],");
}
sb.Length = sb.Length - 1;
sb.Append(" FROM inserted\r\n\r\n");
sb.Append("\tIF NOT EXISTS(SELECT 1 FROM [" + schema + "].[" + tableName + "] WHERE ");
for (int i = 0; i < pkCols.Count; i++)
{
string[] col = (string[])pkCols[i];
sb.Append("[" + col[0] + "]=@" + col[0].Replace(" ", "") + " AND ");
}
sb.Length = sb.Length - 4;
sb.Append(")\r\n");
sb.Append("\t\tRAISERROR('Unable to complete UpdateId/UpdateDate auditing trigger for [" + schema + "].[" + tableName + "] table. PK Value Not Found. Change Rolled Back.',16,1)\r\n\r\n");
sb.Append("\r\n\tSELECT @@nestlevel");
sb.Append("\r\n\tIF @@nestlevel <= 10");
sb.Append("\r\n\tBEGIN");
sb.Append("\r\n\t\tUPDATE [" + schema + "].[" + tableName + "] SET [" + updateDateCol + "] = getdate(), [" + updateIdCol + "] = SYSTEM_USER\r\n");
sb.Append("\t\tWHERE ");
for (int i = 0; i < pkCols.Count; i++)
{
string[] col = (string[])pkCols[i];
sb.Append("[" + col[0] + "]=@" + col[0].Replace(" ", "") + " AND ");
}
sb.Length = sb.Length - 4;
sb.Append("\r\n\r\n\t\tIF(@@Error <> 0)\r\n");
sb.Append("\t\t\tRAISERROR('Unable to complete UpdateId/UpdateDate auditing trigger for [" + schema + "].[" + tableName + "] table. Change Rolled Back.',16,1)\r\n");
sb.Append("\r\n\tEND");
sb.Append("\r\nEND");
sb.Append("\r\nGO\r\n");
return(remove.ToString() + sb.ToString());
}
