ГлавнаяРазноеSql триггеры примеры

Образовательный блог — всё для учебы. Sql триггеры примеры


Пример использования триггеров SQL для поддержания ссылочной целостности

Ниже написанные триггеры сделаны для базы данных, которая была опубликована ранее (см. Пример создания базы данных из нескольких таблиц на SQL). Хочется отметить то, что все названия триггеров являются русскоязычными. Также напомню, что база данных и триггеры созданы для MS SQL (проверялось).

Ссылочная целостность в ранее созданной базе поддерживается за счет следующих триггеров:— Ссылцелост_вид_вклада,— Ссылцелост_вид_кредита,— Ссылцелост_вклады,— Ссылцелост_кредиты,— Удаление_клиента,— Удаление_вид_вклада,— Запрет_удаления_базового_вклада,— Удаление_вид_кредита.Выше перечисленные триггеры обеспечивают корректную работу базы данных, поскольку, исключается ситуация существования в дочерних сущностях кортежей без родительских сущностей.

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

— Триггер Ссылцелост_вклады запрещает добавлять записи в таблицу Вклады, если соответствующие значения ид или ид_вклада отсутствуют в родительских таблицах (Клиенты и Вид_вклада)

CREATE TRIGGER Ссылцелост_вид_вклада on [dbo].[Вид_вклада]after INSERTasBEGINif not exists (select * from [dbo].[Валюта] INNER JOIN inserted on [dbo].[Валюта].ид_валюта=inserted.ид_валюта)BEGINrollback;PRINT ‘Отсутствует запись в родительской таблице — Валюта’;return;ENDEND;

GO

— Аналогично триггеры Ссылцелост_вид_кредита, Ссылцелост_вклады, Ссылцелост_кредиты

CREATE TRIGGER Ссылцелост_вид_кредита on [dbo].[Вид_кредита]after INSERT, UPDATEasBEGINif not exists (select * from [dbo].[Валюта] INNER JOIN inserted on [dbo].[Валюта].ид_валюта=inserted.ид_валюта)BEGINrollback;PRINT ‘Отсутствует запись в родительской таблице — Валюта’;return;ENDEND;

GO

CREATE TRIGGER Ссылцелост_вклады on [dbo].[Вклады]after INSERT, UPDATEasBEGINif not exists (select * from [dbo].[Клиенты] INNER JOIN inserted on [dbo].[Клиенты].ид=inserted.ид_клиент)or not exists (select * from [dbo].[Вид_вклада] INNER JOIN inserted on [dbo].[Вид_вклада].ид_вклада=inserted.вид_вклада)BEGINrollback;PRINT ‘Отсутствует запись в родительской таблице’;return;ENDEND;

GO

CREATE TRIGGER Ссылцелост_кредиты on [dbo].[Кредиты]after INSERT, UPDATEasBEGINif not exists (select * from [dbo].[Клиенты] INNER JOIN inserted on [dbo].[Клиенты].ид=inserted.ид_клиент)or not exists (select * from [dbo].[Вид_кредита] INNER JOIN inserted on [dbo].[Вид_кредита].ид_кредита=inserted.вид_кредита)BEGINrollback;PRINT ‘Отсутствует запись в родительской таблице’;return;ENDEND;

GO

— Триггер Удаление_клиента при удаление клиента из таблицы Клиенты, удаляет все вклады и кредиты этого клиента в дочерних таблицах (Вклады и Кредиты).

CREATE TRIGGER Удаление_клиента on [dbo].[Клиенты]after DELETEASBEGINIF EXISTS (SELECT * FROM DELETED INNER JOIN [dbo].[Вклады] on [dbo].[Вклады].ид_клиент=DELETED.ид)OR EXISTS (SELECT * FROM DELETED INNER JOIN [dbo].[Кредиты] on [dbo].[Кредиты].ид_клиент=DELETED.ид)BEGINDELETE FROM [dbo].[Вклады]WHERE [dbo].[Вклады].ид_клиент = (SELECT ид FROM DELETED)DELETE FROM [dbo].[Кредиты]WHERE [dbo].[Кредиты].ид_клиент = (SELECT ид FROM DELETED)ENDEND;

GO

— Триггер Удаление_вид_вклада при удаление какого-либо вида вклада (кроме базового) автоматически переведет все вклады на базовый вклад. Для запрета удаления базового вклада существует специальный триггер Запрет_удаления_базового_вклада

CREATE TRIGGER Удаление_вид_вклада on [dbo].[Вид_вклада]AFTER DeleteASBEGINIF EXISTS (SELECT * FROM DELETED INNER JOIN [dbo].[Вклады] on [dbo].[Вклады].вид_вклада=DELETED.ид_вклада)BEGINUPDATE [dbo].[Вклады] SET [dbo].[Вклады].вид_вклада=001 WHERE [dbo].[Вклады].вид_вклада = (SELECT ид_вклада FROM DELETED)ENDEND;

GO

CREATE TRIGGER Запрет_удаления_базового_вклада on [dbo].[Вид_вклада]AFTER Delete, updateASBEGINIF EXISTS (SELECT * FROM DELETED WHERE DELETED.ид_вклада=001)rollback;PRINT ‘Запрещено удалять или изменять номер базового вклада’;return;END;

GO

CREATE TRIGGER Удаление_вид_кредита on [dbo].[Вид_кредита]AFTER DeleteASBEGINIF EXISTS (SELECT * FROM DELETED INNER JOIN [dbo].[Кредиты] on [dbo].[Кредиты].вид_кредита=DELETED.ид_кредита)BEGINDELETE FROM [dbo].[Кредиты]WHERE [dbo].[Кредиты].вид_кредита = (SELECT ид_кредита FROM DELETED)ENDEND;

GO

— Хранимая процедура avgsum выводит идентификатор вклада, сумму вклада, номер клиента и название вклада среди всех вкладов, сумма вложений которых больше среднего.

CREATE PROCEDURE avgsum ASBEGINSELECT [dbo].[Вклады].ид_вклад, [dbo].[Вклады].сумма, [dbo].[Вклады].ид_клиент, [dbo].[Вид_вклада].названиеFROM [dbo].[Вклады], [dbo].[Вид_вклада]WHERE ([dbo].[Вклады].сумма >= (select AVG([dbo].[Вклады].сумма) from [dbo].[Вклады])) and([dbo].[Вклады].вид_вклада=[dbo].[Вид_вклада].ид_вклада)END;

— Вызов процедуры avgsum :exec avgsum

go

— Хранимая функция summa выводит средние значение суммы вклада

CREATE FUNCTION summa() RETURNS intBEGINDECLARE @itog int;SELECT @itog=AVG([dbo].[Вклады].сумма) from [dbo].[Вклады]RETURN @itogEND;

— Вызов процедуры avgsum :exec avgsum

go

— Представление Предвклады выводит фамилии клиентов, идентификаторы вкладов и суммы вкладов тех записей, у которых сумма вклада больше или равна 30000.

CREATE VIEW ПредвкладыAS SELECT Клиенты.ф AS Фамилия, Вклады.ид_вклад AS Идентификатор_вклада, Вклады.сумма AS СуммаFROM Клиенты, ВкладыWHERE (Вклады.сумма >= 30000) AND (Клиенты.ид=Вклады.ид_клиент)

— Вызов представления Предвклады:select * from Предвклады

all4study.ru

30. Триггеры в Transact sql. Пример реализации триггера.

Три́ггер (англ. trigger) — это хранимая процедура особого типа, исполнение которой обусловлено наступлением определенного события (действием) — по сути добавлением INSERT или удалением DELETE строки в заданной таблице, или модификации UPDATE данных в определенном столбце заданной таблицы реляционной БД.

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

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

В [] необязательные параметры

Создание триггера

 CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF }

{ DELETE] [,] [INSERT] [ , ] [ UPDATE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ {

IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] [ ...n ] | IF ( COLUMNS_UPDATED ( ) {bitwise_operator} updated_bitmask

) {comparison_operator} column_bitmask [...n] }] sql_statement [ ...n ] } }

trigger_name – название триггера

{ table | view } – имя таблицы или вьюхи на которую вешается триггер

[ WITH ENCRYPTION ] – шифровать или нет триггер

{ { FOR | AFTER | INSTEAD OF } – тип триггера (For – срабатывает до операции, After – после операции, INSTEAD OF - вместо)

{[DELETE] [,] [INSERT] [,] [UPDATE]} – операции на которые срабатывает триггер

[ WITH APPEND ] - Указывает, что требуется добавить триггер существующего типа. Не может быть использовано для триггеров INSTEAD OF или при явном указании триггера AFTER.

[ NOT FOR REPLICATION ] – не срабатывать при репликации

AS – после данного слова идет SQL скрипт самого триггера

IF UPDATE ( column ) – проверяет был ли изменен столбец column

IF (COLUMNS_UPDATED () – возвращает последовательность битов, которая показывает какие столбцы были изменена (например, табличка из столбцов Field1, Field2, Field3. Изменяем значение Field3. D результате после срабатывание триггера COLUMNS_UPDATED вернет нам 100 в двоичной системе или 4 в десятичной)

Для отката изменений используется

RAISERROR (‘Тест ошибки’, 16, 1) – вывод сообщения об ошибке

ROLLBACK TRANSACTION – откат изменений

В триггерах MSSQL существуют две системные таблицы Inserted и Deleted. В данных таблицах содержится полная структура (все поля) изменяемых таблиц и измененные данные.

  • при добавление, то в Inserted попадают добавляемые записи, а Deleted остается пустой

  • при удаление, то в Deleted попадают удаленные записи, а Inserted остается пустой.

  • при внесении изменения, то в Deleted попадают значения записей до изменения, а Inserted значения после изменения.

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

Пример:

CREATE TRIGGER CheckSpec

ON WorkSalesTable

FOR INSERT, UPDATE

AS

declare @work_Id int, @worker_Id int, @rr int

SELECT @work_Id = workId, @worker_Id = workerId FROM inserted

SELECT @rr=count(*) FROM WorkWorker WHERE workId = @work_Id AND workerId = @worker_Id

if(@rr = 0)

BEGIN

raiserror('You Can not do it!',16,1)

ROLLBACK transaction

END

GO

studfiles.net

Хранимые процедуры, функции и триггеры [АйТи бубен]

  • Хранимая процедура — это объект базы данных, представляющий собой набор SQL- инструкций, который компилируется один раз и хранится на сервере.

  • Хранимая функция отличается от хранимой пороцедуры, тем что хранимая функция всегда возвращает только скаляр(то есть единичное значение), а процедура возвращает набор значений.

  • Триггер (trigger) — это хранимая процедура, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено действием по модификации данных: добавлением INSERT, удалением DELETE строки в заданной таблице, или изменением UPDATE данных в определенном столбце заданной таблицы реляционной базы данных. В PostgreSQL триггеры создаются на основе существующих функции, т.е. сначала командой CREATE FUNCTION определяется триггерная функция, затем на ее основе командой CREATE TRIGGER определяется собственно триггер. Посмотреть существующие триггеры:select * from pg_trigger;

  • Пример функции(PL/pgSQL), которая выводит текущую датуCREATE OR REPLACE FUNCTION getDate() RETURNS text AS $BODY$ BEGIN RETURN CURRENT_TIMESTAMP; END $BODY$ LANGUAGE 'plpgsql' ;
  • Функция возвращающая много строкCREATE OR REPLACE FUNCTION g_peer() RETURNS SETOF text AS $BODY$ DECLARE r text; BEGIN FOR r IN SELECT DISTINCT "peer_id" FROM "ratesheets" ORDER BY peer_id LOOP RETURN NEXT r; END LOOP; RETURN; END $BODY$ LANGUAGE 'plpgsql'
  • Функция возвращает сведения о времени последнего процесa autovacuum, autoanalyze. Возвращающей столбец записей.CREATE OR REPLACE FUNCTION sql_last_v_a() RETURNS SETOF record AS' SELECT schemaname,relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze FROM pg_stat_all_tables ORDER BY schemaname, relname; 'LANGUAGE sql;

Триггеры MySQL. Поддержка для триггеров включена, начиная с MySQL 5.0.2.

Триггер представляет собой именованный объект базы данных (хранимая процедура), который связан с таблицей, и он будет активизирован, когда специфическое событие INSERT, UPDATE и DELETE (вставка, обновление строки или удаление) происходит для таблицы. Триггеры могут срабатывать как до BEFORE так и после AFTER изменения таблицы.

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt

где

trigger_name — название триггера trigger_time — Время срабатывания триггера. BEFORE — перед событием. AFTER — после события. trigger_event — Событие: insert — событие возбуждается операторами insert, data load, replace update — событие возбуждается оператором update delete — событие возбуждается операторами delete, replace. Операторы DROP TABLE и TRUNCATE не активируют выполнение триггера tbl_name — название таблицы trigger_stmt выражение, которое выполняется при активации триггера
  • Просмотреть все триггеры в конкретной базе данных. Чтобы вывести все триггеры, определенных для базы данных dbname, нужно выполнить запрос к таблице INFORMATION_SCHEMA.TRIGGERS:SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='dbname';

    или

    SHOW TRIGGERS FROM dbname;
  • Для удаления триггера нужно указывать схемуmysql> DROP TRIGGER dbname.my_trigger;

Пример MySQL триггера

Задача. Нужно после вставки (INSERT) строки в таблицу cdr, обновить (UPDATE) таблицу actions внеся в нее значение id новой записи таблицы cdr.

mysql> DELIMITER // mysql> CREATE TRIGGER `ti_cdr` AFTER INSERT ON `mbillcc`.`cdr` -> FOR EACH ROW -> BEGIN -> UPDATE actions SET state = NEW.id WHERE action = 'cdrrow_end'; -> END// Query OK, 0 ROWS affected (0.00 sec)   mysql> DELIMITER ;

NEW — для доступа к новым записям; OLD — для доступа к старым записям

загрузка...

xranimye_procedury_funkcii_i_triggery.txt · Последние изменения: 2018/07/23 07:27 — darkfire

wiki.dieg.info

Триггеры

Триггеры являются наиболее эффективным инструментом сохранения целостности баз данных, так как позволяют подробно проанализировать события, происходящие в системе.  Все триггеры можно разделить на два класса: триггеры DML – перехват  команд insert, update, delete и триггеры DDL – перехват  команд DDL. В свою очередь триггеры DML делятся на триггеры After – выполняющиеся после выполнения команды и триггеры Instead of – триггеры выполняются вместо соответствующих команд SQL.

Триггеры DML

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

Формат команды создания триггера следующий

create trigger [ schema_name . ]trigger_name

on { table | view }

[ with <dml_trigger_option> [ ,...n ] ]

{ for | after | instead of }

{ [ insert ] [ , ] [ update ] [ , ] [ delete ] }

[ with append ]

[ not for replication ]

as

{ sql_statement  [ ; ] [ ...n ] |

external name <method specifier [ ; ] > }

Здесь

■           [ schema_name . ]trigger_name – имя создаваемого триггера. Должно удовлетворять требованиям, предъявляемым к идентификаторам. Имя триггера не может начинаться с символа '#', поскольку триггер не может быть временным объектом.

■           on { table | view } – опция указывает к какой таблице или представлению (имя таблицы или представления) триггер будет относиться.  Только триггеры типа instead of могут создаваться для представлений.

■                       <dml_trigger_option> - опции триггера.  Можно использовать два вида опций  encryption (шифровать)  и    execution as clause (задать контекст), но с ними мы уже знакомы.

■                       for | after | instead of – определяется тип триггера. Опции и for и after являются синонимами.

■           [ delete ] [ , ] [ insert ] [ , ] [ update ] -  указываются команды DML, на которые будет реагировать данный триггер.  Следует указать, по крайней мере, одну команду.

■           with append  - данная опция устарела и используется для совместимости.

■           not for replication – опция показывает, что триггер не будет срабатывать, когда во время репликации будут происходить изменения в таблице.

■           sql_statement  [ ; ] [ ...n ] – последовательность команд языка Transact SQL, которые будут выполняться, при запуске триггера.

■           external name <method specifier >– опция используется при создании триггера на основе технологии  .NET.

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

alter trigger schema_name.trigger_name

on ( table | view )

[ with <dml_trigger_option> [ ,...n ] ]

( for | after | instead of )

{ [ delete ] [ , ] [ insert ] [ , ] [ update ] }

[ not for replication ]

as { sql_statement [ ; ] [ ...n ] |

external name <method specifier> [ ; ] }

Удаление триггера DDL осуществляется командой drop trigger schema_name.trigger_name [ ,...n ]. Т.е. одной командой можно удалить сразу несколько триггеров.

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

Триггеры instead of выполняются вместо операций DML и по одному для каждой операции. Эти триггеры могут создаваться и для представлений.

В триггерах after используются специальный инструментарий для определения того, что произошло с таблицей (чем вызван запуск триггера). Функция update (column) определяет, был или не был модифицирован данный столбец. Относиться к командам update и insert. Если столбец был модифицирован, то функция возвращает TRUE. Функция columns_updated() позволяет определить, какой столбец был изменении.  Функция возвращает двоичное число, каждый бит которого относиться к конкретному столбцу. Если бит равен 1 то это значит, что столбец был изменен командой update или insert.

Перед выполнением для триггера автоматически создаются две временные таблицы: inserted и deleted. Их содержимое зависит от того, какая операция была выполнена:

■           При выполнении команды insert таблица inserted будет содержать новые строки. Таблица deleted будет пуста.

■           При выполнении команды delete таблица deleted будет содержать удаляемые строки, таблица inserted будет пустой.

■           При выполнении команды update таблица deleted будет содержать старые значения строк, таблица inserted – новые.

В случае триггера instead of таблицы deleted и inserted будут содержать строки, которые соответственно должны быть удалены или должны быть вставлены. 

Во временные таблицы (deleted и inserted) нельзя вносить какие-либо изменения. Но из триггера можно менять содержимое любых других таблиц. Из триггера нельзя выполнять следующие команды языка Transact SQL: reconfigure, create database, alter database, drop database, restore database, restore log, load database, load log.

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

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

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

sp_settriggerorder[@triggername = ] 'triggername'

, [@order = ] 'value'

, [@stmttype = ] 'statement_type'

Здесь

■           [@triggername = ] 'triggername' – определяет имя триггера, которое может содержаться и в переменной.

■           [@order = ] 'value' – порядок следования триггера. Который может быть:

o        first – выполняется первый.

o        last – выполняется второй.

o        none – порядок не определен.

■           [@stmttype = ] 'statement_type' – тип инструкции.

 

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

Наконец триггер не возвращать никаких наборов строк. Эта возможность считается устаревшей и будет удалена в будущих версиях SQL Server.

Примеры триггеров

В Листинге 3.83 представлен простой триггер after, который запрещает обновлять значение столбца t1 для таблицы table1. При этом в вызывающий модуль с помощью функции raiserror будет возвращено сообщение о причине отказа. Поскольку триггер расположен в одной транзакции с командой, которая вызвала данный триггер, то команда rollback transaction возвращает состояние таблицы в исходное положение.

Листинг 3.83

create trigger no_update on table1

after update

as

if update(t1)

begin

        raiserror ('Обновлять нельзя', 15,1)

        rollback transaction

end

Рассмотрим еще один пример (см. Листинг 3.84).

Листинг 3.84

create trigger dt_del on dbo.students

instead of delete

as

begin

--удалить из таблицы marks

        delete from dbo.marks

        where id_student in (select id from deleted)

        if(@@error>0)

        begin

                rollback transaction

                raiserror('Ошибка удаления из таблицы marks',16,3)

                return

        end

--удалить из таблицы students

        delete from dbo.students

        where id in (select id from deleted)

        if(@@error>0)

        begin

                rollback transaction

                raiserror('Ошибка удаления из таблицы students',16,3)

                return

        end

return

end

Триггер из Листинга 3.84 срабатывает на попытку удаления из таблицы dbo.students. Триггер в начале удаляет строки из связанной таблицы dbo.marks, а затем уже из таблицы dbo.students (каскадное удаление). При этом в триггере обрабатываются и возможные ошибки с откатом тразнакции и возвращаением в приложение сообщения об ошибке.

Триггеры DDL

Триггеры DDL запускаются в ответ на команды DDL.

Вот формат команды создания триггера DDL

create trigger_name

on { all server | database }

[ with <ddl_trigger_option> [ ,...n ] ]

{ for | after } { event_type | event_group } [ ,...n ]

as { sql_statement  [ ; ] [ ...n ]

| external name < method specifier >  [ ; ] }

Здесь

■           on { all server | database } – данная опция показывает, будет ли триггер действовать в пределах текущей базы данных или в пределах всего сервера SQL.

■           <ddl_trigger_option> - данная опция аналогична такой же опции для триггеров DML.

■           event_type – имя события, при наступлении которого должен быть запущен триггер. Список событий можно найти в документации. Например, ALTER_INDEX означает, что триггер будет запускаться при попытке  изменить индекс.

■           event_group – можно указать целую группу событий, при наступлении которых должен быть запущен данный триггер. Список имен групп событий можно найти в документации. Например, DDL_TABLE_EVENTS означает группу событий, связанных с таблицами.

Поскольку остальные опции нам уже знакомы, перейдем сразу к примеру (см. Листинг 3.85).

Листинг 3.85

create trigger tr1

on database

for DDL_TABLE_EVENTS –триггер на операции с таблицами

as

begin

        rollback transaction

        raiserror('Операции над таблицами запрещены',16,3)

        return

end

При работе с DDL триггерами удобно использовать функцию eventdata().  Данная функция, запущенная внутри триггера возвращает полную информацию о происшедшем событии. Особенностью функции является то, что информация, которую она возвращает, имеет структуру xml-документа.

ssofta.narod.ru

Триггеры в MySQL / Хабр

Триггер — это хранимая процедура, которая не вызывается непосредственно, а исполняется при наступлении определенного события ( вставка, удаление, обновление строки ). Поддержка триггеров в MySQL началась с версии 5.0.2

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

CREATE TRIGGER trigger_name trigger_time trigger_eventON tbl_name FOR EACH ROW trigger_stmt* This source code was highlighted with Source Code Highlighter. trigger_name — название триггера trigger_time — Время срабатывания триггера. BEFORE — перед событием. AFTER — после события. trigger_event — Событие: insert — событие возбуждается операторами insert, data load, replace update — событие возбуждается оператором update delete — событие возбуждается операторами delete, replace. Операторы DROP TABLE и TRUNCATE не активируют выполнение триггера tbl_name — название таблицы trigger_stmt выражение, которое выполняется при активации триггера

Применение

Лог

Исходные данные: — таблица, за которой мы будем следитьCREATE TABLE `test` ( `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `content` TEXT NOT NULL ) ENGINE = MYISAM — логCREATE TABLE `log` ( `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `msg` VARCHAR( 255 ) NOT NULL, `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `row_id` INT( 11 ) NOT NULL ) ENGINE = MYISAM — триггер DELIMITER |CREATE TRIGGER `update_test` AFTER INSERT ON `test`FOR EACH ROW BEGIN    INSERT INTO log Set msg = 'insert', row_id = NEW.id;END;* This source code was highlighted with Source Code Highlighter. Теперь добавьте запись в таблицу test. В таблице log тоже появится запись, обратите внимание на поле row_id, в нем хранится id вставленной вами строки.

Расширенный лог:

Исходные данные: — Удаляем триггерDROP TRIGGER `update_test`; — Cоздадим еще одну таблицу, — в которой будут храниться резервные копии строк из таблицы testCREATE TABLE `testing`.`backup` ( `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `row_id` INT( 11 ) UNSIGNED NOT NULL, `content` TEXT NOT NULL ) ENGINE = MYISAM — триггеры DELIMITER |CREATE TRIGGER `update_test` before update ON `test`FOR EACH ROW BEGIN   INSERT INTO backup Set row_id = OLD.id, content = OLD.content;END;

CREATE TRIGGER `delete_test` before delete ON `test`FOR EACH ROW BEGIN   INSERT INTO backup Set row_id = OLD.id, content = OLD.content; END

* This source code was highlighted with Source Code Highlighter. Теперь если мы отредактируем или удалим строку из test она скопируется в backup.

зы: надеюсь статья была интересной и полезнойUPD: для создания триггеров в версии до 5.1.6 требуются полномочия суперпользователя.

habr.com

3.4. Триггеры - Transact-SQL В подлиннике : Персональный сайт Михаила Флёнова

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

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

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

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

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

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

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

3.4.1. Создание триггера

Для создания триггеров используйте оператор CREATE TRIGGER. В операторе указывается таблица, для которой объявляется триггер, событие, для которого триггер выполняется и индивидуальные инструкции для триггера. В общем команда показана в листинге 3.2.

Листинг 3.2. Общий вид команды CREATE TRIGGER

CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ { IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] [ ...n ] | IF (COLUMNS_UPDATED() {bitwise_operator} updated_bitmask) { comparison_operator } column_bitmask [ ...n ] } ] sql_statement [ ...n ] } }

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

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

  • ALTER DATABASE;
  • CREATE DATABASE;
  • DISK INIT;
  • DISK RESIZE;
  • DROP DATABASE;
  • LOAD DATABASE;
  • LOAD LOG;
  • RECONFIGURE;
  • RESTORE DATABASE;
  • RESTORE LOG.

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

3.4.2. Откат изменений в триггере

Объявление триггера может содержать оператор ROLLBACK TRANSACTION даже если не существует соответствующего BEGIN TRANSACTION. Как мы уже говорили, для любого изменения SQL сервер требует транзакции. Если она не указано явно, то создается неявная транзакция. Если выполняется оператор ROLLBACK TRANSACTION, то все изменения в триггере и изменения, которые стали причиной срабатывания триггера - откатываются.

При использовании отката изменений, вы должны учитывать следующее:

  • Если срабатывает оператор ROLLBACK TRANSACTION, содержимое транзакции откатывается. Если есть операторы, следующие за ROLLBACK TRANSACTION, операторы выполняются. Это может быть не обязательным при использовании команды RETURN;
  • Если триггер откатывает транзакцию, определенную пользователем, то она откатывается полностью. Если триггер сработал, на выполнение модуля, для модуля команды также отменяются. Последующие операторы модуля не выполняются;
  • Вы должны минимизировать использование ROLLBACK TRANSACTION в коде триггера. Откат транзакции создает дополнительную работу, потому что все работы, которые не были закончены на данный момент в транзакции, будут незавершенными. Это будет негативно сказываться на производительности. Запускайте транзакцию после того, как все проверено, чтобы не пришлось ничего откатывать в триггере.

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

CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE AS ROLLBACK TRANSACTION

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

  • имя начинается одной или сочетания букв u (update или обновление), i (insert или вставка) или d (delete или удаление). По этим буквам вы легко можете определить, на какие действия срабатывает триггер;
  • после подчеркивания идет имя таблицы, для которого создается триггер.

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

Во второй строке идет ключевое слово FOR и событие, на которое срабатывает триггер. В данном примере указано действие UPDATE, т.е. обновление. И, наконец, после ключевого слова AS идет тело триггера, т.е. команды, которые должны выполняться. В данном примере выполняется только одна команда - ROLLBACK TRANSACTION, т.е. откат.

Теперь попробуем изменить данные в таблице tbPeoples, чтобы сработал триггер:

UPDATE tbPeoples SET vcFamil='dsfg'

В данном примере мы пытаемся изменить содержимое поля "vcFamil" для всех записей таблицы tbPeoples. Почему пытаемся? Да потому что при изменении срабатывает триггер с откатом транзакции. Выполните выборку данных, чтобы убедиться, что все данные на месте и не изменились:

SELECT * FROM tbPeoples

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

3.4.3. Изменение триггера

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

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

Листинг 3.3. Оператор обновления триггера

ALTER TRIGGER trigger_name ON ( table | view ) [ WITH ENCRYPTION ] { { ( FOR | AFTER | INSTEAD OF ) { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } [ NOT FOR REPLICATION ] AS sql_statement [ ...n ] } | { ( FOR | AFTER | INSTEAD OF ) { [ INSERT ] [ , ] [ UPDATE ] } [ NOT FOR REPLICATION ] AS { IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] [ ...n ] |IF(COLUMNS_UPDATED() { bitwise_operator } updated_bitmask) { comparison_operator } column_bitmask [ ...n ] } sql_statement [ ...n ] } }

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

ALTER TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE, INSERT AS ROLLBACK TRANSACTION

Как видите, оператор обновления похож на создание триггера. Разница в том, что в первой строке стоит оператор ALTER TRIGGER. Во второй строке произошло изменение, и теперь триггер будет срабатывать не только на обновление (UPDATE), но и на добавление (INSERT).

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

INSERT INTO tbPeoples(vcFamil) VALUES('ПЕТЕЧКИН')

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

ALTER TABLE table {ENABLE | DISABLE} TRIGGER {ALL | trigger_name [,..n]}

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

ALTER TABLE tbPeoples DISABLE TRIGGER u_tbPeoples

В первой строке мы пишем оператор ALTER TABLE и имя изменяемой таблицы. Во второй строке нужно указать ключевое слово DISABLE (отключить) или ENABLE (включить) и ключевое слово TRIGGER. И, наконец, имя триггера.

Попробуйте теперь добавить запить в таблицу tbPeoples. На этот раз, все пройдет успешно.

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

ALTER TABLE tbPeoples ENABLE TRIGGER ALL

3.4.4. Удаление триггеров

Для удаления триггера вы можете воспользоваться оператором DROP TRIGGER. Он удаляется автоматически, когда связанная с ним таблица удаляется.

Пример удаления триггера:

DROP TRIGGER u_tbPeoples

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

3.4.5. Как работают триггеры?

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

Триггер INSERT

Что происходит, когда срабатывает триггер добавления записей? Давайте рассмотрим выполняемые сервером шаги:

  • Пользователем выполняется оператор INSERT для добавления записей;
  • Сервер сохраняет информацию о запросе в журнале транзакций;
  • Вызывается триггер;
  • Подтверждение изменений и физическое изменение данных.

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

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

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

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

Листинг 3.4. Использование таблицы inserted

CREATE TRIGGER i_tbPeoples ON dbo.tbPeoples FOR INSERT AS DECLARE @Name varchar(50) SELECT @Name=vcName FROM inserted IF @Name='ВАСЯ' BEGIN PRINT 'ОШИБКА' ROLLBACK TRANSACTION END

В данном примере мы создаем триггер на добавление записей. Внутри триггера мы объявляем переменную @Name типа varchar длиной в 50 символов. В эту переменную мы сохраняем содержимое поля "vcName" таблицы inserted. Далее проверяем, если имя равно Вася, то сообщаем об ошибке и откатываем транзакцию. Иначе, строка будет удачно добавлена.

Давайте для закрепления материала, напишем триггер, который запретит нулевые значения для поля "vcName". Код такого триггера можно увидеть в листинге 3.5.

Листинг 3.5. Запрет нулевых значений в поле с помощью триггера

CREATE TRIGGER i_tbPeoples ON dbo.tbPeoples FOR INSERT AS IF EXISTS (SELECT * FROM inserted WHERE vcName is NULL) BEGIN PRINT 'ОШИБКА, вы должны заполнить поле vcName' ROLLBACK TRANSACTION END

В этом примере мы проверяем, если в таблице inserted есть записи с нулевым значением поля "vcName", то откатываем попытку добавления.

Триггер DELETE

Когда срабатывает триггер удаления, срабатывает примерно та же логика, что и при добавлении записей:

  • Пользователем выполняется оператор DELETE для добавления записей;
  • Сервер сохраняет информацию о запросе в журнале транзакций;
  • Вызывается триггер;
  • Подтверждение изменений и физическое изменение данных.

Удаляемые строки помещаются в таблицу deleted, с помощью которой вы можете увидеть удаляемые строки. Это логическая таблицf, которая ссылается на данные журнала оператора DELETE.

Вы должны учитывать:

  • когда строки добавляются в таблицу deleted, они еще существуют в таблице базы данных;
  • для таблицы deleted выделяется память, поэтому она всегда в кэше;
  • триггер удаления не выполняется на операцию TRUNCATE TABLE (очистка таблицы) потому что эта операция не заносится в журнал и не удаляет строк.

Давайте попробуем создать триггер, который запретит удаление пользователя с определенным именем. Пример такого триггера можно увидеть в листинге 3.6.

Листинг 3.6. Пример запрета удаления с помощью триггера

CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS IF EXISTS (SELECT * FROM deleted WHERE vcName='рлр') BEGIN PRINT 'ОШИБКА, нельзя удалить этого пользователя' ROLLBACK TRANSACTION END

В этом примере мы проверяем, если в таблице deleted существует запись с именем "рлр", то откатываем удаление. Добавьте в таблице запись с именем "рлр" и попытайтесь ее удалить. В ответ вы должны увидеть ошибку.

А что если попытаться удалить несколько записей? Например, в следующем примере удаляются записи две записи:

DELETE FROM tbPeoples WHERE vcName='рлр' or vcName='ВАСИЛИЙ'

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

Посмотрим на еще один пример в котором запрещается удаление генерального директора. Без триггера такое сделать невозможно:

CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS IF EXISTS (SELECT * FROM deleted WHERE idPosition=1) BEGIN PRINT 'ОШИБКА, нельзя удалить этого пользователя' ROLLBACK TRANSACTION END

В этом примере, запрещается удаление записи, если поле "idPosition" равно 1. Попробуйте удалить такую запись:

DELETE FROM tbPeoples WHERE idPosition=1

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

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

Триггер UPDATE

Обновление происходит в два этапа – удаление и вставка. Нет, физически в базе данных происходит изменение, это триггер видит два этапа. Поэтому существующие строки помещаются в таблицу deleted (то есть то, что было), а новые данные помещаются в таблицу inserted. Триггер может проверять эти таблицы для определения, какие строки и как могут измениться.

Вы можете объявить триггер для мониторинга обновления определенного поля с помощью указания опции IF UPDATE. Это позволяет триггеру изолировать активность определенной колонки. Когда обнаруживается обновление определенной колонки, триггер может выполнить определенные действия, такие как выброс сообщения об ошибке, которое сообщит о невозможности обновления колонки.

Давайте создадим триггер на таблицу tbPeoples, который будет выводить на экран сообщение, если изменяется поле "vcName"

CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE AS IF UPDATE (vcName) PRINT 'Я надеюсь, что вы правильно указали имя'

После оператора IF UPDATE, в скобках указано поле, которое необходимо проверить, было ли оно изменено. Если да, то будет выполнен следующий за проверкой оператор. В данном случае, это вывод на экран сообщения с помощью PRINT. Когда указанное поле не изменяется, то оператор конечно же не выполняется. Если нужно выполнить несколько операторов, то объедините их с помощью BEGIN и END.

Следующий запрос тестирует триггер:

UPDATE tbPeoples SET vcName='ИВАНУШКА' WHERE vcFamil='ПОЧЕЧКИН'

Убедитесь, что сообщение из триггера выводится на экран.

Давайте с помощью триггера попробуем запретить изменение полей, составляющих ФИО ("vcFamil", "vcName" и "vcSurName"). Для этого, если изменено одно из этих полей, то выводим на экран сообщение о запрете и откатываем транзакцию:

CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE AS IF UPDATE (vcName) OR UPDATE (vcFamil) OR UPDATE (vcSurname) BEGIN PRINT 'Нельзя изменять фамилию, имя и отчество' ROLLBACK TRANSACTION END

С помощью такого запроса легко увидеть, как проверять обновление сразу нескольких полей и выводить несколько операторов. Обратите внимание, что проверку делает именно оператор UPDATE, а не IF UPDATE. Я даже не знаю, почему разработчики SQL Server объединяют эти два оператора. Первый, это логический оператор, а второй – проверка, было ли обновлено поле.

3.4.6. INSTEAD OF

Вы можете указать триггер INSTEAD OF для таблиц и просмотрщиков. Действия такого триггера выполняются вместо операторов, сгенерировавших триггер. Не понятно? Рассмотрим пример. Допустим, что у вас есть триггер INSTEAD OF на событие обновления таблицы. Если пользователь выполняет обновление, то выполняется триггер, но при этом, оператор, запущенный пользователем, только генерирует событие. Реальное обновление данных должно происходить с помощью операторов триггера.

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

Как можно использовать INSTEAD OF? Допустим, что у нас есть объект просмотра, который выбирает данные их двух таблиц. Как мы уже знаем, данные вьюшки можно изменять, только если все они принадлежат одной таблице. Но с помощью триггера можно сделать обновление любого количества таблиц.

Давайте создадим объект просмотра, который будет выбирать фамилию работника и название должности. Назовем этот объект просмотра Peoples:

CREATE VIEW People AS SELECT vcFamil, vcPositionName FROM tbPosition ps, tbPeoples pl WHERE ps.idPosition=pl.idPosition

Теперь создадим триггер INSTEAD OF на этот объект просмотра, с помощью которого, можно будет добавлять записи и при этом, они корректно будут прописываться, каждая в свою таблицу:

Листинг 3.7. Триггер INSTEAD OF для вставки данных

CREATE TRIGGER i_People ON dbo.People INSTEAD OF INSERT AS BEGIN -- Добавление должности INSERT INTO tbPosition (vcPositionName) SELECT vcPositionName FROM inserted i -- Добавление работника INSERT INTO tbPeoples (vcFamil, idPosition) SELECT vcFamil, idPosition FROM inserted i,tbPosition pn WHERE i.vcPositionName=pn.vcPositionName END

В этом примере интересности начинаются прямо со второй строки. Здесь указывается оператор INSTEAD OF и событие, на которое нужно реагировать. В данном случае в качестве события выступает вставка (INSERT).

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

INSERT INTO tbPeoples (vcFamil, idPosition) SELECT vcFamil, idPosition FROM inserted i,tbPosition pn WHERE i.vcPositionName=pn.vcPositionName

Попробуйте выполнить следующий запрос на добавление записей в объект просмотра:

INSERT INTO People VALUES('ИВАНУШКИН', 'Клерк')

Выполните следующий запрос и убедитесь, что новая запись добавлена:

SELECT * FROM People

При обновлении таблицы есть одна проблема – нужно связать обновляемые данные с существующими. Первым на ум приходит запрос типа:

UPDATE tbPosition SET vcPositionName=i.vcPositionName FROM tbPosition pn, inserted i WHERE i.vcPositionName = pn.vcPositionName

Здесь мы связываем таблицу должностей с таблицей inserted. Но такой запрос никогда не будет выполнен. Почему? В inserted находятся новые значения, а в tbPosition еще старые и названия должностей никогда не свяжутся. Если связать с таблицей deleted, то записи свяжутся, но мы не будем знать новых значений, которые нужно занести в таблицу. Проблему можно решить, но лучшим вариантом будет добавление в объект просмотра ключевых полей:

ALTER VIEW People AS SELECT idPeoples, pl.idPosition, vcFamil, vcPositionName FROM tbPosition ps, tbPeoples pl WHERE ps.idPosition=pl.idPosition

Теперь INSTEAD OF триггер для обновления данных будет выглядеть, как показано в листинге 3.8.

Листинг 3.8. Обновление связанной вьюшки с помощью триггера

CREATE TRIGGER u_People ON dbo.People INSTEAD OF UPDATE AS BEGIN UPDATE tbPosition SET vcPositionName=i.vcPositionName FROM tbPosition pn, inserted i WHERE i.idPosition=pn.idPosition UPDATE tbPeoples SET vcFamil=i.vcFamil FROM tbPeoples pl, inserted i WHERE i.idPeoples=pl.idPeoples END

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

UPDATE People SET vcFamil='ИВАНУШКИН', vcPositionName='Генеральный директор' WHERE idPeoples=40 AND idPosition=13

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

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

3.4.7. Дополнительно о триггерах

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

  • Выполнения действий или каскадного обновления или удаления. Целостность ссылок может отличаться при использовании ограничений FOREIGN KEY и REFERENCE в операторе CREATE TABLE. Но триггер выгоден для гарантирования необходимых действий, когда должны быть произведены каскадные удаления или обновления, потому что триггеры более мощные. Если ограничение существует для таблицы с триггером, оно проверяется до выполнения триггера. Если ограничение нарушено, то триггер не работает. Если ограничение не сработает, то с помощью триггера можно реализовать более сложные проверки, которые уж точно будут гарантировать, что данные не нарушат целостность и пользователь внесет только те данные, которые разрешены;
  • Вы должны учитывать, что в таблицу может вставляться сразу несколько строк. Вы должны учитывать это при написании триггеров, как мы это делали при создании примеров с использованием INSTEAD OF;
  • Ограничения, правила и значения по умолчанию могут генерировать только стандартные системные ошибки. Если вам нужны собственные сообщения, вы должны использовать триггеры.

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

CREATE TRIGGER iu_tbPeoples ON dbo.tbPeoples FOR INSERT, UPDATE AS Действие

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

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

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

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

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

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

3.4.8. Практика использования триггеров

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

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

Итак, давайте создадим триггер, который при изменении или удалении строк в таблице tbPeoples будет копировать их в таблицу истории tbpeoplesHistory. Если бы первичный ключ был в виде уникального идентификатора, то задача решалась бы следующим образом:

CREATE TRIGGER ud_tbPeoples ON dbo. tbPeoples FOR UPDATE, DELETE AS INSERT INTO tbPeoplesHistory SELECT newid(), del.* FROM Deleted del

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

В данном примере содержимое таблицы Deleted копируется в таблице tbPeoplesHistory. Запрос упрощается тем, что первичный ключ можно сгенерировать с помощью функции newid().

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

CREATE TRIGGER ud_tbPeoplesHistory ON dbo.tbPeoples FOR UPDATE, DELETE AS INSERT INTO tbPeoplesHistory (idPeoples, vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) SELECT del.* FROM Deleted del

Теперь посмотрим, как можно запретить удаление более чем одной строки:

CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS IF (SELECT count(*) FROM deleted)>1 BEGIN PRINT 'Нельзя удалять более одной строки' ROLLBACK TRANSACTION END

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

Вложение при инсталляции включено, но вы можете отключить и снова включить с помощью системной процедуры sp_configure. Например, следующий пример отключает вложенные триггеры:

sp_configure ‘nested triggers’, 0

Триггеры могут иметь вложения до 32 уровней. Если какой-нибудь триггер зациклится, то будет превышен предел. Триггер прерывается и транзакция откатывается.

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

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

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

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

ALTER DATABASE FlenovSQLBook SET RECURSIVE_TRIGGERS ON

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

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

CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS DELETE pn FROM tbPhoneNumbers pn, inserted i WHERE pn.idPeoples=i.idPeoples

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

  1. При добавлении записи в таблицу телефонов увеличиваем значение поля в таблицы работников;
  2. При удалении номера телефона, уменьшаем значения поля.

Попробуйте реализовать это самостоятельно, чтобы закрепить знания и потренироваться в работе с SQL запросами.

Для определения таблиц с триггером, выполните процедуру sp_depends. Например, выполните следующую команду, чтобы увидеть все зависимости для таблицы tbPeoples:

EXEC sp_depends 'tbPeoples'

Для определения, какие триггеры существуют на определенную таблицу, и на какие действия выполните процедуру sp_helptrigger. Следующий пример отображает все триггеры, которые принадлежат объекту просмотра People (если нужно просмотреть триггеры таблицы, то укажите ее имя):

EXEC sp_helptrigger People

Для просмотра кода существующего триггера используйте sp_helptext. Например, следующая команда позволяет увидеть текст триггера u_People, которую мы создавали для объекта просмотра:

EXEC sp_helptext u_People

Назад к содержимому "Transact-SQL В подлиннике"

www.flenov.info

MySQL и триггеры. Тонкости и особенности.

Давайте поговорим о триггерах в MySQL. Это сложная и в некотором роде больная тема в MySQL. Они появились, начиная с версии 5.0.2, и пока еще не все с ними “устаканилось”. У триггеров тут есть несколько тонкостей и о них стоит помнить каждый раз, когда мы пишем очередной триггер.

Триггер можно создавать всего на 3 события INSERT, UPDATE, DELETE. Для каждого из этих событий есть возможность управлять моментом срабатывания триггера: “до” события, “после” события. Итого, со всевозможными комбинациями получаем 6 типов триггеров.Создание триггера.

Синтаксис довольно прост и незамысловат.

CREATE [DEFINER = CURRENT_USER] TRIGGER `[ имя триггера ]` [ BEFORE | AFTER ] [ INSERT | UPDATE | DELETE ] ON `[ имя таблицы ]` FOR EACH ROW BEGIN [ Тело триггера ] END;

CREATE [DEFINER = CURRENT_USER]

 

TRIGGER `[ имя триггера ]` [ BEFORE | AFTER ] [ INSERT | UPDATE | DELETE ] ON `[ имя таблицы ]`

 

FOR EACH ROW

 

BEGIN

 

[ Тело триггера ]

 

END;

 

В DEFINER мы можем указать пользователя-создателя, а в остальном, я думаю, комментариев достаточно. Рассмотрим, что же можно делать внутри тела триггера. В целом там можно писать на SQL различные конструкции, условия, циклы и т.д. Кроме того, код между BEGIN и END выполнится для всех записей, затронутых операцией, на которую срабатывает триггер. Но важно помнить несколько особенностей триггеров в MySQL.

Первое: супер ты или не супер?

Да-да. Глупость казалось бы, но все же. Для того, чтобы создать триггер, вы должны обладать правами суперпользователя, иначе получите ошибку и ничего не добьетесь. Это создает некоторые проблемы, ведь большинство хостинговых площадок не дает таких прав своим клиентам.

Второе: lock – это серьезно!

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

Третье: никаких return’ов!

Да. Это тоже минус. Мы не можем вернуть никакие данные из триггера. То есть, представьте себе ряд условий, выполнение которых необходимо для продолжения работы триггера. По хорошему, мы могли бы прервать тут исполнение триггера конструкцией типа “RETURN;”, но ничего не выйдет. Придется опять выдумывать костыли и генерировать ошибку во время исполнения (например, SET NEW=NULL;).

Но не все так плохо как кажется. В нашем распоряжении есть такие значения как NEW и OLD. Они позволяют обращаться к новой (или вставляемой) записи или же к старой (содержащейся в таблице) соответственно.

Как работать?

Несмотря на все это, с триггерами можно работать, и иногда они оказываются полезными. Мы можем слегка обогнуть lock. Если нам нужно обновить текущую таблицу, просто используем SET (например, SET NEW.<имя_поля>=1;). Как видим в примере, OLD и NEW очень удобны. Но надо помнить, когда они доступны, а когда нет.

  • BEFORE INSERT
    • - NEW – доступен.
      • – Доступна возможность изменения его полей.
      • – Доступна возможность получить значения полей и полей по умолчанию (которые явно в INSERT’е не фигурировали).
      • – Не доступен автоинкремент.
    • - OLD – не доступен (нет ничего прежнего во время вставки).
    • - Внутри триггера можно отменить операцию.
  • AFTER INSERT
    • - NEW – доступен.
      • – Не доступна возможность изменения полей. Они уже вставлены.
      • – Доступна возможность получить значения полей и полей по умолчанию.
      • – Доступна возможность получить автоинкременты.
    • - OLD – не доступен (по тем же причинам).
    • - Внутри триггера нельзя отменить операцию.
  • BEFORE UPDATE
    • - NEW - доступен.
      • – Доступна возможность изменения его полей.
      • – Доступна возможность получить значения полей и полей по умолчанию.
      • – Не доступен автоинкремент.
    • - OLD – доступен.
    • - Внутри триггера можно отменить операцию.
  • AFTER UPDATE
    • - NEW – доступен.
      • – Не доступна возможность изменения полей. Они уже вставлены.
      • – Доступна возможность получить значения полей и полей по умолчанию.
      • – Доступна возможность получить автоинкременты.
    • - OLD – доступен.
    • - Внутри триггера нельзя отменить операцию.
  • BEFORE DELETE
    • - NEW – не доступен (нет ничего нового).
    • - OLD – доступен.
      • – Доступна возможность получить значения полей.
    • - Внутри триггера можно отменить операцию.
  • AFTER DELETE
    • - NEW – не доступен (по той же причине).
    • - OLD - доступен.
    • - Внутри триггера нельзя отменить операцию.

Вот такой вот список получился. Он, в принципе, логичен и понятен. Руководствуясь им, можно определить, где и когда можно использовать OLD и NEW, а главное как.

Когда мы написали триггер, то есть несколько операций, которые мы можем выполнить:

DROP TRIGGER [ IF EXISTS ] [schema_name.]trigger_name

DROP TRIGGER [ IF EXISTS ] [schema_name.]trigger_name

 

Тут мы удаляем триггер, если он есть. Далее:

SHOW TRIGGERS [ { FROM | IN } [database] ] [ LIKE 'pattern' | WHERE { условие } ];

SHOW TRIGGERS [ { FROM | IN } [database] ] [ LIKE 'pattern' | WHERE { условие } ];

 

Тут мы можем просмотреть набор триггеров для данной базы данных. Причем можно выбирать как по шаблону (LIKE), так и при помощи WHERE (внутри можно указать параметры: Trigger-имя триггера, Event-событие триггера, Table-имя таблицы, Statement-активирован триггер или нет, Timing-время наступления события).

Итак, мы рассмотрели основные тонкости и особенности при работе с триггерами в MySQL. Помните, что с ними нужно быть очень осторожными.

aabramoff.ru