public string DropTrigger(DatabaseTrigger trigger) { return(string.Format(CultureInfo.InvariantCulture, DropTriggerFormat, SchemaPrefix(trigger.SchemaOwner), Escape(trigger.Name))); }
public override string AddTrigger(DatabaseTable databaseTable, DatabaseTrigger trigger) { //mysql //CREATE TRIGGER (triggerName) //(BEFORE | AFTER) ([INSERT ] | [ UPDATE (OF Column) ] | [ DELETE ]) //ON (tableName) //FOR EACH ROW (sql_statement) /* * CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN * INSERT INTO film_text (film_id, title, description) * VALUES (new.film_id, new.title, new.description); * END;; */ var sb = new StringBuilder(); sb.AppendLine("DELIMITER ;;"); sb.AppendLine("CREATE TRIGGER " + SchemaPrefix(trigger.SchemaOwner) + Escape(trigger.Name)); sb.AppendLine(trigger.TriggerType + " " + trigger.TriggerEvent); sb.AppendLine("ON " + TableName(databaseTable)); sb.AppendLine("FOR EACH ROW"); sb.AppendLine(trigger.TriggerBody + ";;"); sb.AppendLine("DELIMITER ;"); return(sb.ToString()); }
public static async Task AnalyseTables_GivenTableWithDisabledTrigger_ProducesMessages() { var rule = new DisabledObjectsRule(RuleLevel.Error); var testTrigger = new DatabaseTrigger( "test_check", "test_check_definition", TriggerQueryTiming.After, TriggerEvent.Insert, false ); var table = new RelationalDatabaseTable( "test", new List <IDatabaseColumn>(), null, Array.Empty <IDatabaseKey>(), Array.Empty <IDatabaseRelationalKey>(), Array.Empty <IDatabaseRelationalKey>(), Array.Empty <IDatabaseIndex>(), Array.Empty <IDatabaseCheckConstraint>(), new[] { testTrigger } ); var tables = new[] { table }; var hasMessages = await rule.AnalyseTables(tables).AnyAsync().ConfigureAwait(false); Assert.That(hasMessages, Is.True); }
public virtual string AddTrigger(DatabaseTable databaseTable, DatabaseTrigger trigger) { return(string.Format(CultureInfo.InvariantCulture, @"-- CREATE TRIGGER {0}{1} {2} ON {3};", SchemaPrefix(trigger.SchemaOwner), Escape(trigger.Name), trigger.TriggerEvent, TableName(databaseTable))); }
public override string AddTrigger(DatabaseTable databaseTable, DatabaseTrigger trigger) { if (string.IsNullOrEmpty(trigger.TriggerBody)) { return("-- add trigger " + trigger.Name); } //db2 returns the entire "Create trigger" statement, so this is very easy return(trigger.TriggerBody + ";"); }
public static void QueryTiming_PropertyGet_EqualsCtorArg() { Identifier triggerName = "test_trigger"; const string definition = "create trigger test_trigger..."; const TriggerQueryTiming timing = TriggerQueryTiming.Before; const TriggerEvent events = TriggerEvent.Update; var trigger = new DatabaseTrigger(triggerName, definition, timing, events, true); Assert.That(trigger.QueryTiming, Is.EqualTo(timing)); }
public static void IsEnabled_WhenFalseProvidedInCtor_ReturnsFalse() { Identifier triggerName = "test_trigger"; const string definition = "create trigger test_trigger..."; const TriggerQueryTiming timing = TriggerQueryTiming.Before; const TriggerEvent events = TriggerEvent.Update; var trigger = new DatabaseTrigger(triggerName, definition, timing, events, false); Assert.That(trigger.IsEnabled, Is.False); }
public void BuildDropTrigger(DatabaseTrigger trigger) { try { var txt = _migrationGenerator.DropTrigger(trigger); Clipboard.SetText(txt, TextDataFormat.UnicodeText); } catch (Exception exception) { Debug.WriteLine(exception.Message); } }
public static void ToString_WhenInvoked_ReturnsExpectedValues(string name, string expectedResult) { var triggerName = Identifier.CreateQualifiedIdentifier(name); const string definition = "create trigger test_trigger..."; const TriggerQueryTiming timing = TriggerQueryTiming.Before; const TriggerEvent events = TriggerEvent.Update; var trigger = new DatabaseTrigger(triggerName, definition, timing, events, false); var result = trigger.ToString(); Assert.That(result, Is.EqualTo(expectedResult)); }
public override string AddTrigger(DatabaseTable databaseTable, DatabaseTrigger trigger) { //CREATE TRIGGER notify_dept AFTER INSERT OR UPDATE OR DELETE //ON DEPT //EXECUTE PROCEDURE note_dept(); if (string.IsNullOrEmpty(trigger.TriggerBody)) { return("-- add trigger " + trigger.Name); } return(trigger.TriggerBody + ";"); }
public string BuildAddTrigger(DatabaseTable databaseTable, DatabaseTrigger trigger) { StringBuilder sb = new StringBuilder(); try { sb.Append(m_migrationGenerator.AddTrigger(databaseTable, trigger)); } catch (Exception exception) { Debug.WriteLine(exception.Message); } return(sb.ToString()); }
public override string AddTrigger(DatabaseTable databaseTable, DatabaseTrigger trigger) { //sqlserver: //CREATE TRIGGER (triggerName) //ON (tableName) //(FOR | AFTER | INSTEAD OF) ( [INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ]) //AS (sql_statement); GO //nicely, SQLServer gives you the entire sql including create statement in TriggerBody if (string.IsNullOrEmpty(trigger.TriggerBody)) { return("-- add trigger " + trigger.Name); } return(trigger.TriggerBody + ";"); }
public override string AddTrigger(DatabaseTable databaseTable, DatabaseTrigger trigger) { //sqlite: //CREATE TRIGGER (triggerName) (IF NOT EXISTS) //(BEFORE | AFTER | INSTEAD OF) ([INSERT ] | [ UPDATE (OF Column) ] | [ DELETE ]) //ON (tableName) //(FOR EACH ROW) //BEGIN (sql_statement); END return(string.Format(CultureInfo.InvariantCulture, @"CREATE TRIGGER {0} IF NOT EXISTS {1} {2} ON {3} {4};", Escape(trigger.Name), trigger.TriggerType, trigger.TriggerEvent, TableName(databaseTable), trigger.TriggerBody)); }
private static void FirebirdTriggerTypeCode(DatabaseTrigger trigger) { if (trigger.TriggerType.Length != 1) { return; } //firebird gives a very helpful number switch (trigger.TriggerType) { case "1": trigger.TriggerType = "BEFORE"; trigger.TriggerEvent = "INSERT"; break; case "2": trigger.TriggerType = "AFTER"; trigger.TriggerEvent = "INSERT"; break; case "3": trigger.TriggerType = "BEFORE"; trigger.TriggerEvent = "UPDATE"; break; case "4": trigger.TriggerType = "AFTER"; trigger.TriggerEvent = "UPDATE"; break; case "5": trigger.TriggerType = "BEFORE"; trigger.TriggerEvent = "DELETE"; break; case "6": trigger.TriggerType = "AFTER"; trigger.TriggerEvent = "DELETE"; break; } }
public override string AddTrigger(DatabaseTable databaseTable, DatabaseTrigger trigger) { //oracle: //CREATE (OR REPLACE) TRIGGER (triggerName) //(BEFORE | AFTER | INSTEAD OF) ([INSERT ] [ OR ] [ UPDATE ] [ OR ] [ DELETE ]) //ON (tableName) //(FOR EACH ROW) //(sql_statement); / var beforeOrAfter = trigger.TriggerType; var forEachRow = string.Empty; if (beforeOrAfter.EndsWith(" EACH ROW", StringComparison.OrdinalIgnoreCase)) { //it's not table level forEachRow = "FOR EACH ROW"; beforeOrAfter = beforeOrAfter.Replace(" EACH ROW", ""); //hopefully beforeOrAfter says "BEFORE", "AFTER" or "INSTEAD OF" now } else { beforeOrAfter = beforeOrAfter.Replace(" STATEMENT", ""); } return(string.Format(CultureInfo.InvariantCulture, @"CREATE OR REPLACE TRIGGER {0}{1} {2} {3} ON {4} {5} {6} / ", SchemaPrefix(trigger.SchemaOwner), Escape(trigger.Name), beforeOrAfter, trigger.TriggerEvent, TableName(databaseTable), forEachRow, trigger.TriggerBody)); }
public void FindOracleAutoNumberTrigger() { //arrange var schema = new DatabaseSchema(null, SqlType.Oracle); var table = schema.AddTable("Test"); var id = table.AddColumn <int>("Id").AddPrimaryKey(); id.IsAutoNumber = true; table.AddColumn <string>("Name").AddLength(200); var databaseTrigger = new DatabaseTrigger { Name = "Test_INS_TRG", TriggerEvent = "INSERT", TriggerBody = @"BEGIN SELECT ""Test_SEQ"".NEXTVAL INTO :NEW.""Id"" FROM DUAL; END;", TriggerType = "BEFORE EACH ROW", }; table.Triggers.Add(databaseTrigger); var databaseSequence = new DatabaseSequence { IncrementBy = 1, MinimumValue = 0, Name = "Test_SEQ" }; schema.Sequences.Add(databaseSequence); //act var result = OracleSequenceTrigger.FindTrigger(table); //assert Assert.IsNotNull(result); Assert.IsNotNull(result.DatabaseTrigger); Assert.IsNotNull(result.DatabaseSequence); Assert.AreEqual(databaseSequence, result.DatabaseSequence); Assert.AreEqual(databaseTrigger, result.DatabaseTrigger); }
public string DropTrigger(DatabaseTable databaseTable, DatabaseTrigger trigger) { return(_migration.DropTrigger(trigger)); }
public string AddTrigger(DatabaseTable databaseTable, DatabaseTrigger trigger) { return(_migration.AddTrigger(databaseTable, trigger)); }
private OracleSequenceTrigger(DatabaseTrigger databaseTrigger) { DatabaseTrigger = databaseTrigger; }
/// <summary> /// Converts the "Triggers" DataTable into <see cref="DatabaseTrigger"/> objects /// </summary> private static List <DatabaseTrigger> Triggers(DataTable dt, string tableName) { var list = new List <DatabaseTrigger>(); if (dt.Columns.Count == 0) { return(list); } //sql server string key = "TRIGGER_NAME"; string tableKey = "TABLE_NAME"; string bodyKey = "TRIGGER_BODY"; string eventKey = "TRIGGERING_EVENT"; string triggerTypeKey = "TRIGGER_TYPE"; string ownerKey = "OWNER"; //firebird if (!dt.Columns.Contains(ownerKey)) { ownerKey = null; } if (!dt.Columns.Contains(bodyKey)) { bodyKey = "SOURCE"; } if (!dt.Columns.Contains(eventKey)) { eventKey = "TRIGGER_TYPE"; } if (!dt.Columns.Contains(bodyKey)) { bodyKey = "BODY"; } if (!dt.Columns.Contains(tableKey)) { tableKey = null; } if (!dt.Columns.Contains(bodyKey)) { bodyKey = null; } if (!dt.Columns.Contains(eventKey)) { eventKey = null; } if (!dt.Columns.Contains(triggerTypeKey)) { triggerTypeKey = null; } //this could be more than one table, so filter the view if (!String.IsNullOrEmpty(tableName) && !String.IsNullOrEmpty(tableKey)) { dt.DefaultView.RowFilter = "[" + tableKey + "] = '" + tableName + "'"; } foreach (DataRowView row in dt.DefaultView) { string name = row[key].ToString(); DatabaseTrigger trigger = list.Find(delegate(DatabaseTrigger f) { return(f.Name == name); }); if (trigger == null) { trigger = new DatabaseTrigger(); trigger.Name = name; if (ownerKey != null) { trigger.SchemaOwner = row[ownerKey].ToString(); } list.Add(trigger); } if (!String.IsNullOrEmpty(tableKey)) { trigger.TableName = row[tableKey].ToString(); } if (!String.IsNullOrEmpty(bodyKey)) { trigger.TriggerBody = row[bodyKey].ToString(); } if (!String.IsNullOrEmpty(eventKey)) { trigger.TriggerEvent = row[eventKey].ToString(); } if (triggerTypeKey != null) { trigger.TriggerType = row[triggerTypeKey].ToString(); FirebirdTriggerTypeCode(trigger); } } return(list); }