blob: 2f4a087348206df40530e68972a63cdb2cca53aa (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
|
using System;
using System.Collections.Generic;
using System.Text;
namespace Tango.Synchronization.Conversion
{
public static class TriggerBuilder
{
public static IList<TriggerSchema> GetForeignKeyTriggers(TableSchema dt)
{
IList<TriggerSchema> result = new List<TriggerSchema>();
foreach (ForeignKeySchema fks in dt.ForeignKeys)
{
StringBuilder sb = new StringBuilder();
result.Add(GenerateInsertTrigger(fks));
result.Add(GenerateUpdateTrigger(fks));
result.Add(GenerateDeleteTrigger(fks));
}
return result;
}
private static string MakeTriggerName(ForeignKeySchema fks, string prefix)
{
return prefix + "_"+fks.TableName + "_" + fks.ColumnName + "_" + fks.ForeignTableName + "_" + fks.ForeignColumnName;
}
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;
}
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;
}
}
}
|
