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