public void CreateDeleteTrigger()
{
var alreadyOpened = _connection.State == ConnectionState.Open;
try
{
using (var command = new NpgsqlCommand())
{
if (!alreadyOpened)
{
_connection.Open();
}
if (_transaction != null)
{
command.Transaction = _transaction;
}
var delTriggerName = _postgreSqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger);
command.CommandText = CreateDeleteProcedureCommand(delTriggerName);
command.Connection = _connection;
command.ExecuteNonQuery();
var createTrigger = new StringBuilder();
createTrigger.AppendLine(
$"CREATE TRIGGER {delTriggerName} AFTER DELETE ON {_tableName.QuotedString} FOR EACH ROW ");
createTrigger.AppendLine($"EXECUTE PROCEDURE {delTriggerName}()");
command.CommandText = createTrigger.ToString();
command.Connection = _connection;
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Debug.WriteLine($"Error during CreateDeleteTrigger : {ex}");
throw;
}
finally
{
if (!alreadyOpened && _connection.State != ConnectionState.Closed)
{
_connection.Close();
}
}
}
/// <summary>
/// Check if we need to create the stored procedure
/// </summary>
public bool NeedToCreateProcedure(DbCommandType commandType)
{
if (_connection.State != ConnectionState.Open)
{
throw new ArgumentException("Here, we need an opened connection please");
}
var commandName = _sqlObjectNames.GetCommandName(commandType);
return(!PostgreSqlManagementUtils.ProcedureExists(_connection, _transaction, commandName));
}