public override IEnumerable <string> CreateEventTrigger(IPlatformTableSourceEventTriggerInfo newTrigger, IPlatformTableSourceColumnInfo triggerTablePrimaryKeyColumn, IEnumerable <IPlatformTableSourceColumnInfo> triggerTableEventColumns, IEnumerable <ITableSourceForeignKeyInfo> triggerTableForeignKeys, ITableSourceInfo eventTable, ITableSourceInfo eventQueueTable, ITableSourceInfo lightEventQueueTable) { var sql = new StringBuilder(); string triggerName = ((newTrigger.Name.ToUpperInvariant().EndsWith("_I") || newTrigger.Name.ToUpperInvariant().EndsWith("_U")) ? newTrigger.Name.Substring(0, newTrigger.Name.Length - 2) : newTrigger.Name).Right(Identifiers.MaxLength - 2); ITableSourceInfo table = newTrigger.TableSource; sql.Append("CREATE TRIGGER " + Identifiers.EscapeIdentifier(triggerName + "_I")); sql.Append(" AFTER INSERT ON " + Identifiers.EscapeIdentifier(table.Name)); sql.Append(" FOR EACH ROW "); sql.Append(" BEGIN "); sql.Append(" DECLARE isUpdating integer default 0;"); FillEventTriggerQuery(sql, triggerTablePrimaryKeyColumn, triggerTableEventColumns, triggerTableForeignKeys, eventTable, eventQueueTable, lightEventQueueTable, "NEW", false, "isUpdating"); sql.AppendLine(" END;"); sql.Append("CREATE TRIGGER " + Identifiers.EscapeIdentifier(triggerName + "_U")); sql.Append(" AFTER UPDATE ON " + Identifiers.EscapeIdentifier(table.Name)); sql.Append(" FOR EACH ROW "); sql.Append(" BEGIN "); sql.Append(" DECLARE isUpdating integer default 1;"); FillEventTriggerQuery(sql, triggerTablePrimaryKeyColumn, triggerTableEventColumns, triggerTableForeignKeys, eventTable, eventQueueTable, lightEventQueueTable, "NEW", false, "isUpdating"); sql.AppendLine(" END;"); return(sql.ToString().ToEnumerable()); }
/// <summary> /// This method generates the SQL to drop a trigger previously obtained through the IIntrospectionService API. /// This implementation returns "DROP TRIGGER FullyQualifiedTriggerName" /// </summary> /// <param name="existingTrigger">Info about the trigger to drop.</param> /// <returns>SQL statements to drop the trigger.</returns> public virtual IEnumerable <string> DropEventTrigger(IPlatformTableSourceEventTriggerInfo existingTrigger) { string statement = String.Format("DROP TRIGGER {0}", Identifiers.EscapeAndQualifyIdentifier(existingTrigger.TableSource.Database, existingTrigger.Name)); return(statement.ToEnumerable()); }
//dnv: review this hack public override IEnumerable <string> DropEventTrigger(IPlatformTableSourceEventTriggerInfo existingTrigger) { var sql = new StringBuilder(); string triggerName = existingTrigger.Name; string secondTriggerName = triggerName.ToUpperInvariant().EndsWith("_I") ? triggerName.Substring(0, triggerName.Length - 2) + "_U" : triggerName.Substring(0, triggerName.Length - 2) + "_I"; List <string> statements = new List <string>(); statements.Add(String.Format("drop trigger if exists {0}", Identifiers.EscapeAndQualifyIdentifier(existingTrigger.TableSource.Database, triggerName))); statements.Add(String.Format("drop trigger if exists {0}", Identifiers.EscapeAndQualifyIdentifier(existingTrigger.TableSource.Database, secondTriggerName))); return(statements); }
public override IEnumerable <string> CreateEventTrigger(IPlatformTableSourceEventTriggerInfo newTrigger, IPlatformTableSourceColumnInfo triggerTablePrimaryKeyColumn, IEnumerable <IPlatformTableSourceColumnInfo> triggerTableEventColumns, IEnumerable <ITableSourceForeignKeyInfo> triggerTableForeignKeys, ITableSourceInfo eventTable, ITableSourceInfo eventQueueTable, ITableSourceInfo lightEventQueueTable) { string triggername = Identifiers.EscapeIdentifier(newTrigger.Name); var sql = new StringBuilder(); sql.AppendFormat("CREATE TRIGGER {0} ON {1} AFTER INSERT, UPDATE AS", triggername, newTrigger.TableSource.QualifiedName); sql.Append(" BEGIN"); sql.Append(" DECLARE @isupdate BIT;"); sql.Append(" SET @isupdate=1;"); sql.Append(" IF (SELECT COUNT(1) FROM deleted)=0 SET @isupdate=0;"); FillEventTriggerQuery(sql, triggerTablePrimaryKeyColumn, triggerTableEventColumns, triggerTableForeignKeys, eventTable, eventQueueTable, lightEventQueueTable, "inserted", true, "@isupdate"); sql.Append(";"); sql.Append(" END"); yield return(GetSpecialSchemaStatement(newTrigger.TableSource.Database, sql.ToString())); }
public override IEnumerable <string> CreateEventTrigger(IPlatformTableSourceEventTriggerInfo newTrigger, IPlatformTableSourceColumnInfo triggerTablePrimaryKeyColumn, IEnumerable <IPlatformTableSourceColumnInfo> triggerTableEventColumns, IEnumerable <ITableSourceForeignKeyInfo> triggerTableForeignKeys, ITableSourceInfo eventTable, ITableSourceInfo eventQueueTable, ITableSourceInfo lightEventQueueTable) { var createStatement = new StringBuilder(); ITableSourceInfo table = newTrigger.TableSource; createStatement.Append("CREATE OR REPLACE TRIGGER " + Identifiers.EscapeAndQualifyIdentifier(table.Database, newTrigger.Name)); createStatement.Append(" AFTER INSERT OR UPDATE ON " + Identifiers.EscapeIdentifier(table.Name)); createStatement.Append(" FOR EACH ROW"); createStatement.Append(" DECLARE isUpdating NUMBER(1,0) := 0;"); createStatement.Append(" BEGIN "); createStatement.Append(" IF UPDATING THEN"); createStatement.Append(" isUpdating := 1;"); createStatement.Append(" END IF;"); FillEventTriggerQuery(createStatement, triggerTablePrimaryKeyColumn, triggerTableEventColumns, triggerTableForeignKeys, eventTable, eventQueueTable, lightEventQueueTable, ":new", false, "isUpdating"); createStatement.Append(" END;"); return(createStatement.ToString().ToEnumerable()); }
public abstract IEnumerable <string> CreateEventTrigger(IPlatformTableSourceEventTriggerInfo newTrigger, IPlatformTableSourceColumnInfo triggerTablePrimaryKeyColumn, IEnumerable <IPlatformTableSourceColumnInfo> triggerTableEventColumns, IEnumerable <ITableSourceForeignKeyInfo> triggerTableForeignKeys, ITableSourceInfo eventTable, ITableSourceInfo eventQueueTable);
public override IEnumerable <string> DropEventTrigger(IPlatformTableSourceEventTriggerInfo existingTrigger) { yield return(GetSpecialSchemaStatement(existingTrigger.TableSource.Database, String.Format("DROP TRIGGER {0}", Identifiers.EscapeIdentifier(existingTrigger.Name)))); }