Доступ 2 007 триггеров и эквиваленты процедур?

Хранимые процедуры

Механизм базы данных Access в режиме запроса ANSI-92 поддерживает синтаксис CREATE PROCEDURE (SQL DDL), например,

CREATE PROCEDURE GetCompanies
(
 :company_type VARCHAR(30) = NULL
)
AS
SELECT company_registered_number, organisation_name, company_type
  FROM Companies 
 WHERE company_type = IIF(:company_type IS NULL, company_type, :company_type);

Таким образом, получившийся объект a ПРОЦЕДУРА и сохраняется в файле базы данных вместе с таблицами. Акцент здесь делается на слове «хранится» (а не «процедура»), т.е. оно «близко к данным». Использование этих объектов способствует хорошему разделению внешнего интерфейса (FE) от внутреннего (BE), и я имею в виду логическое, а не физическое; например, код SQL, хранящийся в коде VBA или в свойствах элемента управления Access Forms, не "близок к данным" и смешивает "уровень" серверной части с "слоем" внешнего интерфейса, что затрудняет обслуживание кода SQL. например если вам нужно переименовать столбец в таблице, это легко сделать, если все, что вам нужно сделать, это просмотреть PROCEDURE s и VIEW s.

Еще одно преимущество использования ПРОЦЕДУРА заключается (или скорее была) в том, что в сочетании с безопасностью на уровне пользователя (ULS) она может способствовать «удобству использования». В качестве примера часто спрашивают, как добавить столбец created_date в таблицу и сохранить его значение. Добавление DEFAULT текущей метки времени дает вам только часть пути, например

CREATE TABLE Entities (
   entity_ID CHAR(8) WITH COMPRESSION NOT NULL UNIQUE, 
   CONSTRAINT entity_ID__pattern 
      CHECK (entity_ID NOT ALIKE '%[!0-9]%'), 
   entity_name VARCHAR(20) NOT NULL, 
   CONSTRAINT entity_name__whitespace
      CHECK (
             entity_name NOT ALIKE ' %'
             AND entity_name NOT ALIKE '% '
             AND entity_name NOT ALIKE '%  %'
             AND LEN(entity_name) > 0
            ), 
   created_date DATETIME DEFAULT NOW() NOT NULL
);

. Но это не предотвращает явное значение, которое не является текущей меткой времени. Мы, конечно, могли бы добавить ограничение CHECK или правило проверки, чтобы обеспечить это:

ALTER TABLE Entities ADD
   CONSTRAINT entity_created_date__must_be_current_timestamp
      CHECK (created_date = NOW());

Проблема здесь в том, что ограничение CHECK и правила проверки проверяются на уровне строки, т.е. если вы когда-нибудь попытаетесь изменить другой столбец, ограничение будет укусить. Не хорошо, так:

ALTER TABLE Entities DROP
   CONSTRAINT entity_created_date__must_be_current_timestamp;

Что делать? Один из подходов - удалить привилегии из таблицы, чтобы конечные пользователи (и приложения в этом контексте тоже были пользователями) не могли ВСТАВИТЬ или ОБНОВИТЬ данные таблицы напрямую, а затем создать ] PROCEDURE s, чтобы разрешить изменение данных и вместо этого предоставить соответствующие привилегии для PROCEDURE s, например,

CREATE PROCEDURE AddEntity (
   :entity_ID CHAR(8), 
   :entity_name VARCHAR(20)
)
AS 
INSERT INTO Entities (entity_ID, entity_name, created_date) 
VALUES (:entity_ID, :entity_name, NOW());

EXECUTE EXECUTE AddEntity '00000001', 'Black';

Я использую прошедшее время, потому что, как вы, возможно, знаете, Access команда (или это была команда SharePoint? :)) удалила ULS из механизма ACE new-for-Access2007. Я не уверен, что могу порекомендовать использовать устаревшую функцию.

А теперь плохие новости. Многие (большинство?) Людей возразят, что такая ПРОЦЕДУРА не является процедурой, и они « Мы получили хороший аргумент, потому что синтаксис SQL ядра СУБД Access не поддерживает управление потоком, объявление переменных и даже возможность выполнения нескольких операторов SQL. Другими словами, ПРОЦЕДУРА не может содержать процедурный код. Рассмотрим таблицу, которая ссылается на Entities:

CREATE TABLE FlyingEntities (
   entity_ID CHAR(8) WITH COMPRESSION NOT NULL UNIQUE 
      REFERENCES Entities (entity_ID) 
      ON DELETE CASCADE 
      ON UPDATE CASCADE
);

Было бы неплохо иметь PROCEDURE , которая может создавать строку в Entities и при необходимости создавать строку в FlyingEntities на основе значения параметра, но это просто невозможно. в одном операторе SQL. Следовательно, ядро ​​базы данных Access ПРОЦЕДУРА имеет ограниченную ценность, особенно теперь, когда ULS исчез.

Триггеры

Нельзя обойти тот факт, что в ядре базы данных Access нет и никогда не было были, триггеры. Однако вопрос в том, они вам нужны?

Хотя мне нравится простота движка базы данных Access, правда в том, что много лет назад я перевел всю «серьезную» работу на более «промышленную» и больше продуктов, совместимых со стандартом SQL, в первую очередь SQL Server. Однако в SQL Server я использую триггеры только для двух вещей, причем обе из них могут быть выполнены без триггеров (в определенной степени) в ядре базы данных Access.

Первое из этих применений - справиться с тем фактом, что SQL Server Ограничения CHECK не поддерживают подзапросы; Другими словами, они могут быть уровнями столбцов и строк, но не уровнями таблиц. Механизм доступа к базе данных Ограничения CHECK , введенные в Jet 4.0 и все еще присутствующие в ACE (2007), всегда являются уровнями таблиц ... ну, теоретически. Существует проблема (предполагаемая ошибка), когда они проверяются на уровне строки, тогда как их следует логически проверять на уровне операторов SQL. Они не поддерживают синтаксис SQL-92 DEFERRABLE , поэтому обходного пути для этой проблемы не существует (кстати, SQL Server страдает от той же проблемы при использовании FUNCTION для обхода подзапросов no ограничение). Не все ограничения CHECK столкнутся с этой проблемой, но ее существование заставляет меня немного насторожиться.

Второе и последнее использование триггеров в SQL Server для меня связано с другим ограничением: ужасным «FOREIGN KEY. ..может вызвать циклы или несколько каскадных путей "при попытке создать две REFERENCE с одним и тем же ключом, например, это разрешено в ядре базы данных Access:

CREATE TABLE Marriages (
   entity_ID_1 CHAR(8) WITH COMPRESSION NOT NULL UNIQUE
      REFERENCES Entities (entity_ID) 
      ON DELETE CASCADE 
      ON UPDATE CASCADE, 
   entity_ID_2 CHAR(8) WITH COMPRESSION NOT NULL UNIQUE
      REFERENCES Entities (entity_ID) 
      ON DELETE CASCADE 
      ON UPDATE CASCADE,
   CONSTRAINT cannot_marry_yourself 
      CHECK (entity_ID_1 <> entity_ID_2)
);

Но портируйте это на SQL Server (удалите С СЖАТИЕМ и т.д.), и это запрещено. В этом случае cannot_marry_yourself предотвратит циклы, но SQL Server выполняет простой подсчет и решает, что 1 + 1 = слишком много. Я полагаю, грубый, но эффективный. Использование триггеров - единственный удовлетворительный способ обхода проблемы; CASCADE ссылочные действия - особая проблема с триггерами.

С другой стороны, ядро ​​базы данных Access в некотором смысле даже тупее, чем SQL Server в этом отношении, потому что он не пытается выполнить циклы обнаружения на все. Если вы действительно создаете цикл, вы не получите предупреждения, и результатом будет гонка за перезапись данных последней и сложная ситуация для отладки.

Помимо этих случаев использования, я избегаю триггеров, потому что они являются головной болью обслуживания (если вы может получить их прямо в первую очередь). Я' Я потерял счет тому, сколько раз коллеги просили меня о помощи, когда мы оба были сбиты с толку относительно того, в чем проблема, только они смущенно сказали мне позже, что был триггер, который они забыли, что создали.

Итак, да, в ядре базы данных Access отсутствуют триггеры, но вы можете обнаружить, что без них вам будет лучше.

О, и не давайте мне начинать с документации по ядру базы данных Access. Он фрагментирован, и многие из этих фрагментов со временем исчезли, а многие из них вообще не существовали, например, я упомянул выше ограничения CHECK , но подробностей так и не опубликовано, это всего лишь пара некорректных примеров ( все, что я знаю об ограничениях CHECK , мне пришлось выучить методом проб и ошибок - что существует, на что я еще не наткнулся ?! ) А существующие фрагменты содержат существенные ошибки и ошибки упущения ... даже с ошибочной детализацией функциональности, которой никогда не было! например, Оператор CREATE TABLE из справки Access2007 упоминает временные таблицы с ограничениями NOT NULL и ограничения с несколькими столбцами NOT NULL , которые не существуют, но не упоминается DEFAULT или тот факт, что некоторые CONSTRAINT не реализованы с использованием индексов. Но наиболее серьезным упущением IMO является ссылка на выражения ядра СУБД Access, например, IIF () ведет себя иначе, чем IIf () в VBA, но это, похоже, в настоящее время недокументировано. В справке SQL для Jet 3 был такой список, ни одной версии с тех пор не было, а справка Jet 3 исчезла из MSDN год или два назад.