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))));
}
