private void SetupInsertUpdateTrigger(SQL.DataClasses.Table Table) { StringBuilder Columns = new StringBuilder(); StringBuilder Builder = new StringBuilder(); Builder.Append("CREATE TRIGGER dbo.").Append(Table.Name).Append("_Audit_IU ON dbo.") .Append(Table.Name).Append(" FOR INSERT,UPDATE AS IF @@rowcount=0 RETURN declare @AuditType") .Append(" char(1) declare @DeletedCount int SELECT @DeletedCount=count(*) FROM DELETED IF @DeletedCount=0") .Append(" BEGIN SET @AuditType='I' END ELSE BEGIN SET @AuditType='U' END") .Append(" INSERT INTO dbo.").Append(Table.Name).Append("Audit").Append("("); string Splitter = ""; foreach (Utilities.SQL.DataClasses.Interfaces.IColumn Column in Table.Columns) { Columns.Append(Splitter).Append(Column.Name); Splitter = ","; } Builder.Append(Columns.ToString()); Builder.Append(",AuditType) SELECT "); Builder.Append(Columns.ToString()); Builder.Append(",@AuditType FROM inserted"); Table.AddTrigger(Table.Name + "_Audit_IU", Builder.ToString(), TriggerType.INSERT); }
private void SetupDeleteTrigger(SQL.DataClasses.Table Table) { StringBuilder Columns = new StringBuilder(); StringBuilder Builder = new StringBuilder(); Builder.Append("CREATE TRIGGER dbo.").Append(Table.Name).Append("_Audit_D ON dbo.") .Append(Table.Name).Append(" FOR DELETE AS IF @@rowcount=0 RETURN") .Append(" INSERT INTO dbo.").Append(Table.Name).Append("Audit").Append("("); string Splitter = ""; foreach (Utilities.SQL.DataClasses.Interfaces.IColumn Column in Table.Columns) { Columns.Append(Splitter).Append(Column.Name); Splitter = ","; } Builder.Append(Columns.ToString()); Builder.Append(",AuditType) SELECT "); Builder.Append(Columns.ToString()); Builder.Append(",'D' FROM deleted"); Table.AddTrigger(Table.Name + "_Audit_D", Builder.ToString(), TriggerType.INSERT); }