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);
 }
Example #14
0
 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 + ";";
        }