Что такое триггер sql

Триггер является указанием, что база данных должна автоматически выполнить заданную функцию, всякий раз когда выполнен определённый тип операции. Триггеры можно использовать с таблицами (секционированными и обычными), с представлениями и с внешними таблицами.

Для обычных и сторонних таблиц можно определять триггеры, которые будут срабатывать до или после любой из команд INSERT , UPDATE или DELETE ; либо один раз для каждой модифицируемой строки, либо один раз для оператора SQL . Триггеры на UPDATE можно установить так, чтобы они срабатывали, только когда в предложении SET оператора UPDATE упоминаются определённые столбцы. Также триггеры могут срабатывать для операторов TRUNCATE . Если происходит событие триггера, для обработки этого события в установленный момент времени вызывается функция триггера.

Для представлений триггеры могут быть определены для выполнения вместо операций INSERT , UPDATE и DELETE . Такие триггеры INSTEAD OF вызываются единожды для каждой строки, которая должна быть изменена в этом представлении. Именно функция триггера отвечает за то, чтобы произвести необходимые изменения в нижележащих базовых таблицах представления и должным образом возвращать изменённые строки, чтобы они появлялись в представлении. Триггеры для представлений тоже могут быть определены так, что они будут выполняться единожды для всего оператора SQL , до или после операций INSERT , UPDATE или DELETE . Однако такие триггеры срабатывают, только если для представления определён триггер INSTEAD OF . В противном случае все операторы, обращающиеся к представлению, должны быть переписаны в виде операторов, обращающихся к нижележащим базовым таблицам, и тогда будут срабатывать триггеры, установленные для этих таблиц.

Триггерная функция должна быть создана до триггера. Она должна быть объявлена без аргументов и возвращать тип trigger . (Триггерная функция получает данные на вход посредством специально переданной структуры TriggerData , а не в форме обычных аргументов.)

После создания триггерной функции создаётся триггер с помощью CREATE TRIGGER . Одна и та же триггерная функция может быть использована для нескольких триггеров.

PostgreSQL предлагает как построчные, так и операторные триггеры. В случае построчного триггера триггерная функция вызывается один раз для каждой строки, затронутой оператором, запустившим триггер. Операторный же триггер, напротив, вызывается только один раз при выполнении соответствующего оператора, независимо от количества строк, которые он затрагивает. В частности оператор, который не затрагивает никаких строк, всё равно приведёт к срабатыванию операторного триггера. Эти два типа триггеров также называют триггерами уровня строк и триггерами уровня оператора, соответственно. Триггеры на TRUNCATE могут быть определены только на уровне оператора, а не на уровне строк.

Триггеры также классифицируются в соответствии с тем, срабатывают ли они до, после или вместо операции. Они называются триггерами BEFORE , AFTER и INSTEAD OF , соответственно. Триггеры BEFORE уровня оператора срабатывают до того, как оператор начинает делать что-либо, тогда как триггеры AFTER уровня оператора срабатывают в самом конце работы оператора. Эти типы триггеров могут быть определены для таблиц, представлений или сторонних таблиц. Триггеры BEFORE уровня строки срабатывают непосредственно перед обработкой конкретной строки, в то время как триггеры AFTER уровня строки срабатывают в конце работы всего оператора (но до любого из триггеров AFTER уровня оператора). Эти типы триггеров могут определяться только для обычных и сторонних таблиц, но не для представлений; триггеры уровня строк BEFORE не могут определяться для секционированных таблиц. Триггеры INSTEAD OF могут определяться только для представлений и только на уровне строк: они срабатывают для каждой строки сразу после того, как строка представления идентифицирована как подлежащая обработке.

Оператор, нацеленный на родительскую таблицу в иерархии наследования или секционирования, не вызывает срабатывания триггеров уровня оператора для задействованных дочерних таблиц; срабатывать будут только такие триггеры для родительской таблицы. Однако если для этих дочерних таблиц установлены триггеры уровня строк, они будут срабатывать.

Если запрос INSERT содержит предложение ON CONFLICT DO UPDATE , возможно совместное применение и триггеров уровня строк BEFORE INSERT , и триггеров уровня строк BEFORE UPDATE , которое отразится в окончательном состоянии изменяемой строки, если в запросе задействуются столбцы EXCLUDED . При этом обращение к EXCLUDED не обязательно должно иметь место в обоих наборах триггеров BEFORE на уровне строк. Следует рассмотреть возможность получения неожиданного результата, когда имеются и триггеры BEFORE INSERT , и BEFORE UPDATE на уровне строки, и они вместе модифицируют добавляемую/изменяемую строку (проблемы возможны, даже если изменения более или менее равнозначные, но при этом не идемпотентные). Заметьте, что триггеры UPDATE уровня оператора вызываются при ON CONFLICT DO UPDATE независимо от того, будут ли изменены какие-либо строки в результате UPDATE (и даже в случае, когда альтернативный путь UPDATE вообще не выбирается). При выполнении запроса INSERT с предложением ON CONFLICT DO UPDATE сначала выполняются триггеры BEFORE INSERT , затем триггеры BEFORE UPDATE , потом триггеры AFTER UPDATE и, наконец, AFTER INSERT (речь идёт о триггерах на уровне операторов).

Если оператор UPDATE в секционированной таблице должен переместить строку в другую секцию, это перемещение реализуется в результате выполнения DELETE в исходной секции и последующего INSERT в новой секции. При этом в исходной секции срабатывают все триггеры BEFORE UPDATE и BEFORE DELETE уровня строк. Затем в целевой секции срабатывают все триггеры BEFORE INSERT уровня строк. Следует иметь в виду, что в случаях, когда все эти триггеры модифицируют перемещаемую строку, полученный результат может быть неожиданным. Если рассматривать триггеры AFTER ROW , то применяться будут триггеры AFTER DELETE и AFTER INSERT , но не триггеры AFTER UPDATE , так как команда UPDATE заменяется на DELETE и INSERT . Если же рассматривать триггеры уровня операторов, ни триггеры DELETE , ни триггеры INSERT не будут срабатывать, даже если производится перемещение строк; сработают только триггеры UPDATE , установленные в целевой таблице оператора UPDATE .

Триггерные функции, вызываемые триггерами операторов, должны всегда возвращать NULL . Триггерные функции, вызываемые триггерами строк, могут вернуть строку таблицы (значение типа HeapTuple ). У триггера уровня строки, срабатывающего до операции, есть следующий выбор:

Можно вернуть NULL , чтобы пропустить операцию для текущей строки. Это указывает исполнителю запросов, что не нужно выполнять операцию со строкой вызвавшей триггер (вставку, изменение или удаление конкретной строки в таблице).

Если в триггере BEFORE уровня строки не планируется использовать любой из этих вариантов, то нужно аккуратно вернуть в качестве результата ту же строку, которая была передана на вход (то есть строку NEW для триггеров INSERT и UPDATE , или строку OLD для триггеров DELETE ).

Триггер уровня строки INSTEAD OF должен вернуть либо NULL , чтобы указать, что он не модифицирует базовые таблицы представления, либо он должен вернуть строку представления, полученную на входе (строку NEW для операций INSERT и UPDATE или строку OLD для операций DELETE ). Отличное от NULL возвращаемое значение сигнализирует, что триггер выполнил необходимые изменения данных в представлении. Это приведёт к увеличению счётчика количества строк, затронутых командой. Для операций INSERT и UPDATE (и только для них) триггер может изменить строку NEW перед тем как её вернуть. В результате будут изменены данные, возвращаемые INSERT RETURNING или UPDATE RETURNING , что полезно, когда представление должно возвращать не те данные, что были получены.

Возвращаемое значение игнорируется для триггеров уровня строки, вызываемых после операции, поэтому они могут возвращать NULL .

Если есть несколько триггеров на одно и то же событие для одной и той же таблицы, то они будут вызываться в алфавитном порядке по имени триггера. Для триггеров BEFORE и INSTEAD OF потенциально изменённая строка, возвращаемая одним триггером, становится входящей строкой для следующего триггера. Если любой из триггеров BEFORE или INSTEAD OF возвращает NULL , операция для этой строки прекращается и последующие триггеры (для этой строки) не срабатывают.

В определении триггера можно указать логическое условие WHEN , которое будет проверяться, чтобы посмотреть, нужно ли запускать триггер. В триггерах уровня строки в условии WHEN можно проверять старые и/или новые значения столбцов строки. (В триггерах уровня оператора также можно использовать условие WHEN , хотя в этом случае это не так полезно.) В триггерах BEFORE условие WHEN вычисляется непосредственно перед тем, как триггерная функция будет выполнена, поэтому использование WHEN существенно не отличается от выполнения той же проверки в самом начале триггерной функции. Однако в триггерах AFTER условие WHEN вычисляется сразу после обновления строки и от этого зависит, будет ли поставлено в очередь событие запуска триггера в конце оператора или нет. Поэтому, когда условие WHEN в триггере AFTER не возвращает истину, не требуется ни постановка события в очередь, ни повторная выборка этой строки в конце оператора. Это может существенно ускорить работу операторов, изменяющих большое количество строк, с триггером, который должен сработать только для нескольких. В триггерах INSTEAD OF не поддерживается использование условий WHEN .

Как правило, триггеры BEFORE уровня строки используются для проверки или модификации данных, которые будут вставлены или изменены. Например, триггер BEFORE можно использовать для вставки текущего времени в столбец timestamp или проверки, что два элемента строки согласованы между собой. Триггеры AFTER уровня строки наиболее разумно использовать для каскадного обновления данных в других таблицах или проверки согласованности сделанных изменений с данными в других таблицах. Причина для такого разделения работы в том, что триггер AFTER видит окончательное значение строки, в то время как для триггера BEFORE это не так, ведь могут быть другие триггеры BEFORE , которые сработают позже. Если нет особых причин для выбора между триггерами BEFORE или AFTER , то триггер BEFORE предпочтительнее, так как не требует сохранения информации об операции до конца работы оператора.

Если триггерная функция выполняет команды SQL, эти команды могут заново запускать триггеры. Это известно как каскадные триггеры. Прямых ограничений на количество каскадных уровней не существует. Вполне возможно, что каскадные вызовы приведут к рекурсивному срабатыванию одного и того же триггера. Например, в триггере INSERT может выполняться команда, которая добавляет строку в эту же таблицу, тем самым опять вызывая триггер на INSERT . Обязанность программиста не допускать бесконечную рекурсию в таких случаях.

При определении триггера можно указывать аргументы. Цель включения аргументов в определение триггера в том, чтобы позволить разным триггерам с аналогичными требованиями вызывать одну и ту же функцию. В качестве примера можно создать обобщенную триггерную функцию, которая принимает два аргумента с именами столбцов и записывает текущего пользователя в первый аргумент и текущий штамп времени во второй. При правильном написании такая триггерная функция будет независима от конкретной таблицы, для которой она будет запускаться. Таким образом, одна и та же функция может использоваться при выполнении INSERT в любую таблицу с соответствующими столбцами, чтобы, например, автоматически отслеживать создание записей в транзакционной таблице. Для триггеров UPDATE аргументы также могут использоваться для отслеживания последних сделанных изменений.

У каждого языка программирования, поддерживающего триггеры, есть свой собственный метод доступа из триггерной функции к входным данным триггера. Входные данные триггера включают в себя тип события (например, INSERT или UPDATE ), а также любые аргументы, перечисленные в CREATE TRIGGER . Для триггеров уровня строки входные данные также включают строку NEW для триггеров INSERT и UPDATE и/или строку OLD для триггеров UPDATE и DELETE .

Триггеры уровня оператора по умолчанию не имеют возможностей для проверки отдельных строк, модифицированных оператором. Но триггер AFTER STATEMENT может запросить создание для него переходных таблиц, чтобы ему были доступны наборы затрагиваемых операцией строк. Триггерам AFTER ROW также могут предоставляться переходные таблицы, чтобы они могли видеть все изменения в таблице, а не только изменения в отдельных строках, для которых они срабатывают. Метод обращения к переходным таблицам определяется применяемым языком программирования, но обычно переходные таблицы представляются как временные таблицы только для чтения, к которым в триггерной функции можно обращаться, выполняя SQL-команды.

Пред.	Наверх	След.
Глава 39. Триггеры	Начало	39.2. Видимость изменений в данных

Что такое триггер sql

Триггер — это PL/SQL блок, который хранится в базе данных и срабатывает (выполняется) в ответ на указанное событие.

Триггер может быть определён на таблицу, представление, схему (владельца схемы) или базу данных (всех пользователей).

Типы событий триггеров:

DML-команда ( DELETE , INSERT или UPDATE ).
DDL-команды ( CREATE , ALTER или DROP ).
Операции базы данных, такие как SERVERERROR , LOGON , LOGOFF , STARTUP или SHUTDOWN .

Пример создания триггера:

CREATE OR REPLACE TRIGGER check_salary_trg -- перед INSERT в Employees или UPDATE столбцов Salary или Job_ID BEFORE INSERT OR UPDATE OF Salary, Job_ID ON Employees [REFERENCING OLD AS old_name | NEW AS new_name] -- можно переименовывать OLD и NEW FOR EACH ROW -- строковый триггер, для каждой строки (не для всей DML-команды) BEGIN -- NEW — объект типа строки Employees, хранит строку после DML-команды -- OLD — то же самое, но до DML-операции check_salary(:NEW.Job_ID, :NEW.Salary); -- вызов процедуры check_salary END; /

2. Типы триггеров

Триггеры делятся на:

Триггеры базы данных. Срабатывают при возникновении DML, DDL или системного события в схеме или базе данных.
Триггеры приложений. Срабатывают при возникновении события в конкретном приложении.

Триггеры можно использовать для:

Безопасности.
Аудита.
Целостности данных.
Ссылочной целостности.
Репликации (копирования) таблиц.
Автоматического вычисления производных данных.
Ведения журнала событий.

Допустимые типы триггеров:

Простые DML-триггеры ( BEFORE , AFTER , INSTEAD OF (только для view)).
Составные (compound) триггеры.
Не DML-триггеры (DDL-события, события базы данных).

Уровня команды (по умолчанию). Срабатывают один раз на событие триггера, даже тогда, когда не затронута ни одна из строк.
Строковыми. Нужно прописать FOR EACH ROW , срабатывают один раз на каждую затронутую строку по событию триггера, не срабатывают, если событие не затронуло ни одной строки.

3. Использование условных предикатов

Можно так же задавать условия для выполнения триггеров.

В теле триггера можно использовать конструкции (если триггер состоит из нескольких DML-операций):

IF DELETING ,
IF UPDATING ,
IF INSERTING ,

чтобы определить, какое событие вызвало триггер, который слушает несколько событий.

CREATE OR REPLACE TRIGGER triggerName [BEFORE | AFTER] INSERT OR UPDATE OR DELETE ON Table_Name BEGIN IF DELETING THEN -- . ELSIF UPDATING THEN -- . ELSIF INSERTING THEN -- . END IF; END; /

Можно так же использовать WHEN для строковых триггеров.

CREATE OR REPLACE TRIGGER check_salary_trg BEFORE INSERT OR UPDATE OF Salary, Job_ID ON Employees FOR EACH ROW /* проверяем, что мы действительно обновляем столбец Job_ID или Salary или же вставляем новую строку (без этого триггер сработает на UPDATE, даже если фактическое значение не изменилось). */ WHEN ( OLD.Job_ID IS NULL OR OLD.Salary IS NULL OR OLD.Job_ID != NEW.Job_ID OR OLD.Salary != NEW.Salary ) -- Заметим, что внутри WHEN пишем «OLD», а внутри BEGIN..END — «:OLD». BEGIN check_salary(:NEW.Job_ID, :NEW.Salary); END; /

О NEW и OLD (доступны только в строковых триггерах):

DML-команда	OLD	NEW
INSERT	NULL	введённое значение
UPDATE	значение до обновления	значение после обновления
DELETE	удаляемое значение	NULL

Краткое описание модели выполнения триггера:

Выполняются все BEFORE триггеры уровня команды.

Для каждой строки, затронутой триггером:

Выполняются все строковые BEFORE триггеры
Выполняется DML-команда и проверка ограничений целостности.
Выполняются все строковые AFTER триггеры.

У триггера есть 2 состояния: ENABLE и DISABLE . Синтаксис:

CREATE OR REPLACE TRIGGER triggerName BEFORE INSERT ON tableName FOR EACH ROW DISABLE -- создание выключенного триггера BEGIN -- код END; / -- триггер можно выключить/включить, например, так: ALTER TRIGGER triggerName DISABLE; -- выключаем ALTER TRIGGER triggerName ENABLE; -- включаем

4. Тестирование триггеров

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

Информацию о триггерах можно посмотреть в словарях USER_OBJECTS , USER/ALL/DBA_TRIGGERS . Синтаксис ошибок триггеров можно посмотреть в словаре USER_ERRORS .

5. Составные (compound) триггеры

Составной (compound) триггер — это один триггер на таблицу, позволяющий задать действия для каждой из следующих четырёх точек синхронизации:

Перед вызывающей командой.
Перед каждой строкой, на которую влияет вызывающая команда.
После каждой строки, на которую влияет вызывающая команда.
После вызывающей команды.

Таблица, которая изменяется с помощью команды UPDATE , DELETE или INSERT , или
Таблица, которая может быть обновлена под действием ограничения DELETE CASCADE .

Составные триггеры можно использовать для:

Программирования подхода, в котором вы хотите, чтобы действия, которые вы выполняете для разных точек синхронизации, могли совместно использовать общие данные.
Накопления строк, предназначенных для второй таблицы; так что вы можете периодически их вставлять.
Того, чтобы избежать ошибки мутирующей таблицы (ORA-04091) , разрешив накопление строк, предназначенных для второй таблицы, а затем их массовую вставку.

Ограничения составных триггеров:

Составной триггер должен быть DML-триггером и определяться на таблицу или представление.
Тело составного триггера должно быть блоком составного триггера, написанным на языке PL/SQL.
Тело составного триггера не может иметь блок Инициализации, поэтому оно не может содержать раздел Исключений.
Исключение, которое происходит в одном разделе, должно быть обработано в том же разделе. Он не может передать управление другому разделу.
:OLD и :NEW нельзя использовать в разделах Объявления, BEFORE STATEMENT и AFTER STATEMENT .
Только раздел BEFORE EACH ROW может изменять значение :NEW .
Порядок срабатывания составных триггеров не гарантируется, если вы не используете FOLLOWS .

Ограничение триггеров на мутирующие таблицы:

Сессия, использующая вызывающий триггер оператор, не может изменять мутирующую таблицу или выполнять к ней запросы.
Это ограничение не позволяет триггеру видеть несогласованный набор данных.
Это ограничение применяется ко всем триггерам, использующим раздел FOR EACH ROW .
Представления, изменённые в триггерах INSTEAD OF , не считаются мутирующими.

6. Создание триггеров системных событий

Триггеры LOGON и LOGOFF . Пример:

CREATE OR REPLACE TRIGGER logon_trig AFTER LOGON ON SCHEMA BEGIN INSERT INTO log_trig_table(user_id, log_date, action) VALUES (USER, SYSDATE, 'Logging on'); END; / CREATE OR REPLACE TRIGGER logoff_trig BEFORE LOGOFF ON SCHEMA BEGIN INSERT INTO log_trig_table(user_id, log_date, action) VALUES (USER, SYSDATE, 'Logging off'); END; /

Можно использовать команду CALL внутри триггера:

CREATE OR REPLACE PROCEDURE log_execution IS BEGIN DBMS_OUTPUT.PUT_LINE('log_exection: Employee Inserted'); END; / CREATE OR REPLACE TRIGGER log_employee BEFORE INSERT ON EMPLOYEES CALL log_execution -- точка с запятой не требуется /

Преимущества триггеров на события базы данных:

Повышают безопасность данных:

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

Применение ограничений динамической целостности данных.
Применение сложных ограничений ссылочной целостности.
Гарантия того, что связанные операции выполняются вместе неявно.

Системные привилегии, необходимые для управления триггерами:

Привилегия CREATE/ALTER/DROP (ANY) TRIGGER , которая позволяет создавать триггеры в любой схеме.
Привилегия ADMINISTER DATABASE TRIGGER , которая позволяет создавать триггеры базы данных.
Привилегия EXECUTE (если триггер ссылается на объекты, которых нет в схеме).

7. Рекомендации по Разработке Триггеров

Триггеры разрабатываются для:

Выполнения связанных действий.
Централизации глобальных операций.

Если функциональность уже встроена в сервер Oracle.
Которые дублируют уже существующие триггеры.

Триггеры — спасители

Уже много статей в интернете есть про sql триггеры, но добавлю еще одну с адекватными примерами, что бы закрепить материал для тех, кто «в теме» и что бы лучше понять материал тем, кто только начал постигать «дзен sql». Заодно и создам дискуссию по теме.

Сразу оговорюсь, что мое мнение — это только мое мнение, оно порой сильно категорично. В силу ряда причин приходится работать с высоконагруженными сайтами и сложными веб-приложениями.

Из работы над ними вынесли один ценный опыт — следить за приоритетами и статистикой. Что это значит? Все просто: если у Вас блог и у него 2-3-4-10012 млн посетителей в сутки, а статьи пишутся всего 1-2-3-3435 раз в сутки (на порядок меньше чем число просмотров), то скорость сохранения статьи (и сложность этого) относительно скорости показа статьи может быть пропорционально меньше. Чем больше показываем, тем критичен именно показ, а не сохранение статьи/страницы/таблицы. Что не означает, что и расслабляться можно. Сохранение статьи за 3-5-10 секунд в блоге — это в рамках адекватности, но генерация страницы за срок более 2 секунды (+ пока скрипты и стили с картинками подгрузятся) — это на грани «какой тормознутый сайт, почитаю что-то иное», а еще хуже «пойду куплю в другом месте».

Если мы возьмем среднестатистический сайт с голосовалкой/кармой, комментариями, счетчиком показа страницы и т.п., то многим разработчикам сразу в голову приходят конструкции вроде SELECT count(*) FROM comment WHERE comment.page=page_id. Ну подумаешь на каждую статью посчитать сумму рейтинга, сумму комментариев. А, у нас на главной по 10 статей из каждого раздела. При посещаемости в 10 человек в секунду, на среднем VPS, можно себе позволить по 60-100 запросов к sql на страницу (привет, битрикс).

Но к черту лирику (достал уже, наверное). Голые данные:

CREATE TABLE IF NOT EXISTS `blog` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(128) NOT NULL, `text` text NOT NULL, `creation` datetime NOT NULL, `modification` datetime NOT NULL, `img` varchar(128) NOT NULL DEFAULT 'default.png', `status` tinyint(4) NOT NULL DEFAULT '2', `user_id` int(11) NOT NULL, `rate` int(11) NOT NULL, `relax_type` tinyint(4) NOT NULL, `timers` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `contest` tinyint(1) NOT NULL DEFAULT '0', `views` int(11) NOT NULL DEFAULT '0', `comment` int(11) NOT NULL, `url` varchar(128) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `url` (`url`), KEY `country_id` (`country_id`), KEY `user_id` (`user_id`), KEY `status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1456435 ;

CREATE TABLE IF NOT EXISTS `comments` ( `owner_name` varchar(50) NOT NULL, `owner_id` int(12) NOT NULL, `id` int(12) NOT NULL AUTO_INCREMENT, `parent_id` int(12) DEFAULT NULL, `user_id` int(12) DEFAULT NULL, `text` text, `creation` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `status` int(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `owner_name` (`owner_name`,`owner_id`), KEY `parent_id` (`parent_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=243254252 ;

Как видим, в таблице блога у каждой статьи есть счетчик комментариев (поле comment).
Обычная практика:
1. Добавили комментарий — увеличили счетчик для блога
2. Удалили/скрыли комментарий — уменьшили счетчик.
Делать это в коде удобно и привычно, но есть более удобный инструмент — триггеры.

И так, у нас есть 2 события (на самом деле 3): создание комментария и его удаление (третье событие — это изменение его статуса («удаление», бан и т. п.).
Рассмотрим только создание и удаление, а изменение статуса пусть будет домашним заданием.

В примере есть одна особенность: комментарии могут быть к нескольким типам статей.

CREATE TRIGGER `add_count_comment` AFTER INSERT ON `comments` FOR EACH ROW BEGIN // у пользователя в личном кабинете посчитаем сколько он комментариев написал UPDATE user SET user.countcomment= user.countcomment+1 WHERE user.id = NEW.user_id; // определяем к чему относится комментарий и сразу увеличиваем счетчик в данных таблицах CASE NEW.`owner_name` WHEN 'Blog' THEN UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`+1 WHERE `blog`.id = NEW.`owner_id` ; WHEN 'Article' THEN UPDATE `article` SET `article`.`comment` = `article`.`comment`+1 WHERE `article`.`id` = NEW.`owner_id` ; WHEN 'PopulatePlace' THEN UPDATE `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`+1 WHERE `populate_place`.`id` = NEW.`owner_id` ; END CASE; // тут мы облегчаем себе работу с лентами новостей // url статьи сразу пишем, что бы ПОТОМ не делать выборок лишних CASE NEW.`owner_name` WHEN 'Blog' THEN SET userurl = (SELECT url FROM `blog` WHERE `blog`.id= NEW.`owner_id`); WHEN 'Article' THEN SET userurl = (SELECT url FROM `article` WHERE article.id=NEW.`owner_id`); WHEN 'PopulatePlace' THEN SET userurl = ``; END CASE; // название статьи сразу пишем, что бы ПОТОМ не делать выборку CASE NEW.`owner_name` WHEN 'Blog' THEN SET usertitle = (select title from `blog` where blog.id=NEW.`owner_id`); WHEN 'Article' THEN SET usertitle = (select title from `article` where article.id=NEW.`owner_id`); WHEN 'PopulatePlace' THEN SET usertitle = ` `; END CASE; INSERT INTO user_has_events VALUES (NEW.user_id,NEW.id,"Comments",NOW(),userurl , usertitle ); END

Аналогично и удаление комментария:

CREATE TRIGGER `del_count_comment` AFTER DELETE ON `comments` FOR EACH ROW BEGIN UPDATE user SET user.countcomment= user.countcomment -1 WHERE user.id = OLD.user_id; CASE OLD.`owner_name` WHEN 'Blog' THEN UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`-1 WHERE `blog`.`id` = OLD.`owner_id` ; WHEN 'Article' THEN UPDATE `article` SET `article`.`comment` = `article`.`comment`-1 WHERE `article`.`id` = OLD.`owner_id` ; WHEN 'PopulatePlace' THEN UPDATE `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`-1 WHERE `populate_place`.`id` = OLD.`owner_id` ; END CASE; END

И так, что получили:
1. При вставке комментария у нас автоматически средствами sql сервера посчиталась сумма комментариев у конкретного объекта комментирования (статья, страница, заметка)
2. Мы сформировали ленту новостей (привет всем соцсетям и т. п.)
3. При удалении комментария у нас происходит вычет всех данных.
4. Мы не использовали средства фреймворка.
5. Выборка всех нужных данных происходит быстро (всего 1 запрос при показе страницы, за исключением прочих «левых» данных на ней.)

А еще у нас стоит sphinx который периодически делает выборки статей, которые изменились за последнюю минуту. Для этого в блоге есть поле modification.

CREATE TRIGGER `ins_blog` BEFORE INSERT ON `blog` // делаем вставку времени до сохранения информации путем «подмены» данных. FOR EACH ROW BEGIN SET NEW.modification = NOW(); END

Теперь делая выборку за последнюю минуту мы получим все документы которые добавились за последнюю минуту.

CREATE TRIGGER `ins_blog` BEFORE UPDATE ON `blog` // делаем вставку времени до сохранения информации путем «подмены» данных. FOR EACH ROW BEGIN SET NEW.modification = NOW(); END

При изменении данных — обновим поисковый индекс тоже.

Обычно в среднем проекте все что можно перенести на сторону sql сервера — переносим. Сам sql сервер делает подобные операции быстрее и с меньшими ресурсами, чем это можно сделать через используемый язык программирования.

UPD: Холивар посвященный целесообразности усложнения структуры БД объявляется открытым.

Create Trigger

Триггеры sql представляют собой специальный тип хранимых процедур, запускаемых сервером автоматически при изменении данных (DML) в таблице, с которой он связан. Триггеры подключаются к определенной таблице. Все производимые триггером изменения данных рассматриваются как одна транзакция.

В отличие от обычной хранимой процедуры/функции, триггер вызывается сервером неявно при возникновения определенного триггерного события. Кроме этого триггер SQL не имеет аргументов. С помощью триггера решаются следующие задачи :

проверка корректности изменяемых данных и проверка сложных ограничений целостности данных, которые необходимо поддерживать;
накопление статистической информации посредством фиксации сведений о внесимых изменениях;
поддержка репликации.

С помощью ограничений целостности, установленных правил и значений не всегда можно добиться нужного уровня целостности данных. Иногда требуется реализовать сложные алгоритмы проверки данных, гарантирующие их достоверность и реальность. Кроме того, часто необходимо отслеживать изменения значений таблицы, чтобы нужным образом изменить связанные данные. Триггеры SQL можно рассматривать как своего рода фильтры, вступающие в действие после выполнения всех операций в соответствии с правилами, стандартными значениями и т.д.

Применение SQL триггеров связано с дополнительными затратами ресурсов сервера на операции добавления (trigger insert), обновления (trigger update) или удаления (trigger delete) данных в таблице.

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

Ссылки по странице :

ms sql trigger
postgres trigger
oracle triggers
oracle triggers отключение
Пример триггера ms sql
Пример триггера postgres
Пример триггера oracle

CREATE TRIGGER

Основной формат команды CREATE TRIGGER показан ниже:

CREATE TRIGGER trigger_name [ BEFORE | AFTER ] [INSERT | UPDATE | DELETE] ON [schema_name.]table_name [REFERENCING ] [FOR EACH < ROW | STATEMENT>] begin end;

Момент запуска триггера определяется ключевыми словами BEFORE (триггер запускается перед выполнением связанного с ним событием; например, до добавления записи) или AFTER (после события). Если триггер вызывается до события, он может внести изменения в модифицируемую событием запись, если событие — не удаление записи. Некоторые СУБД накладывают ограничения на операторы, которые могут быть использованы в триггере (например, может быть запрещено вносить изменения в таблицу, с которой «связан» триггер, и т.п.).

Триггеры могут быть подключены не к таблице, а к представлению VIEW. В этом случае с их помощью реализуется механизм «обновляемого представления». При подключении триггера к представлению ключевые слова BEFORE и AFTER влияют лишь на последовательность вызова триггеров, так как собственно событие (удаление, вставка или обновление) не происходит.

Триггер может быть вызван для каждой строки (FOR EACH ROW), охваченной данным событием, или только один раз для каждого события (FOR EACH STATEMENT).

Обозначение относится к таким компонентам, как старая или новая строка (OLD / NEW) либо старая или новая таблица (OLD TABLE / NEW TABLE). Cтарые значения не применимы для событий вставки, а новые – для событий удаления.

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

Некорректно написанные триггеры могут привести к серьезным проблемам, связанным с появлением блокировок. Триггеры способны длительное время блокировать ресурсы, поэтому следует обратить особое внимание на сведение к минимуму конфликтов доступа.

MS SQL trigger

Синтаксис создания триггера в СУБД MS SQL имеет следующий вид :

CREATE TRIGGER [schema_name.]trigger_name ON [table_name | view_name] [FOR | AFTER | INSTEAD OF] [[DELETE] [,] [INSERT] [,] [UPDATE]] [ WITH APPEND ] [ NOT FOR REPLICATION ] AS

schema_name

Наименование схемы триггера DML. Действие триггеров DML ограничивается областью схемы таблицы или представления, для которых они созданы. schema_name не может указываться для триггеров DDL или триггеров входа.

trigger_name

Наименование триггера. Аргумент trigger_name должен соответствовать правилам для идентификаторов — за исключением того, что trigger_name не может начинаться с символов # или ##.

table_name | view_name

Таблица или представление, к которым подключен триггер.

Пример ms sql trigger

Для реализации триггера будут созданы две таблицы : test_table, test_log. К таблице test_table будет подключен триггер. При обновлении записей в таблице test_table триггер будет регистрировать в таблице test_log результаты изменений. Т.е. триггер будет вызываться по событию update.

Тестовая таблица test_table :

create table dbo.test_table ( id int not null, field1 varchar(255) null, field2 varchar(255) null, constraint pkTestTableID primary key (id) );

Таблица журналирования test_log :

create table dbo.test_log ( id bigint identity(1,1) not null, table_name varchar(50) not null, oper varchar(15) not null, record_old xml null, record_new xml null, data datetime null, constraint pkTestLogID primary key (id) );

Триггер обновления данных :

-- trigger update create trigger dbo.trg_test_table_update on dbo.test_table for UPDATE as begin set nocount on -- переменные для хранения старых и новых данных declare @record_new xml; declare @record_old xml; -- в таблице deleted хранятся старые/удаленные данные set @record_old = (SELECT * FROM deleted FOR XML RAW, TYPE); -- в таблице inserted хранятся измененные (только что созданные) данные set @record_new = (SELECT * FROM inserted FOR XML RAW, TYPE); if (@record_new is not null) and (@record_old is not null) begin insert into dbo.test_log (table_name, oper, record_old, record_new, data) values ('test_table', 'update', @record_old, @record_new, GETDATE()) end; end;

Добавим несколько строк в тестовую таблицу, которые будем обновлять для тестирование триггера :

insert into dbo.test_table (id, field1, field2) values (1, 'Кофе', 'Nescafe'); insert into dbo.test_table (id, field1, field2) values (2, 'Чай' , 'Greenfield');

Проверяем работу триггера обновлением строк :

update dbo.test_table set field1 = 'Сахар', field2 = 'Рафинад' where dbo.test_table set field1 = 'Хлеб', field2 = 'Бородинский' where >Проверяем таблицу журналирования test_log. Результат должен выглядеть так, как это представлено на скриншоте :

XML данные просматриваются и показывают, что таблица журналирования включает как старые, так и новые значения.

PostgreSQL trigger

Синтаксис создания триггера

CREATE TRIGGER [schema_name.]trigger_name [BEFORE | AFTER ] [ событие [ OR событие ]] ON table_name FOR EACH < ROW | STATEMENT >EXECUTE PROCEDURE function_name ( аргументы )

В аргументе указывается наименование создаваемого триггера. При необходимости может быть указано наименование схемы.

Ключевое слово BEFORE означает, что trigger before и функция должна выполняться перед выполнением соответствующего события. Ключевое слово AFTER означает, что trigger after и функция вызывается после завершения операции, приводящей в действие триггер.

В PostgreSQL поддерживаются следующие события [INSERT | DELETE | UPDATE]. При перечислении нескольких событий в качестве разделителя используется ключевое слово OR.

Наименование таблицы, модификация которой приводит к срабатыванию триггера.

Ключевое слово, следующее за конструкцией FOR EACH и определяющее количество вызовов функции при наступлении указанного события. Использование ROW означает, что функция вызывается для каждой модифицируемой записи. Если функция должна вызываться всего один раз для всей команды, используется ключевое слово STATEMENT.

EXECUTE PROCEDURE function_name

Наименование вызываемой функции с аргументами. На практике аргументы при вызове триггерных функций не используются.

Синтаксис определения триггерной функции

CREATE FUNCTION function_name () RETURNS trigger AS DECLARE -- объявления переменных BEGIN -- тело триггерной функции END; LANGUAGE plpgsql;

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

Наименование	Тип	Описание
NEW	RECORD	Новые значения полей записи, созданной командой INSERT или обновленной командой UPDATE, при срабатывании триггера уровня записи (ROW). Переменная используется для модификации новых записей. Переменная NEW доступна только при INSERT и UPDATE. Поля записи NEW могут быть изменены триггером.
OLD	RECORD	Старые значения полей записи, содержавшиеся в записи перед выполнением команды DELETE или UPDATE при срабатывании триггера уровня записи (ROW). Переменная OLD доступна только при DELETE и UPDATE. Поля записи OLD можно использовать только для чтения, изменять нельзя.
TG_NAME	name	Имя сработавшего триггера.
TG_WHEN	text	Операторы BEFORE или AFTER в зависимости от момента срабатывания триггера, указанного в определении.
TG_LEVEL	text	Строка ROW или STATEMENT в зависимости от уровня триггера, указанного в определении.
TG_OP	text	Строка INSERT, UPDATE или DELETE в зависимости от операции, вызвавшей срабатывание триггера.
TG_RELID	oid	Идентификатор объекта таблицы, в которой сработал триггер.
TG_RELNAME	name	Имя таблицы, в которой сработал триггер.

К отдельным полям записи NEW и OLD в триггерных процедурах обращаются следующим образом: NEW.names, OLD.rg.

Пример postgresql trigger

В примере реализована простая система логирования пользователей. Она следит за таблицей пользователей и все изменения регистрирует в таблице журналирования. Для примера будем создавать упрощенные таблицы.

CREATE TABLE "public".users ( id int not null, name varchar (64), constraint pkUsersID primary key (id) );

CREATE TABLE "public".logs ( text varchar(256), data timestamp without time zone );

CREATE OR REPLACE FUNCTION "public".add_to_log() RETURNS TRIGGER AS $$ DECLARE v_action varchar(30); v_user varchar(64); v_retstr varchar(256); BEGIN IF TG_OP = 'INSERT' THEN v_user = NEW.name; v_action := 'Add new user '; v_retstr := v_action || v_user; INSERT INTO "public".logs(text, data) values (v_retstr, NOW()); RETURN NEW; ELSIF TG_OP = 'UPDATE' THEN v_user = NEW.name; v_action := 'Update user '; v_retstr := v_action || v_user; INSERT INTO "public".logs(text, data) values (v_retstr, NOW()); RETURN NEW; ELSIF TG_OP = 'DELETE' THEN v_user = OLD.name; v_action := 'Remove user '; v_retstr := v_action || v_user; INSERT INTO "public".logs(text, data) values (v_retstr, NOW()); RETURN OLD; END IF; END; $$ LANGUAGE plpgsql;

Триггерная функция без входящих параметров возвращает специальный тип TRIGGER. В функции в разделе DECLARE определены 3-и переменные. В теле функции выполняется проверка значения переменной TG_OP (внутренняя переменная триггера). В зависимости от транзакции определяем переменнаю v_user и формируется строка retstr, которая записывается в таблицу logs.

Переменные NEW и OLD — это собственно строки которые обрабатывает триггер. В случае INSERT переменная NEW будет содержать новую строку, а OLD будет пустая. В случае UPDATE обе переменные будут определены (соответствующими данными), а в случае DELETE переменная NEW будет пустая, OLD содержать удаляемую строку.

Сам триггер описывается на PL/pgSQL как :

-- trigger insert & trigger update & trigger delete CREATE TRIGGER trg_user AFTER INSERT OR UPDATE OR DELETE ON "public".users FOR EACH ROW EXECUTE PROCEDURE add_to_log ();

Триггер trg_user будет выполняться после выполнения транзакций INSERT, UPDATE, DELETE для каждой строки и вызывать функцию add_to_log(). Теперь любые действия с таблицей users будут протоколироваться.

Следующие скрипты позволяют проверить работоспособность триггера :

-- Добавление записи в таблицу пользователей insert into users (id, name) values (1, 'Киса Воробьянинов'); -- Обновление записи в таблице пользователей update users set name = 'Остап Бендер' where Чтение пользователей select * from users -- Чтение журнала протоколирования. Должно быть 2 записи select * from logs

Oracle triggers

Синтаксис CREATE TRIGGER в Oracle имеет следующий вид :

-- trigger before CREATE TRIGGER trigger_name BEFORE DELETE OR INSERT OR UPDATE ON table_name REFERENCING FOR EACH ROW WHEN (new.field_name > 0) DECLARE -- переменные, константы, курсоры и т.п. BEGIN -- блок PL/SQL END;

В тексте создания триггера может быть включено необязательное ограничение триггера, путем определения булевского выражения SQL в фразе WHEN. Выражение в фразе WHEN проверяется для каждой строки, затрагиваемой триггером. Если результат выражения ИСТИНА, то тело триггера исполняется. Если выражение ЛОЖЬ или NULL, то тело триггера не исполняется. Выражение в фразе WHEN должно быть выражением SQL, но не выражением PL/SQL, и не может включать подзапрос.

REFERENCING

Опция REFERENCING может использоваться в теле триггера для того, чтобы избежать конфликтов между корреляционными именами и именами таблиц, в случае, если таблица имеет имя «OLD» или «NEW». Такая ситуация редка и эта опция почти никогда не применяется.

В качестве примера можно рассмотреть таблицу с именем new. Следующее определение CREATE TRIGGER показывает триггер, ассоциированный с таблицей new, который использует опцию REFERENCING, чтобы избежать конфликтов между корреляционными именами и именем таблицы:

-- trigger before CREATE TRIGGER trg_dummy BEFORE UPDATE ON new REFERENCING new AS newest FOR EACH ROW BEGIN :newest.field2 := TO_CHAR (:newest.field1); END;

Оператор new переименован в newest с помощью опции REFERENCING, а затем использован в теле триггера.

Условные предикаты

Если триггер может быть вызван на исполнение более чем одним типом предложения DML (например, «INSERT OR DELETE OR UPDATE»), то в теле триггера можно использовать операторы INSERTING, DELETING и UPDATING, для выполнения различных участков кода в зависимости от условия. В коде внутри тела триггера вы можете использовать следующие условия :

IF INSERTING THEN . . . END IF; IF UPDATING THEN . . . END IF;

Первое условие будет выполняться в тех случаях, когда триггер был стартован при вставке строки в таблицу. Второе условие будет выполняться при обновлении строки таблицы.

В операторе UPDATING можно дополнительно использовать условие проверки имени обновляемого столбца. В качестве примера можно рассмотреть следующий код, в котором тело будет исполняться, если предложение UPDATE, возбудившее триггер, обновляет столбец SAL :

IF UPDATING ('SAL') THEN . . . END IF;

Oracle triggers отключение, включение

В Oracle триггер можно временно выключить, если имеет место одно из следующих условий:

объект, к которому обращается триггер, недоступен;
необходимо выполнить массовую загрузку данных без вызова триггеров;
нобходимо загрузить данные в таблицу без вызова триггера.

Триггер по умолчанию включается в момент его создания. Чтобы отключить триггер, необходимо использовать команду ALTER TRIGGER с опцией DISABLE. Чтобы включить триггер, используйте команду ALTER TRIGGER с опцией ENABLE. Можно одновременно отключить все триггеры, ассоциированные с таблицей, с помощью команды ALTER TABLE с опцией DISABLE ALL TRIGGERS.

-- отключение триггера ALTER TRIGGER TRG_Orders_INS DISABLE; -- подключение триггера ALTER TRIGGER TRG_Orders_INS ENABLE; -- отключение всех триггеров таблицы ALTER TABLE Orders DISABLE ALL TRIGGERS;

Для включения или отключения триггера с помощью команды ALTER TABLE, необходимо либо быть владельцем таблицы, либо иметь соответствующую привилегию.

Пример oracle trigger

-- генератор последовательностей CREATE SEQUENCE seqID; -- таблица пользователей CREATE TABLE users ( id int PRIMARY KEY not null, name varchar(50), phone varchar(15), dt date ); -- trigger insert определяет идентификатор записи CREATE OR REPLACE TRIGGER trgAutonumber BEFORE INSERT ON users -- trigger before FOR EACH ROW BEGIN select seqID.NEXTVAL into :new.id from dual; END; -- trigger insert определяет дату записи CREATE OR REPLACE TRIGGER trgDate BEFORE INSERT ON users trigger before FOR EACH ROW BEGIN if :old.dt is null then :new.dt := current_date; end if; END trgDate;

В следующем примере триггер trgDepartmentst_del_cascade выполняет каскадное удаление записей TRIGGER DELETE CASCADE. Триггер, подключенный к таблице departments, реализует ссылочное действие DELETE CASCADE по первичному ключу таблицы deptID:

-- trigger after CREATE OR REPLACE TRIGGER trgDepartmentst_del_cascade AFTER DELETE ON departments FOR EACH ROW BEGIN /* После удаления строки из таблицы Departments удалить из таблицы Employees все строки, имеющие такое же значение deptID. */ DELETE FROM employees WHERE employees.deptID = :old.deptID; END;

Примечание: обычно код для DELETE CASCADE объединяют вместе с кодом для UPDATE SET NULL или UPDATE SET DEFAULT, чтобы учесть как обновления, так и удаления в одном триггере.

Что такое триггер sql

Что такое триггер sql

Что такое триггер sql

2. Типы триггеров

3. Использование условных предикатов

4. Тестирование триггеров

5. Составные (compound) триггеры

6. Создание триггеров системных событий

7. Рекомендации по Разработке Триггеров

Триггеры — спасители

Create Trigger

CREATE TRIGGER

MS SQL trigger

Пример ms sql trigger

PostgreSQL trigger

Синтаксис создания триггера

Синтаксис определения триггерной функции

Пример postgresql trigger

Oracle triggers

Oracle triggers отключение, включение

Пример oracle trigger

Добавить комментарий Отменить ответ