Триггер в sql: MS SQL Server и T-SQL

Я хочу знать, как запустить триггер в конце месяца в MSSql .

Привет я хочу вызвать хранимую процедуру или запустить триггер, когда в SQL Server 2008 будет установлено новое соединение. Спасибо

Как я могу запустить триггер BEFORE a delete в T-SQL 2005? FOR на самом деле запускает событие AFTER, и они, кажется, не имеют аргумента BEFORE в функции TRIGGER. Вместо — это не то, чего я хочу….

Я использую SQL Server 2008 R2. Я хочу знать, можно ли запустить триггер, когда я обновляю столбец с tablelA от 0 до 1? Заранее спасибо

Есть ли способ запустить какую-то функцию, например триггер, когда таблица создается в базе данных в SQL SERVER 2008?

Я использую Hive. Я реализовал в нем какую-то задачу и хотел экспортировать таблицу из Hive в SQL Server с помощью SQOOP. Когда таблица экспортируется из Hive, я хочу запустить триггер в SQL Server….

Это очень странная проблема. Я уже спрашивал об этом здесь: Как мой триггер был удален? Я переименовал свой триггер и думал, что это перестало происходить, но проблема, похоже, вернулась снова. Вот…

В PHP, SQL Server, PHP собираюсь вставить недостающие записи, для каждой я вызываю триггер. Триггер займет почти 20 минут, чтобы завершить процесс триггера, поэтому мой скрипт PHP ждет 20 минут для…

Что я должен сделать, чтобы запустить триггер CRON, чтобы запустить его сейчас один раз и следовать выражению, например триггер, который просто срабатывает каждые 5 минут. Я прошел через…

Как запустить триггер в SQL Server, если в строке 2 столбца значения совпадают? В моей таблице есть 3 столбца id , capacity , taken , и в таблице есть несколько строк. Если taken равно capacity , то…

SQL.RU | Триггеры и порядок их выполнения

До версии SQL Server 7.0, мы могли создать только три триггера на одной таблице, один INSERT, один UPDATE и один DELETE. Ситуация изменилась с выходом SQL Server 7.0. Теперь мы можем на одну таблицу задать большое количество (Ограничено числом объектов в базе данных) триггеров каждого типа.

Порядок запуска триггеров при этом никак не определен и не гарантирован. В SQL Server 2000 появилась документированная процедура sp_settriggerorder, которая позволяет изменять порядок запуска триггеров. Она позволяет указать какие триггеры AFTER срабатывают первыми или последними. Триггеры AFTER, срабатывающие между первым и последним триггерами, выполняются в неопределенном порядке.

Для демонстрации запустим скрипт:

--Тестовая таблица
if object_id( 'dbo.TestTable', 'U' ) is not null
  drop table dbo.TestTable
go
create table dbo.TestTable ( id int identity, val varchar(64) )
go

--Триггер 1
if object_id ( 'dbo. TestTrigger01', 'TR' ) is not null
  drop trigger dbo.TestTrigger01
go
create trigger dbo.TestTrigger01 on dbo.TestTable
for insert
as
if @@nestlevel > 1 return
insert into dbo.TestTable
values ( object_name( @@procid ) )
go

--Триггер 2
if object_id ( 'dbo.TestTrigger02', 'TR' ) is not null
  drop trigger dbo.TestTrigger02
go
create trigger dbo.TestTrigger02 on dbo.TestTable
for insert
as
if @@nestlevel > 1 return
insert into dbo.TestTable
values ( object_name( @@procid ) )
go

--Триггер 3
if object_id ( 'dbo.TestTrigger03', 'TR' ) is not null
  drop trigger dbo.TestTrigger03
go
create trigger dbo.TestTrigger03 on dbo.TestTable
for insert
as
if @@nestlevel > 1 return
insert into dbo.TestTable
values ( object_name( @@procid ) )
go

--Триггер 4
if object_id ( 'dbo.TestTrigger04', 'TR' ) is not null
  drop trigger dbo. TestTrigger04
go
create trigger dbo.TestTrigger04 on dbo.TestTable
for insert
as
if @@nestlevel > 1 return
insert into dbo.TestTable
values ( object_name( @@procid ) )
go

--Триггер 5
if object_id ( 'dbo.TestTrigger05', 'TR' ) is not null
  drop trigger dbo.TestTrigger05
go
create trigger dbo.TestTrigger05 on dbo.TestTable
for insert
as
if @@nestlevel > 1 return
insert into dbo.TestTable
values ( object_name( @@procid ) )
go

--Тест 1: Порядок запуска триггеров не задан
insert into dbo.TestTable
select 'Test1'
go
select * from dbo.TestTable
order by id
go

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

--Тест 2: Порядок запуска триггеров не задан, триггер №2 пересоздадим, чтобы он стал самым поздним по времени создания
--Триггер 2
if object_id ( 'dbo.TestTrigger02', 'TR' ) is not null
  drop trigger dbo.TestTrigger02
go
create trigger dbo.TestTrigger02 on dbo.TestTable
for insert
as
if @@nestlevel > 1 return
insert into dbo.TestTable
values ( object_name( @@procid ) )
go
insert into dbo.TestTable
select 'Test2'
go
select * from dbo.TestTable
order by id
go

Теория подтвердилась, но это всего лишь совпадение, и такое поведение не гарантировано и, как указано в БОЛ, без явного указания порядка, триггеры выполняются случайным образом. Но через процедуру sp_settriggerorder мы можем задать только триггеры AFTER, срабатывающие первыми или последними. Триггеры AFTER, срабатывающие между первым и последним триггерами, выполняются в неопределенном порядке. Т.е. если у нас 3 триггера на одно событие, то мы сможем гарантировать порядок срабатывания, через эту процедуру. Но если триггеров больше, как в нашем случае, то порядок будет гарантирован только для первого и последнего триггера. Для иллюстрации работы процедуры sp_settriggerorder, укажем в качестве первого триггера Триггер 2, а последним Триггер 3:

--Тест 3: Зададим порядок запуска триггеров 2 и 3
exec sp_settriggerorder 'dbo.TestTrigger02', 'first', 'insert'
go
exec sp_settriggerorder 'dbo.TestTrigger03', 'last', 'insert'
go
insert into dbo.TestTable
select 'Test3'
go
select * from dbo.TestTable
order by id
go

При попытке задать для другого триггера порядок выполнения, например первым (first), мы получим ошибку:
Msg 15130, Level 16, State 1, Procedure sp_settriggerorder, Line 163
There already exists a ‘first’ trigger for ‘insert’.

Просмотреть, задан ли порядок запуска для триггера можно с помощью функции OBJECTPROPERTY

select name
     , objectproperty( object_id, 'ExecIsFirstInsertTrigger') FirstInsertTrigger 
     , objectproperty( object_id, 'ExecIsLastInsertTrigger')  LastInsertTrigger 
from sys.triggers
where name like 'TestTrigger0%'

На что ещё стоит обратить внимание: Если один и тот же триггер необходимо назначить в качестве первого или последнего для нескольких типов инструкций, процедуру sp_settriggerorder необходимо выполнить для каждого типа инструкций. Кроме того, прежде чем триггер может быть назначен в качестве первого (First) или последнего (Last) выполняющегося триггера для того или иного типа инструкций, этот триггер должен быть сначала определен для данного типа.

Ну и последний тест, сбросим порядок запуска триггеров 2 и 3:

--Тест 4: Сбросим порядок запуска триггеров 2 и 3
exec sp_settriggerorder 'dbo. TestTrigger02', 'none', 'insert'
go
exec sp_settriggerorder 'dbo.TestTrigger03', 'none', 'insert'
go
insert into dbo.TestTable
select 'Test4'
go
select * from dbo.TestTable
order by id
go

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

Создание Триггера Для Формата Телефона По Умолчанию С Использованием T-Sql

Подобно @Jason Quinones, Transact-SQL не поддерживает триггеры FOR EACH ROW. Он также не поддерживает BEFORE, поэтому и его, и мои ответы предлагают триггер AFTER. Хотя, по сути, в T-SQL есть триггеры INSTEAD OF, которые иногда могут использоваться в качестве замены триггеров BEFORE, но тем не менее они совершенно разные (как могут предполагать имена).

Во всяком случае, триггер AFTER должен работать отлично для этой проблемы, так что вот еще одна версия Transact-SQL для вас:

CREATE TRIGGER locations_format_phone
ON locations
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE locations
SET phone = '(' + STUFF(STUFF(RIGHT('907' + RTRIM(i.  (10 chars)      (original value)

Далее, функция STUFF вызывается дважды, чтобы вставить a - и a ) в середине полученного числа. Поскольку позиции вставки связаны с неизмененной 10-значный номер, - вставляется во- первых, то ) ( в противном случае смещение позиции должны были бы быть приняты во внимание):

       #4  #7
|  |
v  v
0)  'xxxxxxxxxx'

1)  'xxxxxx-xxxx'

2)  'xxx) xxx-xxxx'

Наконец, a ( просто конкатенируется в начале.

Еще одна вещь, которую стоит иметь в виду в связи с этой проблемой, заключается в том, что триггер AFTER UPDATE обновляющий одну и ту же таблицу, может снова загореться, что является явлением, называемым триггерной рекурсией. Рекурсия триггера по умолчанию отключена, но если вы не уверены, что соответствующий параметр никогда не был изменен, вы можете проверить его текущее состояние. Запрос в отношении sys.databases — это один из способов:

SELECT is_recursive_triggers_on
FROM sys.databases
WHERE name = 'your database name'

Как is_recursive_triggers_on это bit колонка, 0 будет стоять off и 1 для on.

Вышеуказанный триггер сконструирован таким образом, который не предотвращает его неограниченную рекурсию: даже если вложенные вызовы перестанут обновлять любые строки в какой-то момент (поскольку LEN(i.phone) IN (7, 10) конечном итоге станет false) триггер по- прежнему будет вызываться. Чтобы исправить это, вы можете просто добавить эту проверку в начале:

IF NOT EXISTS (SELECT * FROM inserted)
RETURN
;

Подробнее о рекурсивных триггерах читайте здесь:

EMS SQL Manager — PostgreSQL Tools

Свойства триггера

Главные параметры триггера задаются на вкладке Trigger редактора триггеров.

В поле Name указываете имя триггера.

Enabled — установите этот флажок, если хотите, чтобы триггер был включен.

Из раскрывающегося списка For table or view выбираете таблицу или представление, для которого создаете триггер. Поле доступно для редактирования только при создании триггера.

Constraint

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

Referenced table

Укажите таблицу для создания триггера.

Deferrable

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

Check time

Выберите режим срабатывания ожидающего отложенного триггера: Immediate — немедленно, или Deferred -отложить.

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

Если выбран тип Before, то триггер срабатывает до указанного события.

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

В разделе For each укажите для каких объектов выполняется триггер.

Row — для каждой строки,

Statement — для каждого оператора.

On Event

В этом разделе выбирается событие, которое после выполнения вызывает срабатывание триггера.

Можете выбрать одно или несколько событий из предложенных: Insert, Update, Delete или Truncate.

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

Condition

В этом поле можно задать условие оператора WHEN для триггера.

Важно: Это поле доступно для сервера PostgreSQL версии 9.0 и выше.

На вкладке Function задайте основные свойства триггера.

Для создания триггера Вы можете использовать имеющуюся на сервере функцию. Для этого необходимо установить Use existing function и выбрать одну из уже созданных функций из раскрывающегося списка Function name.

Укажите Create new function, если хотите создать новую функцию. Язык создаваемой функции выберите из раскрывающегося списка Language.

Существует возможность записи и использования макросов.

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

Чтобы начать запись макроса, нажмите кнопку Record, или используйте сочетание клавиш Shift + Ctrl + R.

Чтобы остановить запись, нажмите кнопку Stop, или используйте сочетание клавиш Shift + Ctrl + R.

Для вызова записанный макрос, используйте кнопку воспроизведения Play, или используйте сочетание клавиш Shift + Ctrl + P.

На вкладке Arguments задайте аргументы функции.

Создать аргумент можно с помощью кнопки или пункта контекстного меню Add Argument.

Чтобы удалить аргумент нужно нажать на кнопку или выбрать пункт Delete Argument в контекстном меню, которое открывается при нажатии правой кнопкой мыши на нужном аргументе.

Порядок аргументов в списке можно изменить с помощью кнопок или пунктов Move Up и Move Down в контекстном меню.

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

MySQL. Остановка выполнения sql-запроса через триггер

2 марта 2010 г. MySQL Триггеры

Как приостановить выполнения SQL после триггера? То есть, при определенных условиях, описанных в триггере надо остановить выполнения SQL который и вызвал этот триггер.
Я размышлял и гуглил на эту тему, но единственное что у меня получилось, это выполнять ошибочный SQL-код, тем самым останавливать выполнение SQL кода, который выполнится после триггера.

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

Ну это все слова, а теперь покажу реализацию:

delimiter //
DROP TRIGGER IF EXISTS `customers_update_status`//
CREATE TRIGGER `customers_update_status` BEFORE UPDATE ON `customers` FOR EACH ROW BEGIN
    IF new.`status` < old.`status` THEN
        BEGIN
            INSERT INTO `customers` SET old.`status` = "STOP_SQL_QUERY";
        END;
    END IF;
END//
delimiter ;

Поле status имеет тип ENUM и у него нет значения STOP_SQL_QUERY, поэтому происходит ошибка и SQL-запрос идущий за триггером не выполняется. Это не корректное, но единственное найденное мной решение. Вообще, наверное еще лучше чтобы был не INSERT, а UPDATE. Тогда будет ошибка более корректна с виду.

зы. Кстати на официальной страничке CREATE TRIGGER в камментах приводится именно такой способ по остановке выполнения sql-запроса.

триггеров DML — SQL Server

• 14.03.2017
• 5 минут на чтение

В этой статье

Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure

Триггеры DML — это особый тип хранимой процедуры, которая автоматически вступает в силу, когда происходит событие языка управления данными (DML), которое влияет на таблицу или представление, определенное в триггере.События DML включают инструкции INSERT, UPDATE или DELETE. Триггеры DML могут использоваться для обеспечения соблюдения бизнес-правил и целостности данных, запроса других таблиц и включения сложных операторов Transact-SQL. Триггер и запускающий его оператор рассматриваются как одна транзакция, которую можно откатить из триггера. Если обнаружена серьезная ошибка (например, недостаточно места на диске), вся транзакция автоматически откатывается.

Триггерные преимущества DML

Триггеры DML похожи на ограничения в том, что они могут обеспечивать целостность объекта или целостность домена.В общем, целостность объекта всегда должна обеспечиваться на самом низком уровне с помощью индексов, которые являются частью ограничений PRIMARY KEY и UNIQUE или создаются независимо от ограничений. Целостность домена должна быть обеспечена с помощью ограничений CHECK, а ссылочная целостность (RI) должна обеспечиваться с помощью ограничений FOREIGN KEY. Триггеры DML наиболее полезны, когда функции, поддерживаемые ограничениями, не могут удовлетворить функциональные потребности приложения.

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

• Триггеры DML могут каскадировать изменения через связанные таблицы в базе данных; однако эти изменения могут быть выполнены более эффективно с использованием каскадных ограничений ссылочной целостности. Ограничения FOREIGN KEY могут проверять значение столбца только с точным совпадением со значением в другом столбце, если предложение REFERENCES не определяет каскадное ссылочное действие.

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

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

• Они могут оценивать состояние таблицы до и после изменения данных и предпринимать действия на основе этой разницы.

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

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

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

• Если в таблице триггеров существуют ограничения, они проверяются после выполнения триггера INSTEAD OF, но до выполнения триггера AFTER. Если ограничения нарушаются, действия триггера INSTEAD OF откатываются, а триггер AFTER не выполняется.

Типы триггеров DML

AFTER триггер
AFTER триггеры выполняются после выполнения действия оператора INSERT, UPDATE, MERGE или DELETE.Триггеры AFTER никогда не выполняются, если происходит нарушение ограничения; следовательно, эти триггеры не могут использоваться для какой-либо обработки, которая могла бы предотвратить нарушение ограничений. Для каждого действия INSERT, UPDATE или DELETE, указанного в инструкции MERGE, соответствующий триггер запускается для каждой операции DML.

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

В этой таблице сравнивается функциональность триггеров AFTER и INSTEAD OF.

Функция	ПОСЛЕ триггера	ВМЕСТО триггера
Применимость	Столы	Таблицы и виды
Количество в таблице или просмотре	Несколько для каждого действия запуска (UPDATE, DELETE и INSERT)	По одному на запускающее действие (ОБНОВЛЕНИЕ, УДАЛЕНИЕ и ВСТАВКА)
Каскадные ссылки	Никаких ограничений нет	Триггеры INSTEAD OF UPDATE и DELETE не разрешены для таблиц, которые являются целями каскадных ограничений ссылочной целостности.
Исполнение	После: Обработка ограничений Декларативные ссылочные действия вставлены и удалены Создание таблиц Инициирующее действие	До: Обработка ограничений Вместо: Инициирующего действия После: вставлено и удалено Создание таблиц
Порядок исполнения	Можно указать первое и последнее выполнение	Не применимо
varchar (max) , nvarchar (max) и varbinary (max) ссылки на столбцы в вставлены и удалены таблиц	Разрешено	Разрешено
текст , ntext и изображение ссылки на столбцы в вставлены и удалены таблиц	Не допускается	Разрешено

Триггеры CLR
Триггер CLR может быть триггером AFTER или INSTEAD OF. Триггер CLR также может быть триггером DDL. Вместо выполнения хранимой процедуры Transact-SQL триггер CLR выполняет один или несколько методов, написанных в управляемом коде, которые являются членами сборки, созданной в .NET Framework и загруженной в SQL Server.

См. Также

CREATE TRIGGER (Transact-SQL)
ALTER TRIGGER (Transact-SQL)
DROP TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL)
Trigger Functions (Transact-SQL)

ОБНОВЛЕНИЕ () (Transact-SQL) — SQL Server

• 15.03.2017
• 2 минуты на чтение

В этой статье

Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure

Возвращает логическое значение, указывающее, была ли сделана попытка INSERT или UPDATE для указанного столбца таблицы или представления. UPDATE () используется в любом месте тела триггера INSERT или UPDATE Transact-SQL для проверки того, должен ли триггер выполнять определенные действия.

Соглашения о синтаксисе Transact-SQL

Синтаксис

  ОБНОВЛЕНИЕ (столбец)
  

Аргументы

столбец
Имя столбца для проверки действия INSERT или UPDATE. Поскольку имя таблицы указано в предложении ON триггера, не включайте имя таблицы перед именем столбца.Столбец может быть любого типа данных, поддерживаемого SQL Server. Однако вычисляемые столбцы нельзя использовать в этом контексте.

Типы возврата

логический

Замечания

UPDATE () возвращает TRUE независимо от того, успешна ли попытка INSERT или UPDATE.

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

IF UPDATE возвращает значение TRUE в действиях INSERT, поскольку столбцы имеют вставленные явные или неявные (NULL) значения.

UPDATE ( столбец ) можно использовать в любом месте тела триггера Transact-SQL.

Если триггер применяется к столбцу, значение UPDATED будет возвращено как true или 1 , даже если значение столбца остается неизменным.Это сделано случайно, и триггер должен реализовывать бизнес-логику, которая определяет, допустима ли операция вставки / обновления / удаления.

Примеры

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

  ИСПОЛЬЗОВАТЬ AdventureWorks2012;
ИДТИ
ЕСЛИ СУЩЕСТВУЕТ (ВЫБЕРИТЕ имя ИЗ sys.objects
      ГДЕ name = 'напоминание' И тип = 'TR')
   DROP TRIGGER Человек. напоминание;
ИДТИ
СОЗДАТЬ ТРИГГЕР-напоминание
ON Person.Address
ПОСЛЕ ОБНОВЛЕНИЯ
В КАЧЕСТВЕ
ЕСЛИ (ОБНОВЛЕНИЕ (StateProvinceID) ИЛИ ОБНОВЛЕНИЕ (PostalCode))
НАЧИНАТЬ
RAISERROR (50009, 16, 10)
КОНЕЦ;
ИДТИ
- Проверьте спусковой крючок.
ОБНОВЛЕНИЕ Person.Address
УСТАНОВИТЬ PostalCode = 99999
ГДЕ PostalCode = '12345';
ИДТИ
  

См. Также

COLUMNS_UPDATED (Transact-SQL)
CREATE TRIGGER (Transact-SQL)

Изучение SQL: триггеры SQL

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

Модель

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

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

Что такое триггеры SQL?

В SQL Server триггеры — это объекты базы данных, на самом деле особый вид хранимой процедуры, которая «реагирует» на определенные действия, которые мы совершаем в базе данных. Основная идея триггеров заключается в том, что они всегда выполняют действие в случае возникновения какого-либо события. Если мы говорим о триггерах DML, эти изменения должны быть изменениями в наших данных. Разберем несколько интересных ситуаций:

• Если вы выполняете вставку в таблицу call , вы хотите обновить этот связанный клиент имеет 1 дополнительный вызов (в этом случае у нас должен быть целочисленный атрибут в таблице клиента )
• После завершения вызова вызовите (обновите вызов.значение атрибута end_time) вы хотите увеличить счетчик звонков, совершенных этим сотрудником за этот день (опять же, у нас должен быть такой атрибут в сотрудник стол)
• Когда вы пытаетесь удалить сотрудника , вы хотите проверить, есть ли у него связанные звонки. Если да, то вы предотвратить это удаление и вызвать настраиваемое исключение

Из примеров вы можете заметить, что триггеры DML — это действия, связанные с командами SQL, определенными в этих триггерах.Поскольку они похожи на хранимые процедуры, вы можете проверять значения с помощью оператора IF и т. Д. Это обеспечивает большую гибкость.

Хорошая причина для использования триггеров DML SQL — это случай, когда вы хотите убедиться, что определенный контроль должен выполняться до или после определенного оператора в определенной таблице. Это может быть тот случай, когда ваш код находится повсюду, например база данных используется различными приложениями, код написан непосредственно в приложениях, и у вас нет хорошей документации.

Типы триггеров SQL

В SQL Server у нас есть 3 группы триггеров:

• Триггеры DML (язык управления данными) — мы уже упоминали о них, и они реагируют на команды DML. Эти являются — INSERT, UPDATE и DELETE
• Триггеры DDL (язык определения данных) — как и ожидалось, триггеры этого типа должны реагировать на команды DDL, например: СОЗДАТЬ, ИЗМЕНИТЬ и УДАЛИТЬ
• Триггеры входа в систему — название говорит само за себя.Этот тип реагирует на события LOGON

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

Триггеры DML — синтаксис

Упрощенный синтаксис SQL для определения триггера выглядит следующим образом.

СОЗДАТЬ ТРИГГЕР [имя_схемы.] trigger_name ON имя_таблицы {FOR | ПОСЛЕ | INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]} AS {sql_statements}

Большая часть синтаксиса не требует пояснений. Основная идея — определить:

• Набор {sql_statements}, который должен выполняться при срабатывании триггера (определяется оставшимися параметрами)
• Мы должны определить, когда сработает триггер.Это то, что часть {ЗА | ПОСЛЕ | INSTEAD OF} делает. Если наш триггер определяется как FOR | ПОСЛЕ | INSTEAD OF триггер, чем операторы SQL в триггере, должны выполняться после всех действий запустивший этот триггер успешно запущен. Триггер INSTEAD OF должен выполнять управление и заменять оригинальное действие с действием в триггере, а FOR | ПОСЛЕ (они означают то же самое) триггер сработает дополнительные команды после завершения исходного оператора
• Часть {[INSERT] [,] [UPDATE] [,] [DELETE]} обозначает, какая команда действительно запускает этот триггер.Мы должны указать по крайней мере, один вариант, но мы можем использовать несколько, если нам это нужно

Имея это в виду, мы можем легко написать триггеры, которые будут:

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

Если вы хотите сбросить триггер, вы должны использовать:

DROP TRIGGER [schema_name.] Trigger_name;

Триггер SQL INSERT — пример

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

DROP TRIGGER IF EXISTS t_country_insert; GO СОЗДАТЬ ТРИГГЕР t_country_insert НА СТРАНЕ ВМЕСТО ВСТАВКИ НАЧАТЬ DECLARE @country_name CHAR (128); DECLARE @country_name_eng CHAR (128); DECLARE @country_code CHAR (8); ВЫБРАТЬ @country_name = country_name, @country_name_eng = country_name_eng, @country_code = country_code FROM INSERTED; ЕСЛИ @country_name IS NULL SET @country_name = @country_name_eng; IF @country_name_eng IS NULL SET @country_name_eng = @country_name; INSERT INTO country (country_name, country_name_eng, country_code) VALUES (@country_name, @country_name_eng, @country_code); КОНЕЦ;

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

Здесь я хочу выделить несколько моментов:

• Оператор INSERT запускает этот запрос и фактически заменяется (INSTEAD OF INSERT) оператором в этом спусковой крючок
• Мы определили ряд локальных переменных для хранения значений из исходной вставляемой записи (INSERTED). Этот запись специфична для триггеров и позволяет вам получить доступ к этой отдельной записи и ее значениям

• Примечание: Запись INSERTED может использоваться в триггерах SQL вставки и обновления.

• С помощью операторов IF мы проверили значения и значения SET, если они не были установлены ранее.
• В конце запроса мы выполнили оператор INSERT (тот, который заменил исходный, который запустил этот спусковой крючок)

Теперь давайте запустим команду INSERT INTO и посмотрим, что происходит в базе данных. Запустим следующие операторы:

ВЫБРАТЬ * ИЗ страны; INSERT INTO country (country_name_eng, country_code) VALUES (‘United Kingdom’, ‘UK’); ВЫБРАТЬ * ИЗ страны;

Результат на картинке ниже.

Вы можете легко заметить, что была вставлена ​​строка с id = 10. Мы не указали country_name, но триггер выполнил свою работу и заполнил это значение параметром country_name_eng.

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

Триггер SQL DELETE — пример

Теперь давайте создадим триггер, который будет срабатывать при выполнении оператора DELETE в таблице country .

DROP TRIGGER IF EXISTS t_country_delete; GO СОЗДАТЬ ТРИГГЕР t_country_delete НА СТРАНЕ ВМЕСТО УДАЛЕНИЯ НАЧАЛО DECLARE @id INT; DECLARE @count INT; ВЫБРАТЬ @id = id ИЗ УДАЛЕННОГО; SELECT @count = COUNT (*) FROM city WHERE country_id = @id; ЕСЛИ @count = 0 УДАЛИТЬ ИЗ страны, ГДЕ id = @id; ELSE THROW 51000, ‘нельзя удалить — страна указана в других таблицах’, 1; КОНЕЦ;

В отношении этого триггера стоит выделить следующее:

• Еще раз, мы выполняем действие до (вместо) фактического выполнения (INSTEAD OF DELETE)
• Мы использовали запись DELETED. Эта запись может использоваться в триггерах, связанных с оператором DELETE.

• Примечание: УДАЛЕННАЯ запись может использоваться в триггерах удаления и обновления SQL.

• Мы использовали оператор IF, чтобы определить, следует ли удалять строку. Если должно, мы выполнили оператор DELETE, и если не должно, мы выдаем исключение

Выполнение приведенного ниже оператора прошло без ошибок, поскольку в стране с id = 6 не было связанных записей.

УДАЛИТЬ ИЗ страны, ГДЕ id = 6;

Если мы запустим этот оператор, мы увидим собственное сообщение об ошибке, как показано на рисунке ниже.

УДАЛИТЬ ИЗ страны, ГДЕ id = 1;

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

Триггер обновления SQL

Я оставлю это вам в качестве практики. Так что попробуйте записать триггер UPDATE. Важная вещь, которую вы должны Знаю, что в триггере обновления можно использовать как INSERTED (после обновления), так и DELETED (перед обновлением) записи. Практически во всех случаях вам нужно будет использовать оба.

Когда использовать триггеры SQL?

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

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

Содержание

Эмиль — профессионал в области баз данных с более чем 10-летним опытом работы во всем, что связано с базами данных. В течение многих лет он работал в сфере информационных технологий и финансов, а сейчас работает фрилансером.

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

Вы можете найти его в LinkedIn

Просмотреть все сообщения Emil Drkusic

Последние сообщения Emil Drkusic (увидеть все)

Триггеры MySQL

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

MySQL поддерживает триггеры, которые вызываются в ответ на событие INSERT , UPDATE или DELETE .

Стандарт SQL определяет два типа триггеров: триггеры на уровне строк и триггеры на уровне операторов.

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

MySQL поддерживает только триггеры на уровне строк. Он не поддерживает триггеры на уровне операторов.

Преимущества триггеров

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

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

• Триггеры могут обеспечивать только расширенные проверки, но не все проверки. Для простых проверок можно использовать ограничения NOT NULL , UNIQUE , CHECK и FOREIGN KEY .
• Триггеры могут быть трудными для устранения неполадок, поскольку они выполняются автоматически в базе данных, что может быть незаметно для клиентских приложений.
• Триггеры могут увеличивать нагрузку на сервер MySQL.

Управление триггерами MySQL

Различные типы триггеров в SQL Server

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

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

В SQL Server мы можем создать четыре типа триггеров, триггеры языка определения данных (DDL), триггеры языка манипулирования данными (DML), триггеры CLR и триггеры входа в систему.

1. DDL-триггеры

   В SQL Server мы можем создавать триггеры для операторов DDL (например, CREATE, ALTER и DROP) и определенных системных хранимых процедур, которые выполняют операции, подобные DDL.

   Пример: Если вы собираетесь выполнить оператор CREATE LOGIN или хранимую процедуру sp_addlogin для создания пользователя для входа, то оба они могут выполнять / запускать триггер DDL, который вы можете создать в событии CREATE_LOGIN SQL Server.

   Мы можем использовать только предложение FOR / AFTER в триггерах DDL, а не предложение INSTEAD OF означает, что мы можем использовать только предложение After Trigger для операторов DDL.

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

2. Триггеры DML

   В SQL Server мы можем создавать триггеры для операторов DML (таких как INSERT, UPDATE и DELETE) и хранимых процедур, которые выполняют операции, подобные DML.Триггеры DML бывают двух типов:

   1. После триггера (с использованием FOR / AFTER CLAUSE)

      Триггер этого типа срабатывает после того, как SQL Server завершает выполнение действия, которое его запустило.

      Пример: Если вы вставляете запись / строку в таблицу, то триггер, связанный / связанный с событием вставки в этой таблице, сработает только после того, как строка пройдет все ограничения, такие как ограничение первичного ключа и некоторые правила. Если вставка записи / строки завершается неудачно, SQL Server не запускает триггер After.

   2. Вместо триггера (с использованием INSTEAD OF CLAUSE)

      Этот тип триггера срабатывает до того, как SQL Server начнет выполнение действия, которое его запустило. Это отличается от триггера AFTER, который срабатывает после действия, вызвавшего его срабатывание. У нас может быть триггер INSTEAD OF insert / update / delete для таблицы, которая успешно выполнена, но не включает фактическую вставку / обновление / удаление в таблицу.

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

3. Триггеры CLR

   Триггеры CLR — это особый тип триггеров, основанный на CLR (Common Language Runtime) в платформе .NET. Интеграция триггеров CLR была введена в SQL Server 2008 и позволяет кодировать триггеры на одном из языков .NET, например C #, Visual Basic и F #.

   Мы закодировали объекты (например, триггер) в CLR, которые требуют тяжелых вычислений или нуждаются в ссылках на объекты вне SQL Server. Мы можем писать код для триггеров DDL и DML, используя поддерживаемый язык CLR, такой как C #, Visual Basic и F #. Я расскажу о триггере CLR позже.

4. Триггеры входа в систему

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

   Синтаксис для триггера входа в систему

    CREATE TRIGGER trigger_name
   НА ВСЕХ СЕРВЕРАХ
   [С ШИФРОВАНИЕМ]
   {FOR | AFTER} ВХОД
   В КАЧЕСТВЕ
   sql_statement [1 . .. n]
    

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

 CREATE TRIGGER trigger_name
ON {table | view}
[С ШИФРОВАНИЕМ | ВЫПОЛНИТЬ КАК]
{FOR | AFTER | INSTEAD OF} {[CREATE | ALTER | DROP | INSERT | UPDATE | DELETE]}
[НЕ ДЛЯ ПОВТОРЕНИЯ]
В КАЧЕСТВЕ
sql_statement [1...n] 

1. имя-триггера

   Это имя триггера. Он должен соответствовать правилам для идентификаторов в Sql Server.

2. таблица | представление

   Это таблица / представление, для которого должен быть создан триггер.

3. ШИФРОВАНИЕ

   Эта опция не является обязательной. Если указана эта опция, исходный текст оператора CREATE TRIGGER будет зашифрован.

4. EXECUTE AS

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

5. FOR / AFTER

   FOR / AFTER указывает, что триггер — After Trigger. AFTER — значение по умолчанию, если указано единственное ключевое слово FOR. Триггеры AFTER не могут быть определены в представлениях .

6. INSTEAD OF

   INSTEAD OF указывает, что запускается вместо запуска.

7. CREATE | ALTER | DROP | INSERT | UPDATE | DELETE

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

8. НЕ ДЛЯ РЕПЛИКАЦИИ

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

9. AS

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

10. sql_statement

    Это условия и действия триггера. Действия триггера, указанные в операторах T-SQL.

Примечание

1. Имя триггера должно соответствовать правилам для идентификаторов.

2. Триггер DML может состоять из любых операторов T-SQL, кроме операторов CREATE DATABASE, ALTER DATABASE, DROP DATABASE, LOAD DATABASE, LOAD LOG, RECONFIGURE, RESTORE DATABASE и RESTORE LOG.

3. Нельзя создавать триггеры для системных таблиц или динамических управленческих представлений. Более того, инструкция TRUNCATE TABLE не запускает триггер, потому что эта операция не регистрирует удаления отдельных строк.

4. Если вы используете опцию DATABASE, областью вашего DDL-триггера будет текущая база данных. Если вы используете опцию ALL SERVER, область действия ваших DDL-триггеров переходит на текущий сервер.

5. Триггеры AFTER не могут быть определены для представлений.

6. ПОСЛЕ — значение по умолчанию, если указано единственное ключевое слово FOR.

Резюме

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

Поделиться Артикул

Пройдите бесплатные тесты, чтобы оценить свои навыки!

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

Список триггеров в базе данных SQL Server

Запрос ниже перечисляет триггеры в базе данных с их подробностями.

Запрос

  выберите trg.name как trigger_name,
    schema_name (tab.schema_id) + '.'. + tab.name как [таблица],
    случай, когда is_instead_of_trigger = 1, тогда 'Вместо'
        иначе "После" заканчивается как [активация],
    (случай, когда objectproperty (trg.object_id, 'ExecIsUpdateTrigger') = 1
                затем "Обновить" иначе "конец"
    + случай, когда свойство объекта (trg.object_id, 'ExecIsDeleteTrigger') = 1
                затем "Удалить" еще "конец
    + случай, когда свойство объекта (trg.object_id, 'ExecIsInsertTrigger') = 1
                затем 'Insert' else 'end
    ) как [событие],
    случай, когда trg. parent_class = 1, то 'Триггер таблицы'
        когда trg.parent_class = 0, то "Триггер базы данных"
    конец [класс],
    случай, когда trg. [type] = 'TA', затем 'Assembly (CLR) trigger'
        когда trg. [type] = 'TR', затем 'SQL trigger'
        else '' заканчиваться как [тип],
    случай, когда is_disabled = 1, затем 'Disabled'
        иначе "Активный" заканчивается как [статус],
    object_definition (trg.object_id) как [определение]
из сис.триггеры trg
    слева присоединиться к вкладке sys.objects
        на trg.parent_id = tab.object_id
заказ по trg.name;
  

Колонны

• trigger_name — имя триггера
• таблица — имя таблицы триггеров (для триггеров таблиц) с именем схемы
• активация — время активации триггера: После или Вместо
• событие — специфическая операция SQL: Вставить , Обновить или Удалить
• class — класс триггера:
  • Триггер базы данных — для триггеров DDL
  • Триггер таблицы — для объекта или столбца для триггеров DML
• type — тип объекта:
  • Сборка (CLR) триггера
  • Триггер SQL
• status — статус триггера
• определение — SQL определение триггера

Рядов

• Одна строка представляет один триггер
• Объем строк: все триггеры столбцов, таблиц и баз данных (DDL) в базе данных
• По заказу имя триггера

Пример результатов

Простая вставка, обновление и удаление триггеров в SQL Server с примером

В этой статье я объясню на простых примерах, как писать триггеры Insert, Update и Delete в SQL Server.

Это руководство применимо для всех версий SQL Server, то есть 2005, 2008, 2012, 2014 и т. Д.

База данных

Я использовал следующую таблицу Customers со следующей схемой.

Я уже вставил несколько записей в таблицу.

Ниже приведена таблица CustomerLogs , которая будет использоваться для регистрации действий триггера.

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

Триггеры

Триггеры — это операции с базой данных, которые автоматически выполняются, когда такое действие, как Вставка, Обновление или Удаление, выполняется над таблицей или представлением в базе данных.

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

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

Есть два типа триггеров. после и вместо триггеров .

После триггеров

Эти триггеры выполняются после выполнения такого действия, как Вставка, Обновление или Удаление.

Вместо триггеров

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

После триггеров

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

Вставной триггер

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

Следующий триггер получает CustomerId вставленной записи, и полученное значение вставляется в таблицу CustomerLogs.

СОЗДАТЬ ТРИГГЕР [dbo]. [Customer_INSERT]

ПО [dbo]. [Клиенты]

ПОСЛЕ ВСТАВКИ

AS

НАЧАТЬ

УСТАНОВИТЬ NOCOUNT ON;

ОБЪЯВИТЬ @CustomerId INT

ВЫБЕРИТЕ @CustomerId = INSERTED.CustomerId

ИЗ ВСТАВЛЕННОЙ

ВСТАВИТЬ CustomerLogs

ЗНАЧЕНИЯ (@CustomerId, ‘вставлено’)

КОНЕЦ

Триггер обновления

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

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

Функция UPDATE вернет TRUE для столбца, если его значение было обновлено, в противном случае она вернет false.

Наконец, в зависимости от того, какой столбец записи был обновлен, запись (содержащая CustomerId и соответствующее действие) вставляется в таблицу CustomerLogs.

СОЗДАТЬ ТРИГГЕР [dbo]. [Customer_UPDATE]

ПО [dbo]. [Клиенты]

ПОСЛЕ ОБНОВЛЕНИЯ

AS

НАЧАТЬ

УСТАНОВИТЬ NOCOUNT ON;

ОБЪЯВИТЬ @CustomerId INT

ОБЪЯВИТЬ @Action VARCHAR (50)

ВЫБЕРИТЕ @CustomerId = INSERTED.CustomerId

ИЗ ВСТАВЛЕННОЙ

ЕСЛИ ОБНОВИТЬ (Имя)

НАЧАТЬ

SET @Action = ‘Обновлено имя’

КОНЕЦ

ЕСЛИ ОБНОВИТЬ (Страна)

НАЧАТЬ

SET @Action = ‘Обновленная страна’

КОНЕЦ

ВСТАВИТЬ CustomerLogs

ЗНАЧЕНИЯ (@CustomerId, @Action)

КОНЕЦ

Удалить триггер

Ниже приведен пример триггера после удаления.При каждом удалении строки в таблице клиентов будет выполняться следующий триггер. Удаленная запись доступна в таблице DELETED .

Следующий триггер извлекает CustomerId удаленной записи, и полученное значение вставляется в таблицу CustomerLogs.

СОЗДАТЬ ТРИГГЕР [dbo]. [Customer_DELETE]

ПО [dbo]. [Клиенты]

ПОСЛЕ УДАЛЕНИЯ

AS

НАЧАТЬ

УСТАНОВИТЬ NOCOUNT ON;

ОБЪЯВИТЬ @CustomerId INT

ВЫБРАТЬ @CustomerId = УДАЛЕНО.CustomerId

ИЗ УДАЛЕНО

ВСТАВИТЬ CustomerLogs

ЗНАЧЕНИЯ (@CustomerId, «Удалено»)

КОНЕЦ

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

Вместо триггеров

Ниже приведен пример триггера «Вместо удаления».Каждый раз, когда кто-либо пытается удалить строку из таблицы Customers, выполняется следующий триггер.

Внутри триггера я добавил условие, что если запись имеет значение CustomerId 2, то такая запись не должна удаляться и должна возникать ошибка. Также запись вставляется в таблицу CustomerLogs.

Если значение CustomerId не равно 2, то выполняется запрос на удаление, который удаляет запись безвозвратно, и запись вставляется в таблицу CustomerLogs.

СОЗДАТЬ ТРИГГЕР [dbo].[Customer_InsteadOfDELETE]

ПО [dbo]. [Клиенты]

ВМЕСТО УДАЛЕНИЯ

AS

НАЧАТЬ

УСТАНОВИТЬ NOCOUNT ON;

ОБЪЯВИТЬ @CustomerId INT

ВЫБЕРИТЕ @CustomerId = DELETED.CustomerId

ИЗ УДАЛЕНО

ЕСЛИ @CustomerId = 2

НАЧАТЬ

RAISERROR (запись «Мудассар-хана» не может быть удалена », 16, 1)

ОТКАТ

ВСТАВИТЬ CustomerLogs

VALUES (@CustomerId, ‘Запись не может быть удалена.’)

КОНЕЦ

ELSE

НАЧАТЬ

УДАЛИТЬ ОТ клиентов

ГДЕ CustomerId = @CustomerId

ВСТАВИТЬ CustomerLogs

ЗНАЧЕНИЯ (@CustomerId, «Вместо удаления»)

КОНЕЦ

КОНЕЦ

Следующее сообщение об ошибке отображается при удалении записи с CustomerId 2.

На следующем снимке экрана показана таблица журнала после выполнения триггера вместо триггера.

No related posts.