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 void AddTrigger(TriggerSchema trigger)
{
store.AppendValues(trigger.Name, trigger.TriggerType.ToString(),
trigger.TriggerEvent.ToString(), trigger.TriggerFireType == TriggerFireType.ForEachRow,
trigger.Position.ToString(), trigger.IsActive, trigger.Comment,
trigger.Source, trigger);
}
public static TriggerSchema GenerateUpdateTrigger(ForeignKeySchema fks)
{
var 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)
{
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 virtual void FillSchemaObjects()
{
TreeIter iter;
if (store.GetIterFirst(out iter))
{
do
{
TriggerSchema trigger = store.GetValue(iter, colObjIndex) as TriggerSchema;
trigger.Name = store.GetValue(iter, colNameIndex) as string;
trigger.TriggerType = (TriggerType)Enum.Parse(typeof(TriggerType), store.GetValue(iter, colTypeIndex) as string);
trigger.TriggerEvent = (TriggerEvent)Enum.Parse(typeof(TriggerEvent), store.GetValue(iter, colEventIndex) as string);
trigger.TriggerFireType = (TriggerFireType)Enum.Parse(typeof(TriggerFireType), store.GetValue(iter, colFireTypeIndex) as string);
trigger.Position = int.Parse(store.GetValue(iter, colPositionIndex) as string);
trigger.IsActive = (bool)store.GetValue(iter, colActiveIndex);
trigger.Comment = store.GetValue(iter, colCommentIndex) as string;
trigger.Source = store.GetValue(iter, colSourceIndex) as string;
table.Triggers.Add(trigger);
} while (store.IterNext(ref iter));
}
}
protected virtual void AddClicked(object sender, EventArgs e)
{
TriggerSchema trigger = schemaProvider.GetNewTriggerSchema("trigger_" + table.Name);
trigger.TableName = table.Name;
int index = 1;
while (triggers.Contains(trigger.Name))
{
trigger.Name = "trigger_" + table.Name + (index++);
}
triggers.Add(trigger);
AddTrigger(trigger);
EmitContentChanged();
}
protected virtual string GetTriggerCreateStatement(TriggerSchema trigger)
{
StringBuilder sb = new StringBuilder();
sb.Append(Environment.NewLine);
sb.Append("CREATE TRIGGER ");
sb.Append(trigger.Name);
if (trigger.TriggerType == TriggerType.Before)
{
sb.Append(" BEFORE ");
}
else
{
sb.Append(" AFTER ");
}
switch (trigger.TriggerEvent)
{
case TriggerEvent.Delete:
sb.Append("DELETE");
break;
case TriggerEvent.Insert:
sb.Append("INSERT");
break;
case TriggerEvent.Update:
sb.Append("UPDATE");
break;
default:
throw new NotImplementedException();
}
sb.Append(" ON ");
sb.Append(trigger.TableName);
sb.Append(Environment.NewLine);
sb.Append("FOR EACH ROW ");
sb.Append(Environment.NewLine);
sb.Append("BEGIN");
sb.Append(Environment.NewLine);
sb.Append(trigger.Source);
sb.Append(Environment.NewLine);
sb.Append("END");
sb.Append(Environment.NewLine);
return(sb.ToString());
}
public static TriggerSchema GenerateInsertTrigger(ForeignKeySchema fks)
{
TriggerSchema triggerSchema = new TriggerSchema();
triggerSchema.Name = MakeTriggerName(fks, "fki");
triggerSchema.Type = TriggerType.Before;
triggerSchema.Event = TriggerEvent.Insert;
triggerSchema.Table = fks.TableName;
string text = "";
if (fks.IsNullable)
{
text = " NEW." + fks.ColumnName + " IS NOT NULL AND";
}
triggerSchema.Body = "SELECT RAISE(ROLLBACK, 'insert on table " + fks.TableName + " violates foreign key constraint " + triggerSchema.Name + "') WHERE" + text + " (SELECT " + fks.ForeignColumnName + " FROM " + fks.ForeignTableName + " WHERE " + fks.ForeignColumnName + " = NEW." + fks.ColumnName + ") IS NULL; ";
return(triggerSchema);
}
protected virtual void AddClicked(object sender, EventArgs e)
{
TriggerSchema trigger = schemaProvider.CreateTriggerSchema(string.Concat(table.Name,
"_",
"trigger_",
table.Name));
trigger.TableName = table.Name;
int index = 1;
while (triggers.Contains(trigger.Name))
{
trigger.Name = "trigger_" + table.Name + (index++);
}
// triggers.Add (trigger);
AddTrigger(trigger);
EmitContentChanged();
}
protected virtual void RemoveClicked(object sender, System.EventArgs e)
{
TreeIter iter;
if (listTriggers.Selection.GetSelected(out iter))
{
TriggerSchema trigger = store.GetValue(iter, colObjIndex) as TriggerSchema;
if (Services.MessageService.AskQuestion(
GettextCatalog.GetString("Are you sure you want to remove trigger '{0}'?", trigger.Name),
GettextCatalog.GetString("Remove Trigger")
))
{
store.Remove(ref iter);
triggers.Remove(trigger);
EmitContentChanged();
}
}
}
protected virtual void OnSelectionChanged(object sender, EventArgs e)
{
TreeIter iter;
if (listTriggers.Selection.GetSelected(out iter))
{
buttonRemove.Sensitive = true;
sqlEditor.Editable = true;
TriggerSchema trigger = store.GetValue(iter, colObjIndex) as TriggerSchema;
sqlEditor.Text = trigger.Source;
}
else
{
buttonRemove.Sensitive = false;
sqlEditor.Editable = false;
sqlEditor.Text = String.Empty;
}
}
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>
/// returns <see cref="TriggerSchema"/> corresponding to a query of <paramref name="queryType"/>
/// performed on table corresponding to <paramref name="auditedTableSchema"/>.
/// </summary>
/// <param name="auditedTableSchema"></param>
/// <param name="auditTableSchema"></param>
/// <param name="queryType"></param>
/// <returns>
/// <see cref="TriggerSchema"/> corresponding to a query of <paramref name="queryType"/>
/// performed on table corresponding to <paramref name="auditedTableSchema"/>
/// </returns>
internal static TriggerSchema GetAuditTriggerSchema(
TableSchema auditedTableSchema,
TableSchema auditTableSchema,
Query.eQueryType queryType)
{
TriggerSchema auditTriggerSchema;
InsertQuery onTriggerAuditInsertQuery =
getOnTriggerAuditInsertQuery(
auditedTableSchema,
auditTableSchema,
queryType);
auditTriggerSchema = new TriggerSchema(
getTriggerName(auditedTableSchema, queryType),
getTriggerTime(queryType),
queryType,
auditedTableSchema.Name,
onTriggerAuditInsertQuery);
return(auditTriggerSchema);
}
//http://msdn2.microsoft.com/en-us/library/aa258846(SQL.80).aspx
public override void DropTrigger(TriggerSchema trigger)
{
ExecuteNonQuery(string.Concat("DROP TRIGGER ", trigger.Name));
}
protected virtual string GetTriggerCreateStatement (TriggerSchema trigger)
{
StringBuilder sb = new StringBuilder ();
sb.Append (Environment.NewLine);
sb.Append ("CREATE TRIGGER ");
sb.Append (trigger.Name);
if (trigger.TriggerType == TriggerType.Before)
sb.Append (" BEFORE ");
else
sb.Append (" AFTER ");
switch (trigger.TriggerEvent) {
case TriggerEvent.Delete:
sb.Append ("DELETE");
break;
case TriggerEvent.Insert:
sb.Append ("INSERT");
break;
case TriggerEvent.Update:
sb.Append ("UPDATE");
break;
default:
throw new NotImplementedException ();
}
sb.Append (" ON ");
sb.Append (trigger.TableName);
sb.Append (Environment.NewLine);
sb.Append ("FOR EACH ROW ");
sb.Append (Environment.NewLine);
sb.Append ("BEGIN");
sb.Append (Environment.NewLine);
sb.Append (trigger.Source);
sb.Append (Environment.NewLine);
sb.Append ("END");
sb.Append (Environment.NewLine);
return sb.ToString ();
}
//http://dev.mysql.com/doc/refman/5.1/en/drop-trigger.html
public override void DropTrigger (TriggerSchema trigger)
{
ExecuteNonQuery (string.Concat("DROP TRIGGER IF EXISTS ", trigger.Name, ";"));
}
public override TriggerSchemaCollection GetTableTriggers (TableSchema table)
{
if (table == null)
throw new ArgumentNullException ("table");
TriggerSchemaCollection triggers = new TriggerSchemaCollection ();
IPooledDbConnection conn = connectionPool.Request ();
IDbCommand command = conn.CreateCommand (string.Format (@"SELECT * FROM sqlite_master
WHERE type = 'trigger' and tbl_name = '{0}'",
table.Name));
try {
// Parse Trigger Sql - Needed for alter a table
System.Text.RegularExpressions.Regex parseRegEx = new System.Text.RegularExpressions.Regex
(string.Concat (
@"((CREATE\s*(Temp|Temporary)?\s*TRIGGER){1}\s?(\w+)\s?(IF NOT",
@" EXISTS)?\s?(BEFORE|AFTER|INSTEAD OF){1}\s?(\w+)\s*ON(\s+\w*",
@")\s*(FOR EACH ROW){1}\s*(BEGIN){1})\s+(\w|\W)*(END)"));
using (IDataReader r = command.ExecuteReader ()) {
while (r.Read ()) {
string sql = r.GetString (r.GetOrdinal ("sql"));
System.Text.RegularExpressions.MatchCollection matchs = parseRegEx.Matches (sql);
TriggerSchema trigger = new TriggerSchema (this);
trigger.Name = r.GetString (r.GetOrdinal ("name"));
trigger.TableName = r.GetString (r.GetOrdinal ("tbl_name"));
trigger.Definition = sql;
if (matchs.Count > 0) {
trigger.TriggerFireType = TriggerFireType.ForEachRow;
switch (matchs[0].Groups[7].Value.ToLower ()) {
case "insert":
trigger.TriggerEvent = TriggerEvent.Insert;
break;
case "update":
trigger.TriggerEvent = TriggerEvent.Update;
break;
case "delete":
trigger.TriggerEvent = TriggerEvent.Delete;
break;
default:
throw new NotImplementedException ();
}
switch (matchs[0].Groups[7].Value.ToLower ()) {
case "before":
trigger.TriggerType = TriggerType.Before;
break;
case "after":
trigger.TriggerType = TriggerType.After;
break;
default:
throw new NotImplementedException ();
}
StringBuilder sbSource = new StringBuilder ();
foreach (System.Text.RegularExpressions.Capture c in matchs[0].Groups[11].Captures)
sbSource.Append (c.Value);
trigger.Source = sbSource.ToString ();
}
triggers.Add (trigger);
}
}
} catch (SqliteException e) {
QueryService.RaiseException (e);
}
return triggers;
}
//http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html
public override void CreateTrigger (TriggerSchema trigger)
{
string sql = GetTriggerCreateStatement (trigger);
ExecuteNonQuery (sql);
}
//http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx
public override void CreateTrigger(TriggerSchema trigger)
{
string sql = GetTriggerCreateStatement(trigger);
ExecuteNonQuery(sql);
}
public override TriggerSchemaCollection GetTableTriggers(TableSchema table)
{
if (table == null)
{
throw new ArgumentNullException("table");
}
TriggerSchemaCollection triggers = new TriggerSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(string.Format(@"SELECT * FROM sqlite_master
WHERE type = 'trigger' and tbl_name = '{0}'" ,
table.Name));
try {
// Parse Trigger Sql - Needed for alter a table
System.Text.RegularExpressions.Regex parseRegEx = new System.Text.RegularExpressions.Regex
(string.Concat(
@"((CREATE\s*(Temp|Temporary)?\s*TRIGGER){1}\s?(\w+)\s?(IF NOT",
@" EXISTS)?\s?(BEFORE|AFTER|INSTEAD OF){1}\s?(\w+)\s*ON(\s+\w*",
@")\s*(FOR EACH ROW){1}\s*(BEGIN){1})\s+(\w|\W)*(END)"));
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
string sql = r.GetString(r.GetOrdinal("sql"));
System.Text.RegularExpressions.MatchCollection matchs = parseRegEx.Matches(sql);
TriggerSchema trigger = new TriggerSchema(this);
trigger.Name = r.GetString(r.GetOrdinal("name"));
trigger.TableName = r.GetString(r.GetOrdinal("tbl_name"));
trigger.Definition = sql;
if (matchs.Count > 0)
{
trigger.TriggerFireType = TriggerFireType.ForEachRow;
switch (matchs[0].Groups[7].Value.ToLower())
{
case "insert":
trigger.TriggerEvent = TriggerEvent.Insert;
break;
case "update":
trigger.TriggerEvent = TriggerEvent.Update;
break;
case "delete":
trigger.TriggerEvent = TriggerEvent.Delete;
break;
default:
throw new NotImplementedException();
}
switch (matchs[0].Groups[7].Value.ToLower())
{
case "before":
trigger.TriggerType = TriggerType.Before;
break;
case "after":
trigger.TriggerType = TriggerType.After;
break;
default:
throw new NotImplementedException();
}
StringBuilder sbSource = new StringBuilder();
foreach (System.Text.RegularExpressions.Capture c in matchs[0].Groups[11].Captures)
{
sbSource.Append(c.Value);
}
trigger.Source = sbSource.ToString();
}
triggers.Add(trigger);
}
}
} catch (SqliteException e) {
QueryService.RaiseException(e);
}
return(triggers);
}
//http://www.sqlite.org/lang_droptrigger.html
public override void DropTrigger (TriggerSchema trigger)
{
ExecuteNonQuery ("DROP TRIGGER IF EXISTS " + trigger.Name);
}
public override TriggerSchemaCollection GetTableTriggers (TableSchema table)
{
if (table == null)
throw new ArgumentNullException ("table");
TriggerSchemaCollection triggers = new TriggerSchemaCollection ();
using (IPooledDbConnection conn = connectionPool.Request ()) {
using (IDbCommand command = conn.CreateCommand (string.Format (
@"SELECT * FROM
information_schema.triggers
WHERE event_object_table = '{0}' order by trigger_name",
table.Name))) {
try {
using (IDataReader r = command.ExecuteReader ()) {
while (r.Read ()) {
TriggerSchema trigger = new TriggerSchema (this);
trigger.Name = r.GetString (r.GetOrdinal ("trigger_name"));
trigger.Source = r.GetString (r.GetOrdinal ("action_statement"));
trigger.TriggerType = (TriggerType)Enum.Parse (typeof(TriggerType),
r.GetString (r.GetOrdinal ("condition_timing")));
trigger.TriggerEvent = (TriggerEvent)Enum.Parse (typeof(TriggerEvent),
r.GetString (r.GetOrdinal ("event_manipulation")));
trigger.TriggerFireType = TriggerFireType.ForEachRow;
triggers.Add (trigger);
}
}
} catch (NpgsqlException e) {
QueryService.RaiseException (e);
} finally {
conn.Release ();
}
}
}
return triggers;
//http://msdn2.microsoft.com/en-US/library/aa238878(SQL.80).aspx
public override void RenameTrigger (TriggerSchema trigger, string name)
{
Rename (trigger.Name, name, "OBJECT");
trigger.Name = name;
/// <summary>
/// Gets a create script for the triggerSchema in SQLite syntax
/// </summary>
/// <param name="ts">Trigger to script</param>
/// <returns>Executable script</returns>
protected static string WriteTriggerSchema(TriggerSchema ts)
{
return @"CREATE TRIGGER [" + ts.Name + "] " +
ts.Type + " " + ts.Event +
" ON [" + ts.Table + "] " +
"BEGIN " + ts.Body + " END;";
}
public override TriggerSchemaCollection GetTableTriggers (TableSchema table)
{
if (table == null)
throw new ArgumentNullException ("table");
TriggerSchemaCollection triggers = new TriggerSchemaCollection ();
using (IPooledDbConnection conn = connectionPool.Request ()) {
string sql = string.Format(@"SELECT
Tables.Name TableName,
Triggers.name TriggerName,
Triggers.crdate TriggerCreatedDate,
Comments.Text TriggerText
FROM sysobjects Triggers
INNER JOIN sysobjects Tables On
Triggers.parent_obj = Tables.id
INNER JOIN syscomments Comments On
Triggers.id = Comments.id
WHERE
Triggers.xtype = 'TR'
AND Tables.xtype = 'U'
AND Tables.Name = '{0}'
ORDER BY
Tables.Name,
Triggers.name", table.Name);
using (IDbCommand command = conn.CreateCommand (sql)) {
using (IDataReader r = command.ExecuteReader ()) {
while (r.Read ()) {
System.Text.RegularExpressions.Regex parseRegEx = new System.Text.RegularExpressions.Regex
(string.Concat (
@"((CREATE\s*(Temp|Temporary)?\s*TRIGGER){1}\s?(\w+)\s?(IF NOT",
@" EXISTS)?\s?(BEFORE|AFTER|INSTEAD OF){1}\s?(\w+)\s*ON(\s+\w*",
@")\s*(FOR EACH ROW){1}\s*(BEGIN){1})\s+(\w|\W)*(END)"));
TriggerSchema trigger = new TriggerSchema (this);
trigger.TableName = table.Name;
trigger.Name = r.GetString (r.GetOrdinal ("TriggerName"));
sql = r.GetString (r.GetOrdinal ("TriggerText"));
System.Text.RegularExpressions.MatchCollection matchs = parseRegEx.Matches (sql);
if (matchs.Count > 0) {
trigger.TriggerFireType = TriggerFireType.ForEachRow;
switch (matchs[0].Groups[7].Value.ToLower ()) {
case "insert":
trigger.TriggerEvent = TriggerEvent.Insert;
break;
case "update":
trigger.TriggerEvent = TriggerEvent.Update;
break;
case "delete":
trigger.TriggerEvent = TriggerEvent.Delete;
break;
default:
throw new NotImplementedException ();
}
switch (matchs[0].Groups[7].Value.ToLower ()) {
case "before":
trigger.TriggerType = TriggerType.Before;
break;
case "after":
trigger.TriggerType = TriggerType.After;
break;
default:
throw new NotImplementedException ();
}
StringBuilder sbSource = new StringBuilder ();
foreach (System.Text.RegularExpressions.Capture c in matchs[0].Groups[11].Captures)
sbSource.Append (c.Value);
trigger.Source = sbSource.ToString ();
}
triggers.Add (trigger);
}
}
}
conn.Release ();
}
return triggers;
public CreateTriggerQuery(TriggerSchema triggerSchema)
{
this.triggerSchema = triggerSchema;
}
public static string WriteTriggerSchema(TriggerSchema ts)
{
return(string.Concat("CREATE TRIGGER [", ts.Name, "] ", ts.Type, " ", ts.Event, " ON [", ts.Table, "] BEGIN ", ts.Body, " END;"));
}
//http://msdn2.microsoft.com/en-US/library/aa238878(SQL.80).aspx
public override void RenameTrigger(TriggerSchema trigger, string name)
{
Rename(trigger.Name, name, "OBJECT");
trigger.Name = name;
}
protected virtual string GetTriggerCreateStatement (TriggerSchema trigger)
{
StringBuilder sb = new StringBuilder ();
sb.Append ("CREATE TRIGGER ");
sb.Append (trigger.Name);
switch (trigger.TriggerType) {
case TriggerType.Before:
sb.Append (" BEFORE");
break;
case TriggerType.After:
sb.Append (" AFTER");
break;
default:
throw new NotImplementedException ();
}
switch (trigger.TriggerEvent) {
case TriggerEvent.Insert:
sb.Append (" INSERT ");
break;
case TriggerEvent.Update:
sb.Append (" UPDATE ");
break;
case TriggerEvent.Delete:
sb.Append (" DELETE ");
break;
default:
throw new NotImplementedException ();
}
sb.Append ("ON ");
sb.Append (trigger.TableName);
sb.Append (' ');
sb.Append (Environment.NewLine);
switch (trigger.TriggerFireType) {
case TriggerFireType.ForEachRow:
sb.Append (" FOR EACH ROW ");
break;
case TriggerFireType.ForEachStatement:
sb.Append (" FOR EACH STATEMENT ");
break;
default:
throw new NotImplementedException ();
}
sb.Append (Environment.NewLine);
sb.Append ("EXECUTE PROCEDURE ");
sb.Append (trigger.Source);
sb.Append (";");
return sb.ToString ();
public override TriggerSchemaCollection GetTableTriggers(TableSchema table)
{
if (table == null)
{
throw new ArgumentNullException("table");
}
TriggerSchemaCollection triggers = new TriggerSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
string sql = string.Format(@"SELECT
Tables.Name TableName,
Triggers.name TriggerName,
Triggers.crdate TriggerCreatedDate,
Comments.Text TriggerText
FROM sysobjects Triggers
INNER JOIN sysobjects Tables On
Triggers.parent_obj = Tables.id
INNER JOIN syscomments Comments On
Triggers.id = Comments.id
WHERE
Triggers.xtype = 'TR'
AND Tables.xtype = 'U'
AND Tables.Name = '{0}'
ORDER BY
Tables.Name,
Triggers.name" , table.Name);
using (IDbCommand command = conn.CreateCommand(sql)) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
System.Text.RegularExpressions.Regex parseRegEx = new System.Text.RegularExpressions.Regex
(string.Concat(
@"((CREATE\s*(Temp|Temporary)?\s*TRIGGER){1}\s?(\w+)\s?(IF NOT",
@" EXISTS)?\s?(BEFORE|AFTER|INSTEAD OF){1}\s?(\w+)\s*ON(\s+\w*",
@")\s*(FOR EACH ROW){1}\s*(BEGIN){1})\s+(\w|\W)*(END)"));
TriggerSchema trigger = new TriggerSchema(this);
trigger.TableName = table.Name;
trigger.Name = r.GetString(r.GetOrdinal("TriggerName"));
sql = r.GetString(r.GetOrdinal("TriggerText"));
System.Text.RegularExpressions.MatchCollection matchs = parseRegEx.Matches(sql);
if (matchs.Count > 0)
{
trigger.TriggerFireType = TriggerFireType.ForEachRow;
switch (matchs[0].Groups[7].Value.ToLower())
{
case "insert":
trigger.TriggerEvent = TriggerEvent.Insert;
break;
case "update":
trigger.TriggerEvent = TriggerEvent.Update;
break;
case "delete":
trigger.TriggerEvent = TriggerEvent.Delete;
break;
default:
throw new NotImplementedException();
}
switch (matchs[0].Groups[7].Value.ToLower())
{
case "before":
trigger.TriggerType = TriggerType.Before;
break;
case "after":
trigger.TriggerType = TriggerType.After;
break;
default:
throw new NotImplementedException();
}
StringBuilder sbSource = new StringBuilder();
foreach (System.Text.RegularExpressions.Capture c in matchs[0].Groups[11].Captures)
{
sbSource.Append(c.Value);
}
trigger.Source = sbSource.ToString();
}
triggers.Add(trigger);
}
}
}
conn.Release();
}
return(triggers);
}
//http://www.postgresql.org/docs/8.2/interactive/sql-altertrigger.html
public override void RenameTrigger (TriggerSchema trigger, string name)
{
ExecuteNonQuery (string.Concat("ALTER TRIGGER ", trigger.Name, " ON ", trigger.TableName, " RENAME TO ",
name, ";"));
trigger.Name = name;
/// <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;");
}
//http://www.sqlite.org/lang_droptrigger.html
public override void DropTrigger(TriggerSchema trigger)
{
ExecuteNonQuery("DROP TRIGGER IF EXISTS " + trigger.Name);
}
