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)); }