Тема: «Триггеры: создание и применение. Триггеры: создание и применение

Обзор триггеров Триггеры DML-триггеры DDL-триггеры DML-события: Insert, Delete, Update Logon-триггеры DDL-события: Create, Drop, Alter Logon Появились в SQL Server 2005

DML - trigger Объект - таблица, VIEW Событие - insert, update, delete для таблицы и для VIEW. Время активации – до (вместо) или после выполнения оператора.

DML-триггеры Триггер – блок, выполняемый автоматически каждый раз, когда происходит определенное событие – в отличие от процедуры, которая должна быть вызвана явно Событие – INSERT, UPDATE и DELETE для таблицы, представления – для запроса нельзя определить триггер

DML-триггеры Триггер создается по одной таблице базы данных Может осуществлять доступ и к другим таблицам и объектам других баз данных. Триггеры нельзя создать по временным таблицам или системным таблицам, а только по определенным пользователем таблицам или представлениям. Таблица, по которой определяется триггер, называется таблицей триггера.

Когда нужны триггеры Чтобы оценить состояние таблицы до и после изменения данных и предпринять действия на основе этого различия. Несколько DML-триггеров одинакового типа (INSERT, UPDATE или DELETE) для таблицы позволяют предпринять несколько различных действий в ответ на одну инструкцию изменения данных.

Когда нужны триггеры Для каскадных изменений в связанных таблицах БД (если их нельзя выполнить при помощи каскадных ограничений ссылочной целостности). Для предотвращения случайных или неправильных операций INSERT, UPDATE и DELETE Для реализации ограничений целостности, которые нельзя определить при помощи ограничения CHECK. DML-триггеры могут ссылаться на столбцы других таблиц.

Еще… Журнализация и аудит. С помощью триггеров можно отслеживать изменения таблиц, для которых требуется поддержка повышенного уровня безопасности. Данные об изменении таблиц могут сохраняться в других таблицах и включать, например, идентификатор пользователя, время операции обновления; сами обновляемые данные и т. д. Согласование и очистка данных. С любым простым оператором SQL, обновляющим некоторую таблицу, можно связать триггеры, производящие соответствующие обновления других таблиц. Операции, не связанные с изменением базы данных. В триггерах могут выполняться не только операции обновления базы данных. Стандарт SQL позволяет определять хранимые процедуры (которые могут вызываться из триггеров), посылающие электронную почту, печатающие документы и т. д.

Когда не надо использовать триггеры Не нужно реализовывать триггерами возможности, достигаемые использованием декларативных средств СУБД (ограничения целостности или внешние ключи) Избегайте сложных цепочек триггеров

Советы Не используйте триггеры, если можно применить проверочное ограничение CHECK Не используйте ограничение CHECK, если можно обойтись ограничением UNIQUE.

Основные параметры триггера Имя таблицы (или представления) Время срабатывания: AFTER(FOR) или INSTEAD OF Событие: INSERT, UPDATE, DELETE (TRUNCATE TABLE – это не удаление!) Тело триггера! Последовательность срабатывания однотипных триггеров произвольна

Группировка событий Например, вы можете создать триггер, который будет активизироваться, когда происходит выполнение оператора UPDATE или INSERT, и такой триггер мы будем называть триггером UPDATE/INSERT. Вы можете даже создать триггер, который будет активизироваться при возникновении любого из трех событий модификации данных (триггер UPDATE/INSERT/DELETE).

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

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

Пример CREATE TRIGGER trg ON my_table FOR INSERT, UPDATE, DELETE AS select "this is trigger"

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

Выбор типа триггера Триггеры INSTEAD OF используются для: – Выборочного запрещения исполнения команды, для которой определен триггер (проверки предусловия); – Подсчета значений столбцов до завершения команды INSERT или UPDATE. Триггеры AFTER используются для: – Учета выполненных операций; – Проверки пост-условий исполнения команды.

Циклы и вложенность SQL Server позволяет использовать вложенные триггеры, до 32 уровней вложенности. Если любой из вложенных триггеров выполняет операцию ROLLBACK, то последующие триггеры не запускаются. Запуск триггеров отменяется, если формируется бесконечный цикл.

Триггер INSTEAD OF Триггер INSTEAD OF выполняется вместо запуска оператора SQL. Тем самым переопределяется действие запускающего оператора. Можно задать по одному триггеру INSTEAD OF на один оператор INSERT, UPDATE или DELETE. Триггер INSTEAD OF можно задать для таблицы и/или представления Можно использовать каскады триггеров INSTEAD OF, определяя представления поверх представлений, где каждое представление имеет отдельный триггер INSTEAD OF. Триггеры INSTEAD OF не разрешается применять для модифицируемых представлений, содержащих опцию WITH CHECK.

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

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

Порядок AFTER-триггеров sp_settriggerorder @triggername = "Another. Trigger", @order = "first" sp_settriggerorder @triggername = "My. Trigger", @order = "last" sp_settriggerorder @triggername = "My. Other. Trigger", @order = "none" sp_settriggerorder @triggername = "Yet. Another. Trigger", @order = "none"

Использование inserted, deleted Специальные таблицы: inserted – вставленные значения (для INSERT, UPDATE) deleted – удаленные значения (для UPDATE, DELETE)

Использование таблиц deleted и inserted При создании триггера вы имеете доступ к двум временным таблицам с именами deleted и inserted. Они хранятся в памяти, а не на диске. Эти две таблицы имеют одинаковую структуру с таблицей (одинаковые колонки и типы данных), по которой определяется данный триггер.

Использование таблиц deleted и inserted Таблица deleted содержит копии строк, на которые повлиял оператор DELETE или UPDATE. Строки, удаляемые из таблицы данного триггера, перемещаются в таблицу deleted. После этого к данным таблицы deleted можно осуществлять доступ из данного триггера. Таблица inserted содержит копии строк, добавленных к таблице данного триггера при выполнении оператора INSERT или UPDATE. Эти строки добавляются одновременно в таблицу триггера и в таблицу inserted.

Использование таблиц deleted и inserted Поскольку оператор UPDATE обрабатывается как DELETE, после которого следует INSERT, то при использовании оператора UPDATE старые значения строк копируются в таблицу deleted, а новые значения строк – в таблицу триггера и в таблицу inserted. Триггер INSERT => deleted пуст Триггер DELETE => inserted пуст но сообщение об ошибке не возникнет!

Создание триггера CREATE TRIGGER [ schema_name. ]trigger_name ON { table | view } { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { sql_statement}

CREATE TRIGGER plus_1 ON table 1 instead of insert AS insert table 1 (id, col 1) select id+1, col 1 from inserted;

Обработка исключений Команда ROLLBACK указывает серверу остановить обработку модификации и запретить транзакцию. Существует также команда RAISEERROR, с помощью которой вы можете отправить сообщение об ошибке пользователю. TRY…CATCH

Обработка исключений сообщение об ошибке RAISERROR ("Error raised because of wrong data. ", -- Message text. 16, -- Severity. 1 -- State.); Severity – число от 0 до 25 Определенный пользователем уровень серьезности ошибки. 0 до 18 может указать любой пользователь. 19 до 25 могут быть указаны только sysadmin 20 до 25 считаются неустранимыми - соединение с клиентом обрывается и регистрируется сообщение об ошибке в журналах приложений и ошибок. State Целое число от 0 до 255. Отрицательные значения или значения больше 255 приводят к формированию ошибки. Если одна и та же пользовательская ошибка возникает в нескольких местах, то при помощи уникального номера состояния для каждого местоположения можно определить, в каком месте кода появилась ошибка.

Функции об ошибках Функция ERROR_LINE() возвращает номер строки, в которой произошла ошибка. Функция ERROR_MESSAGE() возвращает текст сообщения, которое будет возвращено приложению. Текст содержит значения таких подставляемых параметров, как длина, имена объектов или время. ERROR_NUMBER() возвращает номер ошибки. Функция ERROR_PROCEDURE() возвращает имя хранимой процедуры или триггера, в котором произошла ошибка. Эта функция возвращает значение NULL, если данная ошибка не была совершена внутри хранимой процедуры или триггера. ERROR_SEVERITY() возвращает уровень серьезности ошибки. ERROR_STATE() возвращает состояние.

Пример триггера CREATE TRIGGER Low. Credit ON Purchasing. Purchase. Order. Header AFTER INSERT AS BEGIN DECLARE @creditrating tinyint, @vendorid int ; SELECT @creditrating = v. Credit. Rating, @vendorid = p. Vendor. ID FROM Purchasing. Purchase. Order. Header p JOIN inserted i ON p. Purchase. Order. ID = i. Purchase. Order. ID JOIN Purchasing. Vendor v ON v. Vendor. ID = i. Vendor. ID ; IF @creditrating = 5 RAISERROR ("This vendor""s credit rating is too low to accept new purchase orders. ", 16, 1) ; END

Управление триггерами Отключение/включение триггера: – DISABLE/ENABLE TRIGGER trigger_name ON object_name Отключение/включение всех триггеров таблицы: – DISABLE/ENABLE TRIGGER ALL ON object_name Изменение триггера: – ALTER TRIGGER trigger_name … Удаление триггера: – DROP TRIGGER trigger_name

Активация/деактивация триггера DISABLE TRIGGER {trigger_name [ , . . . n ] | ALL } ON { object_name} ; ENABLE TRIGGER {trigger_name [ , . . . n ] | ALL } ON { object_name}

Применение триггеров Защита – Запрещение доступа в зависимости от значений данных Учет – Ведение журналов изменений Целостность данных – Сложные правила целостности – Сложная ссылочная целостность Производные данные – автоматическое вычисление значений

Типы триггеров Функция Триггер AFTER Триггер INSTEAD OF Сущности Таблицы и представления Количество триггеров на таблицу/представление Несколько на одно событие Один триггер на одно событие Нет ограничений INSTEAD OF UPDATE и DELETE нельзя определять для таблиц, на которые распространяются каскадные ограничения ссылочной целостности. Каскадные ссылки После следующих операций: Обработка ограничений. Выполнение Декларативные ссылочные действия. Создание таблиц inserted и deleted. Действие, запускающее триггер. Перед следующей операцией: Обработка ограничений. Вместо следующей операции: Действие, запускающее триггер. После следующих операций: Создание таблиц inserted и deleted.

DDL - trigger Триггеры DDL могут быть использованы в административных задачах, таких как аудит и регулирование операций базы данных. Действие этих триггеров распространяется на все команды одного типа во всей базе данных или на всем сервере.

DDL - триггеры Триггеры DDL, как и обычные триггеры, вызывают срабатывание хранимых процедур в ответ на событие. Срабатывают в ответ на разнообразные события языка определения данных (DDL). Эти события в основном соответствуют инструкциям языка Transact-SQL, начинающимся ключевыми словами CREATE, ALTER или DROP.

Задачи для DDL - триггеров Предотвратить внесение определенных изменений в схему базы данных. Выполнить в базе данных некоторые действия в ответ на изменения в схеме базы данных. Записывать изменения или события схемы базы данных. Триггеры DDL срабатывают только после выполнения соответствующих инструкций DDL. Триггеры DDL нельзя использовать в качестве триггеров INSTEAD OF.

CREATE TRIGGER trigger_name ON { DATABASE | ALL SERVER } { FOR | AFTER } { event_type | event_group } AS { sql_statement [ ; ] [ , . . . n ] [ ; ] }

Создание/удаление DDL-тр CREATE TRIGGER ddl_trig_database ON ALL SERVER FOR CREATE_DATABASE AS PRINT "Database Created. " DROP TRIGGER ddl_trig_database ON ALL SERVER;

DDL - trigger CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT "You must disable Trigger "safety" to drop or alter tables!" ROLLBACK ;

Для одной инструкции Transact-SQL можно создать несколько триггеров DDL. Триггер DDL и инструкция, приводящая к его срабатыванию, выполняются в одной транзакции. Откат событий ALTER DATABASE, возникших внутри триггера DDL, невозможен. Триггеры DDL выполняются только после завершения инструкции Transact-SQL. Триггеры DDL нельзя использовать в качестве триггеров INSTEAD OF. Триггеры DDL не создают таблицы inserted и deleted.

Logon - trigger Триггеры входа выполняют хранимые процедуры в ответ на событие LOGON. Это событие вызывается при установке пользовательского сеанса с экземпляром SQL Server. Триггеры входа срабатывают после завершения этапа проверки подлинности при входе, но перед тем, как пользовательский сеанс реально устанавливается.

Logon - trigger CREATE TRIGGER trigger_name ON ALL SERVER { FOR| AFTER } LOGON AS { sql_statement }

Триггер – это подпрограмма, похожая на процедуру БД, автоматически вызываемая СУБД при изменении, удалении или добавлении записи в таблице. К триггерам невозможно обратиться из программы, передать им параметры или получить от них результат. Наиболее часто триггеры применяются для поддержания ссылочной целостности и каскадных операций в БД. Ссылочные спецификации, определяющие каскадные действия при удалении и обновлении и создаваемые при объявлении таблиц, также реализуются через триггеры, однако текст этих триггеров не редактируется.

Назначение триггеров

Предотвращение изменения (например, предотвратить счета от изменений после того как они разосланы).
. Журналирование изменения (например, хранить копии старых данных).
. Аудит изменений (например, вести лог пользователей и ролей, участвующих в изменениях).
. Фиксация изменений (например, обеспечить, чтобы все изменения датировались по часам сервера, а не клиента).
. Реализация бизнес-правил.
. Репликация данных (например, хранить записи всех изменений, которые будут отправлены в другой базе данных более поздней версии).
. Повышение производительности (например, обновление сальдо после каждой детали сделки, для ускорения запросов).

Объявление триггеров

CREATE TRIGGER {BEFORE|AFTER } {DELETE|INSERT|UPDATE [OF ]} ON REFERENCING {OLD {[ROW ]|TABLE [AS ] } NEW {ROW|TABLE } [AS ] }] [FOR EACH {STATEMENT|ROW [WHEN ]}]
[BEGIN ATOMIC ]

[END ]

Ключевые слова

. BEFORE|AFTER – время запуска триггера – до | после операции обновления.
. DELETE|INSERT|UPDATE = событие срабатывания триггера.
. FOR EACH ROW – для каждой строки (строчный триггер, тогда и WHEN).
. FOR EACH STATEMENT – для всей команды (действует по умолчанию).
. REFERENCING – позволяет присваивать до 4-х псевдонимов старым и | или новым строкам и | или таблицам, к которым могут обращаться триггера.

Ограничения триггеров

Тело триггера не может содержать операторов:
. Определения, удаления и изменения объектов БД (таблиц, доменов и т.п.)
. Обработки транзакций (COMMIT, ROLLBACK)
. Подключения и отключения к БД (CONNECT, DISCONNECT)

Особенности применения
. Триггер выполняется после применения всех других (декларативны) проверок целостности и целесообразен тогда, когда критерий проверки достаточно сложен. Если декларативные проверки отклоняют операцию обновления, то до выполнения триггеров дело не доходит. Триггер работает в контексте транзакции, а ограничение FK нет.
. Если триггер вызывает дополнительную модификацию своей базовой таблицы, то чаще всего это не приводит к его рекурсивному выполнению, однако это следует уточнять. В СУБД SQL Server 2005 предусмотрена возможность указания рекурсии до 255 уровней с помощью ключевого слова OPTION (MAXRECURSIV 3).
. Триггеры обычно не выполняются при обработке больших двоичных столбцов (BLOB).
. Следует помнить, что всякий раз при обновлении данных СУБД автоматически создает так называемые триггерные виртуальные таблицы, которые в различных СУБД носят разные название. В InterBase и Oracle – Это New и Old. В SQL Server – Inserted и Deleted. Причем при изменении данных создаются обе. Эти таблицы имеют то же количество столбцов, с теми же именами и доменами, что и обновляемая таблица. В СУБД SQL Server 2005 предусмотрена возможность указания таблицы, включая временную, в которую следует вставить данные с помощью ключевого слова OUTPUT Inserted.ID,… INTO @ .
. В ряде СУБД допустимо объявлять триггеры для нескольких действий одновременно. Для реализации разных реакций на различные действия в Oracle предусмотрены предикаты Deleting, Inserting, Updating, возвращающие True для соответствующего вида обновления.
. В СУБД Oracle можно для триггеров Update указать список столбцов (After Update Of), что обеспечит вызов триггера только при изменении значений только этих столбцов.
. Для каждого триггерного события может быть объявлено несколько триггеров (в Oracle 12 триггеров на таблицу) и обычно порядок их запуска определяется порядком создания. В некоторых СУБД, например, InterBase, порядок запуска указывается с помощью дополнительного ключевого слова POSITION . В общем случае считается, что первоначально должны выполняться триггеры для каждой команды, а затем – для каждой строки.
. Триггеры можно встраивать друг в друга. Так SQL Server допускает 32 уровня вложения (с помощью глобальной переменной @@NextLevel можно определить уровень вложения).

Недостатки триггеров

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

Изменение и удаление триггеров

Для удаление триггера используется оператор DROP TRIGGER
. Для изменения триггера используется оператор ALTER TRIGGER …
. Отключение триггеров
В ряде случаев, например, при пакетной загрузке, триггеры требуется отключать. В ряде СУБД предусмотрены соответствующие возможности. В Oracle и SQL Server ключевые слова DISABLE|ENABLE, в InterBase INACTIVE|ACTIVE в операторе ALTER TRIGGER.

Особенности промышленных серверов

1) InterBase/Firebird

CREATE TRIGGER FOR {ACTIVE|INACTIVE } {BEFORE|AFTER } {INSERT|DELETE|UPDATE } [POSITION ]
AS [DECLARE VARIABLE [()]]
BEGIN

END

Пример:

CREATE TRIGGER BF_Del_Cust FOR Customer
ACTIVE BEFORE DELETE POSITION 1 AS
BEGIN
DELETE FROM Orders WHERE Orders.CNum=Customer.CNum;
END;

2) SQL Server

CREATE TRIGGER ON [WITH ENCRYPTION ] {FOR|AFTER|INSTEAD OF } {INSERT|UPDATE|DELETE }
AS

USE B1;
GO
CREATE TRIGGER InUpCust1 ON Customer AFTER INSERT, UPDATE
AS RAISEERROR(‘Изменена таблица Customer’);

Дополнительные виды триггеров

В СУБД Oracle и SQL Server есть возможность создания (замещающих) триггеров для не обновляемых представлений. Для этого предусмотрены ключевые слова INSTEAD OF:

CREATE TRIGGER ON INSTEAD OF INSERT AS …

Можно отслеживать попытки клиента обновлять данные с помощью представлений и выполнять какие-либо действия, обрабатывать не обновляемые представления и т.п.
. В СУБД SQL Server предусмотрен триггер отката, фактически прекращающий все действия с выдачей сообщения:

ROLLBACK TRIGGER

Уже много статей в интернете есть про 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 на страницу (привет, битрикс).

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

Таблица blog

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 ;

Таблица comments

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: Холивар посвященный целесообразности усложнения структуры БД объявляется открытым.

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

Существует три типа триггеров в зависимости от команд, на которые они реагируют:

1) Триггеры на вставку. Запускаются при попытке вставки данных с помощью команды Insert.

2) Триггеры на обновление. Запускаются при попытке изменения данных с помощью команды Update.

3) Триггеры на удаление. Запускаются при попытке удаления данных с помощью команды Delete.

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

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

CREATE TRIGGER trigger_name

sql_statement [...n]

Второй вариант данной команды:

CREATE TRIGGER trigger_name

{FOR { [ [,] }

{ IF UPDATE (column)

UPDATE(column) ]

sql_statement [...n]

Рассмотрим работу первого варианта работы команды Create trigger.

Trigger_name – задает имя триггера, с помощью которого он будет распознаваться хранимыми процедурами и командами Transact SQL. Имя триггера должно быть уникальным в пределах БД.

TABLE – имя таблицы БД, к которой будет привязан триггер.

WITH ENCRYPTION – при указании этой опции сервер выполняет шифрование кода триггера.

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

WITH APPEND – указание этого ключевого слова требуется для обеспечения совместимости с более ранними версиями SQL-сервер.

NOT FOR REPLICATION – запрещает запуск триггера при модификации таблиц с помощью репликации.

sql_statement – определяет набор команд, которые будут выполняться при запуске триггера.

Рассмотрим использование второго способа:

FOR { [ [,] – эта инструкция определяет, при выполнении какой команды будет запускаться триггер.

IF UPDATE (column) – использование этого параметра позволяет выполнить триггер при модификации конкретной колонки таблицы.

AND/OR UPDATE(column) – применение совпадает с предыдущим параметром, если необходимо выполнить запуск триггера при модификации нескольких колонок. Аргумент column задает имя колонки, при модификации которой будет производиться запуск триггера. Ключевое слово AND предписывает запускать триггер только в том случае, если были модифицированы обе колонки, указанные в этой и в предыдущей конструкции. При использовании ключевого слова OR триггер будет выполнен при выполнении изменений в любой из колонок. Допускается использование нескольких конструкций AND/OR UPDATE(column).

Для изменения триггера используется команда ALTER TRIGGER:

ALTER TRIGGER trigger_name

{FOR { [,] [,] }

sql_statement [...n]

Перед тем как выполнить команду ALTER TRIGGER следует убедиться в существовании в таблице table триггера.

Для удаления триггера используется команда DROP TRIGGER:

DROP TRIGGER {trigger} [..n]

Единственный аргумент trigger обозначает имя триггера. С помощью одной команды DROP TRIGGER можно удалить несколько триггеров.