public static TriggerSchema GenerateDeleteTrigger(ForeignKeySchema fks)
{
var 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;
}
public static TriggerSchema GenerateInsertTrigger(ForeignKeySchema fks)
{
var 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;";
}