private string DeleteTriggerBodyText() { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append(OracleBuilderTrigger.CreateBeginTrigger()); stringBuilder.AppendLine(); stringBuilder.AppendLine($"UPDATE {trackingName.UnquotedString} "); stringBuilder.AppendLine("SET \tsync_row_is_tombstone = 1"); stringBuilder.AppendLine("\t,update_scope_id = NULL -- since the update if from local, it's a NULL"); stringBuilder.AppendLine($"\t,update_timestamp = {OracleObjectNames.TimestampValue}"); stringBuilder.AppendLine($"\t,timestamp = {OracleObjectNames.TimestampValue}"); stringBuilder.AppendLine("\t,last_change_datetime = sysdate"); // Filter columns if (this.Filters != null && Filters.Count > 0) { foreach (var filter in this.Filters) { var columnFilter = this.tableDescription.Columns[filter.ColumnName]; if (columnFilter == null) { throw new InvalidExpressionException($"Column {filter.ColumnName} does not exist in Table {this.tableDescription.TableName}"); } if (this.tableDescription.PrimaryKey.Columns.Any(c => c.ColumnName == columnFilter.ColumnName)) { continue; } ObjectNameParser columnName = new ObjectNameParser(columnFilter.ColumnName); stringBuilder.AppendLine($"\t,{columnName.UnquotedString} = :old.{columnName.UnquotedString}"); } stringBuilder.AppendLine(); } stringBuilder.AppendLine($"WHERE "); stringBuilder.AppendLine(OracleManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKey.Columns, trackingName.UnquotedString, ":old") + " ;"); stringBuilder.AppendLine(OracleBuilderTrigger.CreateEndTrigger()); return(stringBuilder.ToString()); }
private string InsertTriggerBodyText() { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append(OracleBuilderTrigger.CreateBeginTrigger()); stringBuilder.AppendLine(); stringBuilder.AppendLine("-- If row was deleted before, it already exists, so just make an update"); stringBuilder.AppendLine($"UPDATE {trackingName.UnquotedString} "); stringBuilder.AppendLine("SET \tsync_row_is_tombstone = 0"); stringBuilder.AppendLine("\t,update_scope_id = NULL -- since the update if from local, it's a NULL"); stringBuilder.AppendLine("\t,last_change_datetime = sysdate "); // Filter columns if (this.Filters != null && Filters.Count > 0) { foreach (var filter in Filters) { var columnFilter = this.tableDescription.Columns[filter.ColumnName]; if (columnFilter == null) { throw new InvalidExpressionException($"Column {filter.ColumnName} does not exist in Table {this.tableDescription.TableName}"); } if (this.tableDescription.PrimaryKey.Columns.Any(c => c.ColumnName == columnFilter.ColumnName)) { continue; } stringBuilder.AppendLine($"\t,{columnFilter.ColumnName} = :new.{columnFilter.ColumnName}"); } stringBuilder.AppendLine(); } stringBuilder.AppendLine($"WHERE "); stringBuilder.AppendLine(OracleManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKey.Columns, trackingName.UnquotedString, ":new") + " ;"); stringBuilder.AppendLine(); stringBuilder.AppendLine($"INSERT INTO {trackingName.UnquotedString} ("); StringBuilder stringBuilderArguments = new StringBuilder(); StringBuilder stringBuilderArguments2 = new StringBuilder(); StringBuilder stringPkAreNull = new StringBuilder(); string argComma = string.Empty; string argAnd = string.Empty; foreach (var mutableColumn in this.tableDescription.PrimaryKey.Columns.Where(c => !c.ReadOnly)) { ObjectNameParser columnName = new ObjectNameParser(mutableColumn.ColumnName); stringBuilderArguments.AppendLine($"\t{argComma} {trackingName.UnquotedString}.{columnName.UnquotedString}"); stringBuilderArguments2.AppendLine($"\t{argComma} :new.{columnName.UnquotedString}"); stringPkAreNull.Append($"{argAnd} :new.{columnName.UnquotedString} IS NULL"); argComma = ","; argAnd = " AND "; } stringBuilder.Append(stringBuilderArguments.ToString()); stringBuilder.AppendLine("\t,create_scope_id"); stringBuilder.AppendLine("\t,create_timestamp"); stringBuilder.AppendLine("\t,update_scope_id"); stringBuilder.AppendLine("\t,update_timestamp"); stringBuilder.AppendLine("\t,timestamp"); stringBuilder.AppendLine("\t,sync_row_is_tombstone"); stringBuilder.AppendLine("\t,last_change_datetime"); StringBuilder filterColumnsString = new StringBuilder(); // Filter columns if (this.Filters != null && Filters.Count > 0) { foreach (var filter in Filters) { var columnFilter = this.tableDescription.Columns[filter.ColumnName]; if (columnFilter == null) { throw new InvalidExpressionException($"Column {filter.ColumnName} does not exist in Table {this.tableDescription.TableName}"); } if (this.tableDescription.PrimaryKey.Columns.Any(c => c.ColumnName == columnFilter.ColumnName)) { continue; } ObjectNameParser columnName = new ObjectNameParser(columnFilter.ColumnName); filterColumnsString.AppendLine($"\t, :new.{columnName.UnquotedString}"); } stringBuilder.AppendLine(filterColumnsString.ToString()); } stringBuilder.AppendLine(") "); stringBuilder.AppendLine("VALUES ("); stringBuilder.Append(stringBuilderArguments2.ToString()); stringBuilder.AppendLine("\t,NULL"); stringBuilder.AppendLine($"\t,{OracleObjectNames.TimestampValue}"); stringBuilder.AppendLine("\t,NULL"); stringBuilder.AppendLine("\t,0"); stringBuilder.AppendLine($"\t,{OracleObjectNames.TimestampValue}"); stringBuilder.AppendLine("\t,0"); stringBuilder.AppendLine("\t,sysdate"); if (Filters != null) { stringBuilder.AppendLine(filterColumnsString.ToString()); } stringBuilder.AppendLine("\t);"); stringBuilder.AppendLine(OracleBuilderTrigger.CreateEndTrigger()); return(stringBuilder.ToString()); }