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 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);
}
private static string MakeTriggerName(ForeignKeySchema fks, string prefix)
{
return(prefix + "_" + fks.TableName + "_" + fks.ColumnName + "_" + fks.ForeignTableName + "_" + fks.ForeignColumnName);
}
