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>
/// Add foreign key schema object from the specified components (Read from SQL Server).
/// </summary>
/// <param name="ts">The table schema to whom foreign key schema should be added to</param>
private void CreateForeignKeySchema(TableSchema ts)
{
ts.ForeignKeys = new List<ForeignKeySchema>();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(
@"SELECT " +
@" ColumnName = CU.COLUMN_NAME, " +
@" ForeignTableName = PK.TABLE_NAME, " +
@" ForeignColumnName = PT.COLUMN_NAME, " +
@" DeleteRule = C.DELETE_RULE, " +
@" IsNullable = COL.IS_NULLABLE " +
@"FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C " +
@"INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME " +
@"INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME " +
@"INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME " +
@"INNER JOIN " +
@" ( " +
@" SELECT i1.TABLE_NAME, i2.COLUMN_NAME " +
@" FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 " +
@" INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME " +
@" WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' " +
@" ) " +
@"PT ON PT.TABLE_NAME = PK.TABLE_NAME " +
@"INNER JOIN INFORMATION_SCHEMA.COLUMNS AS COL ON CU.COLUMN_NAME = COL.COLUMN_NAME AND FK.TABLE_NAME = COL.TABLE_NAME " +
@"WHERE FK.Table_NAME='" + ts.TableName + "'", conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ForeignKeySchema fkc = new ForeignKeySchema();
fkc.ColumnName = (string)reader["ColumnName"];
fkc.ForeignTableName = (string)reader["ForeignTableName"];
fkc.ForeignColumnName = (string)reader["ForeignColumnName"];
fkc.CascadeOnDelete = (string)reader["DeleteRule"] == "CASCADE";
fkc.IsNullable = (string)reader["IsNullable"] == "YES";
fkc.TableName = ts.TableName;
ts.ForeignKeys.Add(fkc);
}
}
}
}
private static string MakeTriggerName(ForeignKeySchema fks, string prefix)
{
return prefix + "_" + fks.TableName + "_" + fks.ColumnName + "_" + fks.ForeignTableName + "_" + fks.ForeignColumnName;
}
