ТРИГГЕРЫ - Что это, и с чем его едят.

Тема в разделе 'MySQL', создана пользователем XmP, 11 сен 2011.

  1. XmP ADD ebx, 110h

    ТРИГГЕРЫ
    Триггер – разновидность хранимых процедур, связанных с таблицей или представлением и выполняемых только три изменении содержимого таблицы.
    Триггеры обычно сохраняются в виде фрагментов кода, прикрепленных к определенной таблице; они не имеют параметров и не возвращают значения.
    Имеется возможность использовать любые команды T-SQL за исключением: ALTER TABLE, CREATE TABLE, DROP TABLE, RESTORE LOG, RECONFIGURE, RESTORE DATABASE
    Триггеры выполняются только для протоколируемых операций (DELETE – да, TRUNCATE TABLE – нет).
    Сферы использования триггеров:
    – поддержка целостности в ситуациях, когда невозможно организовать управление с помощью декларативных ограничений;
    – создание контрольного журнала;
    – выполнение собственного программного кода вместо операторов пользователя.

    Синтаксис:

    Код:
    CREATE TRIGGER <имя_триггера>
    ON <имя_таблицы> | <имя_представления>
    [WITH ENCRYPTION]
    {{{FOR|AFTER}<[DELETE][,][INSERT][,][UPDATE]>) | INSTEAD OF}
    [WITH APPEND]
    AS
    <SQL-оператор>
    …
    …>
    WITHENCRYPTION – запрещение просмотра программного кода триггера на уровне сходного текста (кодирование)

    WITHAPPENDдля обратной совместимости с версией 6.5. Позволяет определить для одной таблицы несколько триггеров одного и того же типа.

    Существует для модели триггеров:
    INSTEAD OF – работает вместо команд, воздействуя на таблицу или представление. Для каждой таблицы можно иметь по одному триггеру для каждой операции или один объединенный.
    AFTER – запускается после команды, которая воздействовала на таблицу (но не на представление). Можно иметь неограниченное число триггеров, которые запускаются операциями INSERT, UPDATE, DELETE или любой их комбинацией.

    Доступ к измененным строкам таблицы:
    При работе триггера создаются специальные таблицы, содержащие измененные значения:
    – таблица INSERTED – заполняется во время операций INSERT, UPDATE;
    – таблица DELETED – заполняется во время операций DELETE, UPDATE.

    Определение модифицированных столбцов:
    1) UPDATE (<имя_столбца>)
    ПРИМЕР:
    Код:
    IF UPDATE(<имя_столбца>)
    BEGIN
    <SQL-оператор>
    END
    2) COLUMN_UPDATE () – возвращает шестнадцатеричное число с битовой маской столбцов в таблице
    Операции:
    | – ИЛИ
    & – И
    ^ – ИСКЛЮЧАЮЩЕЕ ИЛИ
    ПРИМЕР:
    COLUMN_UPDATE () > 0 – изменен хотя бы один столбец
    COLUMN_UPDATE () ^ 258 = 0 – изменены столбцы 2 и 9, остальные – нет
    COLUMN_UPDATE () & 258 = 258 – изменены столбцы 2 и 9
    COLUMN_UPDATE () | 258 = 258 – изменен любой из столбцов 2 или 9
    COLUMN_UPDATE () | 258 != 258 – изменены всех других столбцов, кроме 2 и 9

    Использование нескольких триггеров для одной операции:
    1) вариант вложенных триггеров
    sp_confugure ‘nested triggers’, 1 | 0
    Разрешение устанавливается на уровне сервера. По умолчанию – 1. Максимальная глубина вложенности – 32.
    2) вариант рекурсивных триггеров
    sp_dboption ‘<имя_БД>’, ‘recursive triggers’, ‘TRUE’ | ‘FALSE’
    Требует установленный вариант вложенных триггеров. Устанавливается на уровне БД.

    ЗАПРЕТ ТРИГГЕРОВ
    ALTER TABLE <имя_таблицы>
    <ENABLE|DISABLE> TRIGER <ALL|<имя_триггера>>

    ПОРЯДОК ЗАПУСКА ТРИГГЕРОВ
    sp_settriggerorder [@triggername =] ‘<имя_триггера>’,
    [@order =] ‘{FIRST | LAST | NONE}’,
    [@stmttype =] ‘{INSERT | UPDATE | DELETE}’
    Управление порядка определяется:
    – необходимостью подготовить некоторую основу для выполнения других триггеров;
    – оптимизацией производительности выполнения транзакций (триггеры, выполняющие откат, запускаются в первую очередь).

    Обработка ошибок, вызванных ограничениями
    Server: Msg <код_ошибки>, Level <уровень>, State <состояние>, Line <строка>
    <Сообщение>

    Msg – код ошибки:
    1-49999 – системные ошибки (синтаксис, дисковые ошибки, неправильные SQL-операторы);
    50000 – нерегламентированные сообщения об ошибках без заданного кода;
    50001 и больше – определяемые пользователем сообщения.

    Level – уровень серьезности ошибки (0-18 – сообщения пользователю, 16 – значение по умолчанию, 19-25 – действительно серьезные ошибки, связанные с порчей данных)

    State – характеризует состояние процесса, когда возникла ошибка (1-27).

    Line – строка в групповой операции или объекте, где возникла ошибка (для отладки)

    Доступ к коду ошибки:
    Код:
    SET @<имя_переменной> = @@error
    Создание ошибки:
    Код:
    RAISERROR ( { msg_id | msg_str } { , severity , state }
    
    [ ,argument [ ,...n ] ] )
    Создание пользовательского сообщения: sp_addmessage

    При возникновении ошибки выполнение операции, вызвавшей триггер, прерывается командой ROLLBACK TRAN.

    ПРИМЕРЫ:
    I. Эксклюзивные подкатегории.

    Код:
    CREATE TRIGGER ChildEntity1Trigger
    ON ChildEntity1
    FOR INSERT, UPDATE
    AS
    -- Проверка ID в главной таблице
    IFEXIST
    (
    SELECT ‘TRUE’
    FROM INSERTED i
    LEFT JOIN ParentEntity p
    ON i.ID = p.ID
    WHERE p.ID IS NULL
    )
    BEGIN
    RAISEERROR (‘ChildEntity1 item Must Have Corresponding ParentEntity Item’, 16, 1)
    ROLLBACK TRAN
    END
    -- Проверка ID в таблице ChildEntity2
    IF EXIST
    (
    SELECT *
    FROM INSERT i
    LEFT JOIN ChildIntity2 c
    ON i.ID = c.ID
    WHERE c.ID IS NOT NULL
    )
    BEGIN
    RAISEERROR (‘ChildEntity1 Record Exist’, 16, 1)
    ROLLBACK TRAN
    END
    II. Ограничение проверки данных в разных таблиц

    Код:
    CREATE TRIGGER ChildEntityTrigger
    ON ChildEntity
    FOR INSERT, UPDATE
    AS
    IF EXIST
    (
    SELECT ‘TRUE’
    FROM INSERTED i
    JOIN Parent p
    ON i.ID = p.ID
    WHERE <условие_на_запрещение>
    )
    BEGIN
    RAISEERROR (‘Value is not valid, 16, 1)
    ROLLBACK TRAN
    END
    III. Проверка дельты при модификации данных

    Код:
    CREATE TRIGGER EntityTrigger
    ON Entity
    FOR UPDATE
    AS
    IF EXIST
    (
    SELECT ‘TRUE’
    FROM INSERTED i
    JOIN DELETED d
    ON i.ID = d.ID
    WHERE (d.Value – i.Value) > d.Value / 2 AND
    d.Value – i.Value > 0
    )
    BEGIN
    RAISEERROR (‘Cannot reduce value by more than 50 %% at once’, 16, 1)
    ROLLBACK TRAN
    END
    
    IV. Обновление обобщенной информации
    Код:
    ALTERTABLECustomers
    ADD CurrentBalance money NOT NULL
    CONSTRAIN CurrentBalanceDefault
    DEFAULT 0 WITH VALUES
    CREATE TRIGGER OrderDetailAffectCustomerBalance
    ON [Order Details]
    FOR INSERT, UPDATE, DELETE
    AS
    UPDATE c
    SET c.CurrentBalance=c.CurrentBalance+i.UnitBalance*i.Quantity*(1– Discount)
    FROM Customer c
    JOIN Orders o
    ON c.CustomerID = o.CustomerID
    JOIN INSERTED i
    ON :confused:rderID = i.OrderID
    UPDATE c
    SET c.CurrentBalance=c.CurrentBalance-i.UnitBalance*i.Quantity*(1–Discount)
    FROM Customer c
    JOIN Orders o
    ON c.CustomerID = o.CustomerID
    JOIN DELETED d
    ON :confused:rderID = d.OrderID
    V. Запрещение удаления всех записей из таблицы
    Код:
    CREATE TRIGGER EntityTrigger1
    ON Entity
    FOR DELETE
    AS
    IF (SELECT COUNT(*) FROM Entity) < 1
    BEGIN
    RAISERROR ('Error', 16, 1)
    ROLLBACK TRAN
    END
    
    VI. Автоматическое поддержание полей

    Код:
    CREATE TRIGGER UpperFieldInsertTrigger
    ON Entity
    INSTEAD OF INSERT
    AS
    INSERT INTO Entity (Field1, Field2, Field3)
    SELECT UPPER(Field1), Field2, Field3
    FROM INSERTED
    CREATE TRIGGER UpperFieldUpdateTrigger
    ON Entity
    INSTEAD OF UPDATE
    AS
    UPDATE Entity
    SET Field1 = UPPER(INSERTED.Field1),
    Fireld2 = INSERTED.Field2,
    Field3 = INSERTED.Field3
    FROM Entity
    FOIN INSERTED ON Entity.ID = INSERTED.ID

Поделиться этой страницей