public static TriggerSchema GenerateDeleteTrigger(ForeignKeySchema fks)
        {
            TriggerSchema trigger = new TriggerSchema();
            trigger.Name = MakeTriggerName(fks, "fkd");
            trigger.Type = TriggerType.Before;
            trigger.Event = TriggerEvent.Delete;
            trigger.Table = fks.ForeignTableName;

            string triggerName = trigger.Name;

            if (!fks.CascadeOnDelete)
            {
                trigger.Body = "SELECT RAISE(ROLLBACK, 'delete on table " + fks.ForeignTableName +
                                      " violates foreign key constraint " + triggerName + "')" +
                                      " WHERE (SELECT " + fks.ColumnName +
                                      " FROM " + fks.TableName + " WHERE " + fks.ColumnName + " = OLD." +
                                      fks.ForeignColumnName +
                                      ") IS NOT NULL; ";
            }
            else
            {
                trigger.Body = "DELETE FROM [" + fks.TableName + "] WHERE " + fks.ColumnName + " = OLD." +
                                      fks.ForeignColumnName + "; ";

            }
            return trigger;
        }
Exemple #2
0
        public static TriggerSchema GenerateDeleteTrigger(ForeignKeySchema fks)
        {
            TriggerSchema trigger = new TriggerSchema();

            trigger.Name  = MakeTriggerName(fks, "fkd");
            trigger.Type  = TriggerType.Before;
            trigger.Event = TriggerEvent.Delete;
            trigger.Table = fks.ForeignTableName;

            string triggerName = trigger.Name;

            if (!fks.CascadeOnDelete)
            {
                trigger.Body = "SELECT RAISE(ROLLBACK, 'delete on table " + fks.ForeignTableName +
                               " violates foreign key constraint " + triggerName + "')" +
                               " WHERE (SELECT " + fks.ColumnName +
                               " FROM " + fks.TableName + " WHERE " + fks.ColumnName + " = OLD." +
                               fks.ForeignColumnName +
                               ") IS NOT NULL; ";
            }
            else
            {
                trigger.Body = "DELETE FROM [" + fks.TableName + "] WHERE " + fks.ColumnName + " = OLD." +
                               fks.ForeignColumnName + "; ";
            }
            return(trigger);
        }
Exemple #3
0
        public static TriggerSchema GenerateUpdateTrigger(ForeignKeySchema fks)
        {
            TriggerSchema trigger = new TriggerSchema();

            trigger.Name  = MakeTriggerName(fks, "fku");
            trigger.Type  = TriggerType.Before;
            trigger.Event = TriggerEvent.Update;
            trigger.Table = fks.TableName;

            string triggerName = trigger.Name;
            string nullString  = "";

            if (fks.IsNullable)
            {
                nullString = " NEW." + fks.ColumnName + " IS NOT NULL AND";
            }

            trigger.Body = "SELECT RAISE(ROLLBACK, 'update on table " + fks.TableName +
                           " violates foreign key constraint " + triggerName + "')" +
                           " WHERE" + nullString + " (SELECT " + fks.ForeignColumnName +
                           " FROM " + fks.ForeignTableName + " WHERE " + fks.ForeignColumnName + " = NEW." +
                           fks.ColumnName +
                           ") IS NULL; ";

            return(trigger);
        }
        public static TriggerSchema GenerateInsertTrigger(ForeignKeySchema fks)
        {
            TriggerSchema trigger = new TriggerSchema();
            trigger.Name = MakeTriggerName(fks, "fki");
            trigger.Type = TriggerType.Before;
            trigger.Event = TriggerEvent.Insert;
            trigger.Table = fks.TableName;

            string nullString = "";
            if (fks.IsNullable)
            {
                nullString = " NEW." + fks.ColumnName + " IS NOT NULL AND";
            }

            trigger.Body = "SELECT RAISE(ROLLBACK, 'insert on table " + fks.TableName +
                          " violates foreign key constraint " + trigger.Name + "')" +
                          " WHERE" + nullString + " (SELECT " + fks.ForeignColumnName +
                          " FROM " + fks.ForeignTableName + " WHERE " + fks.ForeignColumnName + " = NEW." +
                          fks.ColumnName +
                          ") IS NULL; " ;
            return trigger;
        }
 /// <summary>
 /// Gets a create script for the triggerSchema in sqlite syntax
 /// </summary>
 /// <param name="ts">Trigger to script</param>
 /// <returns>Executable script</returns>
 public static string WriteTriggerSchema(TriggerSchema ts)
 {
     return @"CREATE TRIGGER [" + ts.Name + "] " +
            ts.Type + " " + ts.Event +
            " ON [" + ts.Table + "] " +
            "BEGIN " + ts.Body + " END;";
 }