Sql триггер на добавление записи. Триггеры, объявление и назначения триггеров в SQL. Oracle triggers отключение, включение
Вопрос: Нужен триггер, который будет обновлять одну таблицу при добавлении записи в другую
Нужен триггер, который срабатывает после того как добавлется запись в таблицу.
Итак, имеется:
Таблица Товар
Таблица Производитель
они связаны между собой, в таблице Товар я выбираю производителя по айди из таблицы Производитель
Необходимо чтобы при добавлении записи в таблицу Товар, триггер находил производителя который в этом товаре и в таблице Производитель добавлял +1 в столбец kol_tov
Я вообще не понимаю как это все организовать(
Ответ: При ДОБАВЛЕНИИ (!) записи в таблицу "Товар" такой триггер добавит +1 в таблицу "Производитель":
| Код T-SQL | |
Конечно, здесь не учитывается, что запись может еще и редактироваться или удаляться.
PS.
Здесь я подразумеваю наличие поля "ПроизводительID" в обеих таблицах.
Добавлено через 10 минут
Неточность у меня... Новый триггер не "ALTER TRIGGER..." а "CREATE TRIGGER..." Прошу прощения.
Вопрос: Как в триггере добавить запись сразу в две таблицы?
Здравствуйте!! Как при помощи триггера добавить запись сразу в две таблицы?
есть таблица Students и TableForTriggers, структура таблицы TableForTriggers такая же как у таблицы Students, единственное отличие в нем есть еще один столбец в котором показывает значение действия для записи-insert,update,delete.
вот мой триггер для добавления записи только в одну таблицу:
и еще триггер не сохраняется, что с этим делать?
Добавлено через 40 минут
С сохранением триггера все решил
Ответ:
Сообщение от Бекболот
структура таблицы TableForTriggers такая же как у таблицы Students, единственное отличие в нем есть еще один столбец в котором показывает значение действия для записи-insert,update,delete.
Или задание дурацкое, или ты не до конца понял условие, в той таблице нужное свое автоинкрементное поле, а Id, что в Students должен быть обычным атрибутом, а то после update в Students, а затем нового update, возвращающего прежнее название, ты получишь совершенно идентичные записи в TableForTriggers, а это очень неправильно. К тому же совершенно непонятен будет порядок updat-ов одной и той же записи в Students и какой либо смысл от таблицы TableForTriggers пропадет вовсе
Вопрос: Создание формы множественного добавления записей в таблицу
доброго дня, Уважаемые форумчане.
Возможно вопрос кому-то покажется похожим на другие и банальным, но не знаю с какой стороны к нему подступиться.
в аксессе не силен
Общее описание: база данных на ранней стадии развития, содержащая 3 таблицы:
1. транспортные средства - содержит поля с информацией по машинам
2. бортовое оборудование - содержит поля с информацией по бортовому оборудованию
3. наработка транспорта.
таблица "Наработка" содержит поля: год, месяц, гос№ТС, пробег, расход и т.д.
заполнить эту таблицу можно, в простейших вариантах, либо вручную непосредственно в самой таблице либо создав простую форму на основе этой таблицы и заполнять по одной записи.
Перекопал форумы и не нашел даже направления, в котором надо копать.
Вопрос такой: можно ли создать форму множественного добавления записей?
Т.е. пользователь, открыв форму, сверху видит поля: "ГОД" и "МЕСЯЦ". По умолчанию им присваиваются значения соответственно года и месяца текущей даты.
Под этими полями должна вывестись таблица со списком транспорта (из таблицы "Транспортные средства") и к каждому из этих транспортных средств в этой таблице должны быть поля о пробеге, расходе и т.д., т.е пустые, готовые к заполнению поля с информацией о наработке.
Пользователь выставляет нужный месяц, заполняет данные о наработке к каждому из транспортных средств и после заполнения этих данных в таблице "наработка" должны создаться соответствующие записи, у которых год и месяц взяты из верхних отдельных полей (с выпадающими списками), а данные по наработке из таблицы соответственно для каждой из машин.
В данный момент не рассматривается вопрос организации проверки наличия записей в базе за выбранный период - просто добавляется или накладывается молча на старую.
ps. делать за меня не надо - только пните в нужную сторону
Ответ:
Сообщение от xopek160183
Можно ли как то задать такое же условие отбора, чтобы выполняли оба запроса при однократном вводе значения отбора?
Да, использовать поле со списком на форме для отбора, а в запросе ссылаться на это поле.
Например:
| Код T-SQL | ||
|
||
Вопрос: Добавление записи в таблицу на основе существующей записи
Здравствуйте!
PHP и MySQL только начинаю изучать и естественно не все получается.
У меня есть таблица с пользователями USERS, в ней поля 1) ID (пк, аи), 2)еще всякие поля, 3) поле PAGE, куда записывается путь к странице пользователя.
Я хочу, чтобы при добавлении записи в таблицу, мной заполнялись все поля кроме ID и PAGE и чтоб значение поля PAGE формировалось на основе поля ID. Т.е., например ID=1, ..., PAGE=/userpage.php?id=1.
Понимаю, что для этого надо сделать триггер, но он у меня выдает ошибку (Can"t update table "users" in stored function/trigger because it is already used by statement which invoked this stored function/trigger.) при добавлении строки и конечно не работает.
Тк все остальное сам подставляет php my admin.
Спасибо тем кто поможет исправить ошибку.
Версии - PHP 5.5, MySQL 5.5.
Ответ: Интересно, а как же поле ID не будет заполняться, когда на его основе формируется поле PAGE? Значит значение ID нужно каким-то образом формировать и где-то сохранять.
Вопрос: Запуск запроса на добавление записей
Форумчане,доброго времени суток!
Подскажите, пожалуйста, как мне запустить запрос на добавление записей в таблицу.
При таком коде:
| Код SQL | ||
|
||
Ругается два раза:
1.Ядро БД не может найти входную таблицу или запрос..
2.Не удается добавить все записи...
Что не удается добавить все записи- пусть! Не нужно при этом выводить информационное сообщение (не удается добавить все записи...)
При таком коде:
| Код SQL | ||
|
||
ругается 1 раз (не удается добавить все записи...)
Подскажите, как мне запустить запрос?
Ответ:
mobile
, Спасибо!
поясню подробнее: изначально в таблицу1 заносятся данные о каждой единице упаковки комплектующих (и наклеивается на нее индивидуальный штрихкод)
Затем каждая из этих упаковок ставится на "Приход"- т.е копируется в таблицу2 (вышеописанным запросом).В этой таблице сделано уникальное поле (код_таблицы1)
т.е. записи которые уже скопировались в таблицу2 уже невозможно скопировать из таблицы1 при последующих копирования.
Решение плохое, лучше если будут копироваться вновь введенные (в таблицу1) записи, находящиеся в Форме1
Что-то с налету не получилось- это надо создать модуль, сделать функцию этого модуля условием отбора в запросе.... (т.е то что мне советовали, да и создавали соответствующие процедуры, в том числе и Вы). Сейчас не выходит, буду пробовать так позднее.
Вопрос: Можно ли создать триггер на репликацию транзакций?
Настроил транзакционную репликацию.
Проверил: тригер FOR INSERT для таблицы подписчика при добавлении записи через эту репликацию не срабатывает.
Вопрос: можно ли на подписчике отследить вставку новой записи в таблицу через транзакционную репликацию?
Что то вроде:
create
trigger
for
insert
FOR
replication
- как сделать?
Ответ:
И правда - убрал триггер на паблишере и все заработало на подписчике как надо. - Спасибо!
Одного всё таки не понял:
почему при двух триггерах, значение N добавленое на паблишере увеличивалось
по приходу на подписчик на 1 а не на 2
Вопрос: Триггер на изменение значения поля при добавлении записи в другую таблицу MySQL
Работаю в phpMyAdmin
Ребят, такая проблема. Не понимаю в чем дело, т.к. я чайник в MySQL.
В общем, мне нужно изменить значение в столбце `Количество книг` в таблице `книги`, когда в таблицу `продажа` добавляется новая запись о продаже - сделать все с помощью ТРИГГЕРА
CREATE TABLE IF NOT EXISTS `книги` (
`Автор` varchar(100) NOT NULL,
`Название` varchar(100) NOT NULL,
`Год издания` date NOT NULL,
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Издательство` varchar(100) NOT NULL,
`Количество книг` int(11) NOT NULL,
`Цена` int(11) NOT NULL,
PRIMARY KEY (`ID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
INSERT INTO `книги` (`Автор`, `Название`, `Год издания`, `ID`, `Издательство`, `Количество книг`, `Цена`) VALUES("Феоктистов Леонид", "Большая энциклопедия астрономии", "2009-01-01", 2, "Росмэн-Пресс", 10, 310);
INSERT INTO `книги` (`Автор`, `Название`, `Год издания`, `ID`, `Издательство`, `Количество книг`, `Цена`) VALUES("Иар Эльтеррус", "Отзвуки серебряного ветра Мы - будем", "2009-01-01", 3, "Альфа - книга", 10, 735);
INSERT INTO `книги` (`Автор`, `Название`, `Год издания`, `ID`, `Издательство`, `Количество книг`, `Цена`) VALUES("Лев Николаевич Толстой", "Война и мир", "1996-01-01", 4, "Лексика", 10, 1300);
INSERT INTO `книги` (`Автор`, `Название`, `Год издания`, `ID`, `Издательство`, `Количество книг`, `Цена`) VALUES("Михаил Афанасьевич Булгаков", "Мастер и Маргарита", "2009-01-01", 5, "АСТ", 10, 185);
CREATE TABLE IF NOT EXISTS `продажа` (
`КнигиID` int(11) NOT NULL,
`ПродавцыНомер трудовой книги` int(11) NOT NULL,
`Дата продажи` date NOT NULL,
`Номер сделки` int(11) NOT NULL AUTO_INCREMENT,
`Сумма` int(11) NOT NULL,
PRIMARY KEY (`Номер сделки`),
KEY `КнигиID` (`КнигиID`),
KEY `ПродавцыНомер трудовой книги` (`ПродавцыНомер трудовой книги`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
INSERT INTO `продажа` (`КнигиID`, `ПродавцыНомер трудовой книги`, `Дата продажи`, `Номер сделки`) VALUES(3, 70113047, "2016-01-01", "");
DELIMITER |
FOR EACH ROW
BEGIN
UPDATE `manual1`.`книги`
WHERE `книги`.`ID` = `продажа`.`КнигиID`;
END;
Если делаю так, что при добавлении записи в таблицу `продажа` пишет ошибку "#1054 - Unknown column "продажа.КнигиID" in "where clause""
-----
DELIMITER |
CREATE TRIGGER `update_knigi` AFTER INSERT ON `manual1`.`продажа`
FOR EACH ROW
BEGIN
UPDATE `manual1`.`книги`
SET `Количество книг` = (`Количество книг` - 1)
WHERE `книги`.`ID`;
END;
Если так, то значения отнимаются у всех значений `Количество книг`.
Не знаю что делать, пожалуйста, помогите:с
Ответ:
Зачем так много лишней информации? Вопрос изложен понятно.
В триггере befor_insert таблицы продажа выполнить запрос:
UPDATE таблица_книги SET количество_книг = количество_книг - NEW.количество_проданных_книг WHERE таблица_книги.код_книги = NEW.код_книги
Вопрос: Обновление поля со списком после добавления записи в таблицу
Здравствуйте!
Подскажите, как сдлать чтобы после добавления записи в таблицу даные в поле со списком обновлялись. я добавляю запись а в поле со списком не появляется. вернее, если первый раз добавлю, то первая запись почему то появляется в поле со списком, а если второй раз добавлю, то вторая запись не появляетсяя. и так каждый раз после того как я зарою базу и снова открою.
Ответ:
С макросами дела не имею, не подскажу. А вот на ВБА будет так - смотрите код на процедуре кнопки в форме Добавление
Вопрос: значение по умолчанию - время добавления записи
если ставишь в поле значение по умолчанию Now() то ставится время добавления предыдущей записи, т.е. значение не обновляется при добавлении записи. Если повесить макрос на изменение в поле код, то ничего не происходит. Как быть?
Ответ: 14,
ну не знаю, "проще" у каждого своё, зависит о конкретики задачи, потому всё очень относительно.
У меня в программе пользователь, например, сидит в реестре расходных накладных. Пришёл покупатель - кассир тискает кнопку "Создать накладную", и у него открывается окно с новой накладной, в которой автоматом вставляется время создания.
Вопрос: Нумерация в запросе и выборка записей по условию нумерации
Здравствуйте! Не могу решить проблему. Есть запрос в котором записи выбираются по некоторому условию. Необходимо чтобы в этом запросе была своя независимая нумерация строк т.е. 1 2 3 4 5 6 и т.д. в зависимости от количества записей в запросе.
А также необходимо чтобы отбирались только первые 3-ри записи поэтому условию.
Ответ:
Спасибо большое! За решение. Теперь как выбрать первые 3 записи из запроса вы мне подсказали.
Основная задача состояла в выборе первых трех записей в запросе. Создания поля с нумерацией необязательно, мне необходимо было выбрать первые три записи из запроса по условию. Я предполагал что если задать условие в поле нумерации о выборе записей <4 я выберу первые три записи в запросе, а вы мне подсказали как эту проблему решить гораздо проще SELECT top 3 * спасибо.
Создание генераторов
Генератор – это хранящаяся в БД программа, выдающая при каждом обращении к ней уникальное число.
Создание генератора:
CREATE GENERATOR <Имя генератора>
Начальное значение задается инструкцией:
SET GENERATOR <Имя генератора> TO <Начальное значение (целое число)>
CREATE GENERATOR GenStore
SET GENERATOR GenStore TO 1
Обращение к созданному генератору выполняется с помощью функции
GEN_ID (<Имя генератора>, <Шаг>)
Триггер – это процедура, которая находится на сервере БД и вызывается автоматически при модификации записей БД, т.е. при изменении столбцов или при их удалении и добавлении. В отличие от хранимых процедур, триггеры нельзя вызывать из приложения клиента, а также передавать им параметры и получать от них результаты.
Создание триггера:
CREATE TRIGGER <> FOR <>
{BEFORE | AFTER}
{UPDATE | INSERT | DELETE}
AS <Тело триггера>
Описатели ACTIVE | INACTIVE определяют активность триггера сразу после его создания. По умолчанию действует ACTIVE.
Описатели BEFORE | AFTER задают момент начала выполнения триггера до или после наступления соответствующего события, связанного с изменением записей.
Описатели UPDATE | INSERT | DELETE определяют, при наступлении какого события вызывается триггер – при редактировании, добавлении или удалении записей.
Для одного события можно создать несколько триггеров, каждый из которых будет автоматически выполнен (если находится в активном состоянии). При наличии нескольких триггеров порядок их вызова (выполнения) определяет число, указанное в операнде POSITION. Триггеры выполняются в порядке возрастания этих чисел.
Удаление триггера:
DROP TRIGGER <Имя триггера>
Изменение триггера:
Для доступа к значениям столбца используются инструкции формата:
OLD.<Имя столбца> - обращается к старому (до внесения изменений) значению столбца,
NEW.<Имя столбца> - обращается к новому (после внесения изменений) значению столбца.
Создание триггера для занесения в ключевой столбец уникальных значений
CREATE TABLE Store
(S_Code INTEGER NOT NULL ,
PRIMARY KEY (S_Code));
CREATE GENERATOR GenStore
SET GENERATOR GenStore TO 1
CREATE TRIGGER CodeStore FOR Store
NEW.S_Code = GEN_ID (GenStore, 1);
При добавлении к таблице Store новой записи ключевому столбцу S_Code этой записи автоматически присваивается уникальное значение. Это обеспечивается обращением GEN_ID к генератору GenStore.
Реализация каскадного удаления записей с участием триггера
CREATE TABLE Store
(S_Code INTEGER NOT NULL ,
PRIMARY KEY (S_Code));
CREATE TABLE Cards
(C_Code INTEGER NOT NULL,
C_Code2 INTEGER NOT NULL,
PRIMARY KEY (C_Code));
CREATE TRIGGER DeleteStore FOR Store
DELETE FROM Cards WHERE Store.S_Code = Cards.C_Code2;
После удаления записи в таблице Store буду автоматически удалены все соответствующие записи в таблице Cards.
Замечание : для таблиц не должны действовать ограничения ссылочной целостности, заданные на физическом уровне.
Обновление столбцов связи (ключевых столбцов) связанных таблиц, заключающееся в том, что при изменении значения столбца связи главной таблицы соответственно изменяются значения столбца связи всех связанных записей подчиненной таблицы.
CREATE TRIGGER ChangeStore FOR Store
IF (OLD.S_Code <> NEW.S_Code)
THEN UPDATE Cards
SET C_Code2 = NEW.S_Code
WHERE C_Code2 = OLD.S_Code;
При изменении столбца S_Code, используемого для связи главной таблицы Store с подчиненной таблицей Cards, автоматически изменяются значения столбца связи C_Code2 соответствующих записей подчиненной таблицы.
Чтобы столбец главной таблицы можно было редактировать, по нему не должен быть создан ключ.
Триггером называют процедуру, которая хранится на сервере в откомпилированном виде и выполняется автоматически при наступлении события добавления, редактирования или удаления записи независимо от желания клиента. Принудительно запустить триггер из приложения клиента нельзя. Триггеру нельзя передать входные параметры и получить от него выходные параметры.
Триггер всегда работает с текущей записью и реализует конкретное действие.
Триггеры могут выполняться до наступления события (параметр BEFORE) или после наступления события (параметр AFTER).
Триггеры различают по направлению действия:
- INSERT - на добавление записи;
- UPDATE - на редактирование записи;
- DELETE - на удаление записи.
Триггер создается для конкретной таблицы и принадлежит ей. Если таблица имеет несколько триггеров одного направления действия, то время их срабатывания определяется в первую очередь параметрами BEFORE и AFTER , а при одинаковом значении параметра наступления события - параметром POSITION с указанием номера (порядка) срабатывания триггера.
При работе с триггерами следует иметь в виду, что:
- при откате транзакций откатываются все изменения, сделанные триггерами;
- легко реализуются каскадные изменения и каскадные удаления в дочерних таблицах;
- изменения, внесенные в тело триггера, автоматически работают для каждого приложения клиента, т. е. нет необходимости сделанные изменения доводить до каждого пользователя.
Создание триггера производится по правилам создания хранимых процедур, хотя имеются некоторые особенности.
Создание триггера
Для создания триггера используется оператор CREATE TRIGGER.
Формат оператора
CREATE TRIGGER FOR
[ ACTIVE | INACTIVE ]
[ BEFORE |AFTER]
[ INSERT | UPDATE DELETE ]
[ POSITION ]
Назначение опций:
ACTIVE - триггер активен, т. е. при обращении к указанной таблице выполняется процедура, записанная в стеле триггерам
INACTIVE - триггер пассивен, т. е. триггер создан и хранится на сервере, но при обращении к указанной таблице стело триггера> не выполняется;
BEFORE - до наступления события;
AFTER - определяет время срабатывания после наступления события;
INSERT - определяет для триггера событие добавления записи в таблицу;
UPDATE - определяет для триггера событие редактирования записи в таблице;
DELETE - определяет для триггера событие удаления записи из таблицы;
POSITION - определяет номер (позицию) срабатывания триггера внутри определенного события срабатывания (BEFORE или AFTER).
Для описания процедуры стело триггера> используются те же операторы и конструкции, которые используются при создании хранимой процедуры. В заголовке триггера определяют его активность, событие срабатывания, действие, на которое он реагирует, и, при необходимости, позицию срабатывания триггера.
При написании стела триггера> дополнительно можно использовать ключевые слова OLD (до события) и NEW (после события) с последующим указанием имени поля.
Так как в удаленной базе данных все изменения в таблицах (добавление, редактирование и удаление записи) производятся в выборках (в оперативной памяти), то, например, при изменении значения поля можно обратиться как к старому (до изменения) значению поля - ОЬО.симя поля>, так и к новому (после изменения) значению поля - NEW-симя полях Если в указанное поле изменение не вносилось, то ОЬО.симя поля> будет равно NEW.chmb полях
Пример 6.10. Создание триггера.
CREATE TRIGGER T_COMPCODE FOR COMPOSERS
ACTIVE BEFORE INSERT POSITION 0
new.code_composer=gen_id(g_composers, 1); if(COMPOSERS.data born is not null) then begin
new.actuallyage = (CAST("NOW" AS DATE)-COMPOSERS.data_born)/365; if(COMPOSERS.data day is not null) then COM POSERS.age =(COMPOSERS.data_day-COMPOSERS. data born) /365; else new.age=null; end
Триггер срабатывает до наступления события добавления новой записи и вычисляет возраст композитора. Вычисленный возраст записывает в поле «actually_age ».
Изменение триггера
Для изменения триггера используется команда ALTER TRIGGER , имеющая аналогичный формат и аналогичный принцип работы, что и команда ALTER PROCEDURE для изменения тела хранимой процедуры.
Формат команды
ALTER TRIGGER FOR [ ACTIVE I INACTIVE
BEFORE I AFTER
INSERT I UPDATE DELETE
[ POSITION 1
После выполнения оператора ALTER TRIGGER старое определение триггера заменяется новым определением. Старое определение триггера восстановить нельзя.
Пример 6.11. Редактирование триггера.
ALTER TRIGGER T_COMPCODE FOR COMPOSERS ACTIVE BEFORE INSERT POSITION 5 AS
new.code_composer=gen_id(g_composers, 1); if(COMPOSERS.data_born is not null) then begin
new.actually_age = (CAST("NOW" AS DATE)-COMPOSERS.data_born)/365; if(COM POSERS.dataday is not null) then COM POSERS.age =
(COMPOSERS.data_day-COMPOSERS.data born) /365; else new.age=null; end
else begin new.age=null; new.actually_age=null; end end
В триггер, созданный в примере 6.10, внесено изменение: задана новая позиция (POSITION ) срабатывания триггера - 5.
Удаление триггера
Для удаления триггера используют команду
Пример 6.12. Удаление триггера.
DROP TRIGGER T_COMPCODE
Восстановить удаленный триггер нельзя.
Использование триггера в каскадных воздействиях
Как отмечалось выше, предусмотрено два вида каскадных воздействий: каскадное изменение и каскадное удаление. Поэтому для каждой родительской таблицы надо предусмотреть два триггера: один - для каскадного изменения, второй - для каскадного удаления. При этом условия ссылочной целостности должны быть удалены.
Пример 6.13. Каскадное удаление записей.
При удалении фамилии из родительской таблицы ГАМ необходимо удалить соответствующие фамилии (по ключам фами-
лии) во всех дочерних таблицах (в примере таблицы AUTHOR и BOOK).
CREATE TRIGGER DEL FAM FOR FAM ACTIVE
DELETE FROM AUTHOR
WHERE FAM.KEYFAM = AUTHOR. KEYFAM; DELETE FROM BOOK
WHERE FAM.KEY FAM = BOOK.KEY FAM;
Пример 6.14. Каскадное редактирование записей.
При изменении значения ключевого поля (KEY FAM) в родительской таблице FAM необходимо изменить соответствующие значения внешних ключей во всех дочерних таблицах (в примере таблицы AUTHOR и BOOK).
CREATE TRIGGER UPD FAM FOR FAM ACTIVE
BEFORE UPDATE AS
IF (OLD.KEYFAM NEW.KEY FAM) THEN BEGIN
UPDATE AUTHOR SET KEY FAM = NEW.KEY FAM
WHERE KEY FAM - OLD.KEY FAM; UPDATE BOOK SET KEYFAM - N EW. KEYFAM
WHERE KEY FAM = OLD.KEY FAM;
Особенности использования каскадных воздействий:
- при откате транзакций все изменения, сделанные триггерами, возвращаются к исходным значениям;
- в одном триггере допускается обращение к нескольким таблицам;
- триггер может обращаться только к текущей записи таблицы, т. е. запрещен переход к другим записям таблицы.
Обеспечение достоверности данных с помощью триггера
Так как одна таблица может иметь несколько триггеров на одно событие (добавление, редактирование или удаление записи), то имеется возможность обеспечить достоверность вводимых в таблицу данных. Для создания триггера по обеспечению достоверности данных дополнительно используют операторы и ключевые слова.
а. Обеспечение уникальности значения поля
Как правило, для этих целей используют генератор. Работу с генераторами см. п. 6.3. Предварительно создается генератор, а затем имя генератора указывают в теле триггера.
Пример 6.15. Заполнение поля первичного ключа.
Написать триггер по добавлению уникального значения первичного ключа KEYFAM. Генератор GFAM уже создан.
CREATE TRIGGER К РАМ FOR FAM
N EW. KEYFAM = GEN_ID(G_FAM, 1);
Пример 6.16. Заполнение информационного поля.
CREATE TRIGGER TCOMPDATE FOR COMPOSERS
ACTIVE BEFORE UPDATE POSITION 0
if(COMPOSERS.data born is not null) then begin
new.actually_age = (CAST("NOW" AS
DATE)-COMPOSERS.data_born)/365; if(COMPOSERS.data day is not null) then COM POSERS.age =
(COM POSERS.data_day-COMPOSERS.data_born)/365; else new.age=null; end
else begin new.age=null; new.actually_age=null; end
В данном примере вычисляется возраст человека и заполняется поле actually age.
Ведение журнала аудита с помощью триггера
В удаленных базах данных особый интерес представляет ведение журнала изменений таблиц базы данных с целью определения источника недостоверных данных. При ведении журнала изменений в специальной таблице фиксируется:
- выполненное действие над таблицей;
- новое значение поля;
- старое значение поля;
- дата внесения изменения;
- фамилия, имя и отчество пользователя (USER NAME );
- номер (имя) рабочей станции.
Пример 6.17. Автоматическое заполнение журнала аудита.
CREATE TRIGGER AFTJNS_DOGS FOR DOGS ACTIVE AFTER INSERT POSITION 0 AS begin
insert into log (act, table_name,record_id) values("INSERT","DOGS",DOGS. ID); end
CREATE TRIGGER AFT_UPD_DOGS FOR DOGS ACTIVE AFTER UPDATE POSITION 0 AS begin
insert into log (act,table_name,record_id) values(’UPDATE’,"DOGS’,DOGS.ID); end
CREATE TRIGGER AFT DEL DOGS FOR DOGS ACTIVE AFTER DELETE POSITION 0 AS
insert into log (act,table_name,record_id) values(’DELETE","DOGS’,DOGS.ID); end
В этом примере для таблицы DOGS созданы три триггера (по одному на каждое событие INSERT, UPDATE и DELETE). Каждый из триггеров добавляет в таблицу аудита log одну строку, которая содержит поля «выполненное действие», «имя таблицы» и «номер записи». При желании количество полей в таблице аудита можно увеличить.
Три́ггер (trigger ) - это хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено наступлением определенного события (действием) - по сути добавлением INSERT или удалением DELETE строки в заданной таблице, или модификации UPDATE данных в определенном столбце заданной таблицы реляционной базы данных. Триггеры применяются для обеспечения целостности данных и реализации сложной бизнес-логики. Триггер запускается сервером автоматически при попытке изменения данных в таблице, с которой он связан. Все производимые им модификации данных рассматриваются как выполняемые в транзакции, в которой выполнено действие, вызвавшее срабатывание триггера. Соответственно, в случае обнаружения ошибки или нарушения целостности данных может произойти откат этой транзакции.
Триггеры создаются отдельно для каждой таблицы и располагаются в обозревателе объектов в папке «Triggers». Создадим триггеры для таблицы «teachers». Папка «Triggers» будет входить в состав таблицы «teachers»:
Создадим триггер, выводящий сообщение «Запись добавлена» при добавлении записи в таблицу. Создадим новый триггер, щёлкнув ПКМ по папке «Triggers» в таблице «teachers» и в появившемся меню выбрав пункт «New Trigger». Появится следующее окно с новым триггером:
Рассмотрим структуру триггеров:
1) Область определения имени функции (Trigger_Name);
2) Область, показывающая для какой таблицы создаётся триггер (Table_Name);
3) Область, показывающая когда выполнять триггер (INSERT – при создании записи в таблице, DELETE – при удалении и UPDATE – при изменении) и как его выполнять (ALTER – после выполнения операции, INSTEAD OF – вместо выполнения операции);
4) Тело триггера, содержит команды языка программирования запросов TSQL.
В окне нового триггера наберем код:
CREATE TRIGGER tr_add
ON dbo.teachers
PRINT "Новая запись добавлена"
Из рисунка видно, что создаваемый триггер «Индикатор добавления» выполняется после добавления записи (AFTER INSERT) в таблицу «dbo.teachers» (ON dbo.teachers). После добавления записи триггер выведет на экран сообщение "Новая запись добавлена" (PRINT "Новая запись добавлена"). Выполним набранный код, нажав кнопку на панели инструментов. В нижней части окна с кодом появиться сообщение «Command(s) completed successfully.».
Проверим, как работает новый триггер. Создадимновый пустой запрос и в нём наберем следующую команду для добавления новой записи в таблицу «dbo.teachers»:
insert into dbo.teachers
"Сидоров"
Выполним набранную команду, нажав кнопку на панели инструментов. В таблицу будет добавлена новая запись, и триггер выведет сообщение «Новая запись добавлена»
Аналогичным образом создаются триггеры с UPDATE и DELETE.
Задание: в соответствии со своей темой создайте в SQL Server Management Studio представления (3-4), запросы(3-4), хранимые процедуры(3-4), пользовательские функции(3-4), триггеры (INSERT, UPDATE и DELETE ). Используйте при создании операторы условия и циклы.
В SQL Server существуют два вида триггеров:
Триггеры выполняемые после события, произошедшего с таблицей (Полный аналог процедур событий в Visual Basic);
Триггеры выполняемые вместо события, происходящего с таблицей. В этом случае событие (добавление, изменение илиудаление записей ) не выполняется, а вместо него выполняются SQL команды заданные внутри триггера.
Первый вид триггеров применяется для обработки событий таблиц, а второй - для обеспечения целостности данных , то естьудаление записей из вторичной таблицы при удалении связанной с ними записи из первичной таблицы.
Замечание : Триггеры создаются для конкретной таблицы и выполняются автоматически, если с таблицей, для которой они были созданы, происходит событие (добавление, изменение или удаление записей ).
Для создания триггера на вкладке нового запроса необходимо набрать команду CREATE TRIGGER , имеющую следующийсинтаксис :
CREATE TRIGGER <Имя триггера>
ON <Имя таблицы>
FOR
AS <Команды SQL>
Имя триггера - это имя создаваемого триггера.
Имя таблицы - имя таблицы , для которой создаётся триггер.
Если используется параметр AFTER, то триггер выполняется после события, а если параметр INSTEAD OF, то выполняется вместо события.
Параметры INSERT, UPDATE и DELETE определяют событие, при котором (или вместо которого) выполняется триггер.
Параметр WITH ENCRYPTION - предназначен для включения шифрования данных при выполнении триггера .
Команды SQL - это SQL команды, выполняемые при активизации триггера.
Рассмотрим примеры создания различных триггеров для таблицы "Студенты" .
Пример : Создаёт триггер "Добавление" , выводящий на экран сообщение "Запись добавлена" при добавлении новой записи в таблицу "Студенты"
CREATE TRIGGER Добавление
ON Студенты
FOR AFTER INSERT
AS PRINT "Запись добавлена"
Пример : Создаёт триггер "Изменение" "Запись изменена" при изменении записи в таблице"Студенты"
CREATE TRIGGER Изменение
ON Студенты
FOR AFTER UPDATE
AS PRINT "Запись изменена"
Пример : Создаёт триггер "Удаление" , выводящий на экран с сообщение "Запись удалена" при удалении записи из таблицы"Студенты"
CREATE TRIGGER Удаление
ON Студенты
FOR AFTER DELETE
AS PRINT "Запись удалена"
Пример : В данном примере вместо удаления студента из таблицы "Студенты" выполняется код между BEGIN и END. Он состоит из двух команд DELETE. Первая команда удаляет все записи из таблицы "Оценки" , которые связаны с записями из таблицы "Студенты" . То есть у которых Оценки.[Код студента] равен коду удаляемого студента. Затем из таблицы"Студенты" удаляется сам студент.
CREATE TRIGGER УдалениеСтудента
ON Студенты
INSTEAD OF DELETE
DELETE Оценки
WHERE deleted.[Код студента]=Оценки.[Код студента]
DELETE Студенты
WHERE deleted.[Код студента]=Студенты.[Код студента]
Замечание : Здесь удаляемая запись обозначается служебным словом deleted.
Замечание : Для обеспечения целостности данных триггеры используют обычно вместе с диаграммами, но мы можем применять такие триггеры и без диаграмм, однако мы не можем применять диаграммы без триггеров.
