Предыдущая часть

Особенности работы с Microsoft SQL Server в Delphi 5
(Часть 2)

У Анатолий Тенцер
Статья была опубликована в "КомпьютерПресс", №6-2001

• INSERT
• UPDATE и DELETE
• CREATE TABLE
• Написание триггеров
• Пакеты команд
• Обработка ошибок

INSERT

В дополнение к стандартным возможностям Microsoft SQL Server позволяет вставить в таблицу набор данных, полученный в результате выполнения хранимой процедуры, при помощи синтаксиса:

INSERT author_sales EXECUTE get_author_sales

UPDATE и DELETE

Сервер поддерживает расширенный синтаксис

UPDATE MyTable
          SET Name = ‘Иванов’
     FROM MyTable T INNER JOIN AnotherTable A ON T.Id = A.MyTableId
          AND A.SomeField = 20

CREATE TABLE

В версии 7.0 поддерживаются следующие типы данных:

В версии SQL 2000 дополнительно предусмотрены следующие типы данных:

В версии 7.0 поддерживается создание вычисляемых полей

CREATE TABLE MyTable (
  Direction BIT NOT NULL,
  Amount MONEY,
  CASE Direction
  WHEN 1 THEN Amount
  ELSE -Amount
  END AS SignedAmount
)

Выражение не должно содержать подзапросов. В версии Microsoft SQL Server 2000 по вычисляемому полю может быть построен индекс.

Написание триггеров

Триггеры в Microsoft SQL Server срабатывают после обновления и лишь один раз на оператор (а не на каждую обновленную запись). Количество триггеров на таблицу не ограничено. В триггере доступны обновленная таблица и две виртуальные таблицы Inserted и Deleted.

В них находятся:

Основываясь на содержании этих таблиц, триггер может осуществить дополнительную модификацию данных либо отменить транзакцию, вызвавшую этот оператор. Например:

CREATE TRIGGER T1 ON MyTable FOR INSERT, UPDATE
   AS BEGIN
      -- Заносим в поля:
      -- LastUserName – имя пользователя, последним обновившего запись
      -- LastDateTime – дату и время последнего обновления
      UPDATE MyTable
         SET LastUserName = SUSER_NAME(),
            LastDateTime = GETDATE()
         FROM Inserted I INNER JOIN MyTable T ON I.Id = T.Id
END

CREATE TRIGGER T2 ON MyTable FOR DELETE
AS BEGIN
   -- Этот триггер откатывает и снимает всю транзакцию
   -- вызвавшую ошибку
   IF EXISTS (SELECT * FROM Deleted
               WHERE Position = ‘Boss’) BEGIN
      RAISERROR(‘Нельзя удалять начальника’, 16, 1)
      ROLLBACK
   END
END

CREATE TRIGGER T3 ON MyTable FOR DELETE
AS BEGIN
   -- А этот просто не дает удалить запись
   -- позволяя продолжить транзакцию
   IF EXISTS (SELECT * FROM Deleted
               WHERE Position = ‘Programmer’) BEGIN
   INSERT INTO MyTable
      SELECT * FROM Deleted
         WHERE Position = ‘Programmer’
      RAISERROR(‘Программиста удалить тоже не получится’, 16, 1)
   END
END

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

CREATE TABLE Main (
   Id INTEGER PRIMARY KEY
   )

   CREATE TABLE Child (
   Id INTEGER PRIMARY KEY,
   MainId INTEGER NOT NULL REFERENCES Main(Id)
   )

то при удалении записи из Main, на которую имеются ссылки в Child, триггер на Main не сработает. Чтобы обойти эту проблему, рекомендуется создать хранимую процедуру

CREATE PROCEDURE DeleteFromMain
   @Id INTEGER
   AS BEGIN
      DECLARE @Result INTEGER
      BEGIN TRANSACTION
         SAVE TRANSACTION DeleteFromMain
            DELETE Child WHERE MainId = @Id
            DELETE Main WHERE Id = @Id
         SET @Result = @@ERROR
         IF @Result <> 0
               ROLLBACK TRANSACTION DeleteFromMain
         COMMIT
END

Другой способ — реализация ограничений ссылочной целостности только при помощи триггеров.

Кроме того, в версии Microsoft SQL Server 2000 возможно создание триггеров INSTEAD OF, которые выполняются вместо вызвавшей их операции. При этом ответственность за запись данных в таблице полностью лежит на программисте. Такие триггеры могут быть созданы на представлениях (VIEW), что позволяет сделать обновляемым любое представление, независимо от его сложности.

Пакеты команд

Операторы могут быть отправлены на сервер не поодиночке, а пакетами. Пакетом (batch) называется группа команд, отправленная клиентским приложением на сервер одновременно. Весь пакет компилируется в единый план исполнения. Такая техника позволяет уменьшить сетевой трафик и увеличить эффективность приложения. Типичный пакет может выглядеть следующим образом:

BEGIN TRANSACTION
      INSERT One (SomeField) VALUES (:1)
      INSERT Two (AnotherField) VALUES (:2)
      IF @@ERROR = 0
            COMMIT
      ELSE
            ROLLBACK

Внутри пакета возможно объявление переменных. Область их видимости ограничена пакетом, в котором они объявлены.

Весь пакет не выполняется в случае синтаксической ошибки в любом из операторов пакета. Однако в случае ошибки выполнения любого оператора остальные операторы продолжают исполняться до окончания пакета.

Разделителем пакетов команд служит оператор GO.

Обработка ошибок

Для того чтобы проинформировать клиентское приложение об ошибке, Microsoft SQL Server использует функцию RAISERROR. При этом необходимо помнить, что:

• выполнение процедуры этой функцией не прерывается, транзакции не откатываются. Если в этом есть необходимость, используйте ROLLBACK или RETURN;
• ошибки с параметром severity ниже 10 являются информационными и не вызывают исключения компонентов работы с данными.

При возникновении ошибки в каком-либо из операторов внутри пакета выполнение пакета продолжается, а функция @@ERROR возвращает код ошибки, который можно обработать.

INSERT MyTable (Name) VALUES (‘Петров’)
      IF @@ERROR != 0
         PRINT ‘Ошибка вставки’.

После успешного оператора @@ERROR возвращает 0, поэтому если значение ошибки может понадобиться впоследствии, то его необходимо сохранить в переменной.

DECLARE @ErrCode INTEGER

   SET @ErrCode = 0

      BEGIN TRANSACTION
      INSERT MyTable (Name) VALUES (‘Иванов’)
      IF @@ERROR != 0
               @ErrCode = @@ERROR

      INSERT MyTable (Name) VALUES (‘Петров’)
      IF @@ERROR != 0
               @ErrCode = @@ERROR

      IF @ErrCode = 0
         COMMIT
      ELSE BEGIN
         ROLLBACK
         RAISERROR(‘Не удалось обновить данные’, 16, 1)
END

Если оператор обновления данных не нашел ни одной записи, ошибки не возникает. Проверить эту ситуацию можно при помощи функции @@ROWCOUNT, которая возвращает количество записей, обработанных последним оператором.

UPDATE MyTable
            SET Name = ‘Сидоров’
      WHERE Name = ‘Петров’

      IF @@ROWCOUNT = 0
            PRINT ‘Петров не найден’

Дополнительную информацию Вы можете получить в компании Interface Ltd.

Обсудить на форуме Borland
Отправить ссылку на страницу по e-mail