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 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 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 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 + ";";
}
/// <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;
}
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);
}
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 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 string DropTrigger(DatabaseTrigger trigger)
{
return string.Format(CultureInfo.InvariantCulture,
DropTriggerFormat,
SchemaPrefix(trigger.SchemaOwner),
Escape(trigger.Name));
}
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));
}
private OracleSequenceTrigger(DatabaseTrigger databaseTrigger)
{
DatabaseTrigger = databaseTrigger;
}
public string AddTrigger(DatabaseTable databaseTable, DatabaseTrigger trigger)
{
return _migration.AddTrigger(databaseTable, trigger);
}
public string DropTrigger(DatabaseTable databaseTable, DatabaseTrigger trigger)
{
return _migration.DropTrigger(trigger);
}
public void BuildDropTrigger(DatabaseTrigger trigger)
{
try
{
var txt = _migrationGenerator.DropTrigger(trigger);
Clipboard.SetText(txt, TextDataFormat.UnicodeText);
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
}
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 + ";";
}