Trigger sql ms: MS SQL Server и T-SQL

Триггеры

в SQL Server с примерами

Вернуться к: Учебное пособие по SQL Server для начинающих и профессионалов

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

1. Что такое триггеры в SQL Server?
2. Типы триггеров в SQL Server
3. Что такое триггеры DML в SQL Server?
4. Примеры для понимания триггера DML
5. Где создаются триггеры в SQL Server?
6. Зачем нужны триггеры DML в SQL Server?
7. Несколько примеров для понимания изложенных выше понятий

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

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

Существует два типа триггеров. Они следующие:

1. Вместо триггеров: Вместо соответствующих операций DML будут выполняться триггеры вместо. Это означает, что вместо операций DML, таких как вставка, обновление и удаление, будут выполняться триггеры вместо.
2. Триггеры After: Триггеры After срабатывают в SQL Server после действия триггера. Это означает, что как только оператор DML (такой как Insert, Update и Delete) завершит свое выполнение, этот триггер будет запущен.

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

В SQL Server доступны четыре типа триггеров. Они следующие:

1. Триггеры DML — триггеры языка манипулирования данными.
2. Триггеры DDL — триггеры языка определения данных
3. Триггеры CLR — триггеры Common Language Runtime
4. Триггеры входа в систему

DML триггеры , а остальные обсудим в наших следующих статьях.

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

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

Синтаксис для создания триггеров DML в SQL Server:

Вы можете создать триггер DML в SQL Server, используя следующий синтаксис.

Давайте подробно разберемся в синтаксисе.

1. ON TableName или ViewName: Это относится к имени таблицы или представления, для которого мы определяем триггер.
2. For/After/InsteadOf: Параметр For/After указывает, что триггер срабатывает только после выполнения операторов SQL, тогда как параметр Вместо указывает, что триггер выполняется от имени инициирующего оператора SQL. Вы не можете создать After Trigger для представлений.
3. ВСТАВКА, ОБНОВЛЕНИЕ, УДАЛЕНИЕ: ВСТАВКА, ОБНОВЛЕНИЕ, УДАЛЕНИЕ указывают, какой оператор SQL запускает этот триггер, и нам необходимо использовать по крайней мере один параметр или комбинацию нескольких параметров.

Примечание: Операторы Insert, Update и Delete также известны как Инициирующие операторы SQL, поскольку эти операторы отвечают за срабатывание триггера.

Примеры для понимания триггера DML в SQL Server:

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

Используйте приведенный ниже сценарий SQL для создания и заполнения таблицы сотрудников.

 -- Создать таблицу сотрудников
СОЗДАТЬ ТАБЛИЦУ Сотрудник
(
  Id первичный ключ,
  Имя nvarchar(30),
  Зарплата инт,
  Пол nvarchar(10),
  Идентификатор отдела
)
ИДТИ
-- Вставить данные в таблицу сотрудников
ВСТАВЬТЕ В ЦЕННОСТИ СОТРУДНИКОВ (1, «Праная», 5000, «Мужской», 3)
ВСТАВЬТЕ В ЦЕННОСТИ сотрудников (2, «Приянка», 5400, «Женщина», 2)
ВСТАВЬТЕ В ЗНАЧЕНИЯ СОТРУДНИКОВ (3, 'Анураг', 6500, 'мужчина', 1)
ВСТАВЬТЕ В ЗНАЧЕНИЯ СОТРУДНИКОВ (4, 'самбит', 4700, 'мужчина', 2)
ВСТАВЬТЕ В ЗНАЧЕНИЯ СОТРУДНИКОВ (5, «Хина», 6600, «Женщина», 3)
 

Пример: триггер For/After Insert DML в SQL Server

Итак, в основном мы хотим создать триггер For/After DML , который должен срабатывать после операции INSERT DML при выполнении на Таблица Сотрудник . Триггер должен ограничивать операцию INSERT в таблице Employee . Для этого выполните приведенный ниже запрос. Как видно из приведенного ниже запроса, этот триггер создается для таблицы Employee. Мы также указываем, что это триггер FOR для операции INSERT DML, и как часть тела триггера мы просто откатываем транзакцию, которая отменяет оператор вставки.

 СОЗДАТЬ ТРИГГЕР trInsertEmployee
ON Сотрудник
ДЛЯ ВСТАВКИ
КАК
НАЧИНАТЬ
  ПЕЧАТЬ 'ВЫ НЕ МОЖЕТЕ ВЫПОЛНИТЬ ОПЕРАЦИЮ ВСТАВКИ'
  ОТМЕНА ТРАНЗАКЦИИ
КОНЕЦ
 

Попробуем вставить следующую запись в таблицу сотрудников.
INSERT INTO Employee VALUES (6, «Saroj», 7600, «Мужской», 1)
Когда вы пытаетесь выполнить приведенный выше оператор Insert, вы получаете следующую ошибку. Сначала выполняется оператор INSERT, а затем сразу же срабатывает этот триггер и выполняется откат операции INSERT, а также печать сообщения.

Пример: для/после обновления триггера DML в SQL Server

Итак, в основном мы хотим, чтобы мы хотим создать для/после DML Trigger , который должен запустить после операции DML DML . выполнение на таблице Employee . Триггер должен ограничивать операцию UPDATE в таблице Employee. Для этого выполните приведенный ниже запрос. Как видно из приведенного ниже запроса, этот триггер создается для таблицы Employee. Мы также указываем, что это триггер FOR для операции UPDATE DML, и как часть тела триггера мы просто откатываем транзакцию, которая отменяет инструкцию UPDATE и печатает сообщение.

 СОЗДАТЬ ТРИГГЕР trUpdateEmployee
ON Сотрудник
ДЛЯ ОБНОВЛЕНИЯ
КАК
Начинать
  ПЕЧАТЬ 'ВЫ НЕ МОЖЕТЕ ВЫПОЛНИТЬ ОПЕРАЦИЮ ОБНОВЛЕНИЯ'
  ОТМЕНА ТРАНЗАКЦИИ
КОНЕЦ
 

Давайте попробуем обновить одну запись в таблице Employee
UPDATE Employee SET Salary =

WHERE Id = 1

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

Пример 3: триггеры For/After Delete DML в SQL Server

Итак, в основном, мы хотим создать триггер For/After DML , который должен срабатывать после DELETE DML 9000 6 операция, когда выполнение на таблице Employee . Триггер должен ограничивать операцию DELETE в таблице Employee. Для этого выполните приведенный ниже запрос. Как видно из приведенного ниже запроса, этот триггер создается для Сотрудник таблица. Мы также указываем, что это триггер FOR для операции DELETE DML , и как часть тела триггера мы просто откатываем транзакцию, которая откатывает операцию DELETE и печатает сообщение.

 СОЗДАТЬ ТРИГГЕР trDeleteEmployee
ON Сотрудник
ДЛЯ УДАЛЕНИЯ
КАК
НАЧИНАТЬ
  ПЕЧАТЬ «ВЫ НЕ МОЖЕТЕ ВЫПОЛНИТЬ ОПЕРАЦИЮ УДАЛЕНИЯ»
  ОТМЕНА ТРАНЗАКЦИИ
КОНЕЦ
 

Попробуем удалить одну запись из таблицы Employee
DELETE FROM Employee WHERE Id = 1
Когда мы пытаемся выполнить указанный выше оператор Delete, возникает следующая ошибка. Сначала выполняется оператор DELETE, а затем сразу же срабатывает этот триггер и выполняется откат операции DELETE, а также печать сообщения.

Где создаются триггеры в SQL Server?

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

Пример 4: триггер DML для вставки/обновления/удаления в SQL Server

Итак, в основном, мы хотим создать триггер For/After DML, который должен срабатывать после любой операции DML (INSERT, UPDATE и DELETE). при выполнении на таблице Employee. Триггер должен ограничивать все операции DML в таблице Employee. Как видно из приведенного ниже запроса, этот триггер создается для таблицы Employee. Мы также указываем, что это триггер FOR для операций INSERT, UPDATE, DELETE DML, и как часть тела триггера мы просто откатываем транзакцию, которая отменяет все операции DML и печатает сообщение.

Сначала удалите все триггеры, которые мы уже создали в таблице Employee. Чтобы удалить, вы можете использовать приведенный ниже синтаксис.
DROP Trigger TrggerName

Пример:
DROP TRIGGER trDeleteEmployee
DROP TRIGGER trInsertEmployee
DROP TRIGGER trUpdateEmployee

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

 СОЗДАТЬ ТРИГГЕР trAllDMLOperationsOnEmployee
ON Сотрудник
ДЛЯ ВСТАВКИ, ОБНОВЛЕНИЯ, УДАЛЕНИЯ
КАК
НАЧИНАТЬ
  ПЕЧАТЬ 'ВЫ НЕ МОЖЕТЕ ВЫПОЛНИТЬ ОПЕРАЦИЮ DML'
  ОТМЕНА ТРАНЗАКЦИИ
КОНЕЦ
 

Теперь вы не можете выполнять какие-либо операции DML в таблице Employee, поскольку эти операции ограничены триггером с именем trAllDMLOperationsOnEmployee.

Пример 5:

Создайте триггер, который будет ограничивать все операции DML в таблице сотрудников только в ПОНЕДЕЛЬНИК.

1. ВС ДЕНЬ = 1
2. ПОН ДЕНЬ = 2
3. ВТОР ДЕНЬ = 3
4. СРЕДА ДЕНЬ = 4 ДЕНЬ = 6
5. СУБ ДЕНЬ = 7

 ALTER TRIGGER trAllDMLOperationsOnEmployee
ON Сотрудник
ДЛЯ ВСТАВКИ, ОБНОВЛЕНИЯ, УДАЛЕНИЯ
КАК
НАЧИНАТЬ
  ЕСЛИ ЧАСТЬ ДАТЫ (DW, ПОЛУЧИТЬ ДАТУ ()) = 2
  НАЧИНАТЬ
    PRINT 'ОПЕРАЦИИ DML ОГРАНИЧЕНЫ В ПОНЕДЕЛЬНИК'
    ОТМЕНА ТРАНЗАКЦИИ
  КОНЕЦ
КОНЕЦ
 

Пример 6:

Создайте триггер, который будет ограничивать все операции DML в таблице Employee до 13:00.

 ALTER TRIGGER trAllDMLOperationsOnEmployee
ON Сотрудник
ДЛЯ ВСТАВКИ, ОБНОВЛЕНИЯ, УДАЛЕНИЯ
КАК
НАЧИНАТЬ
  ЕСЛИ ЧАСТЬ ДАТЫ (ЧЧ, ПОЛУЧИТЬ ДАТУ ()) < 13
  НАЧИНАТЬ
    ПЕЧАТЬ 'НЕДЕЙСТВИТЕЛЬНОЕ ВРЕМЯ'
    ОТМЕНА ТРАНЗАКЦИИ
  КОНЕЦ
КОНЕЦ 

Зачем нужны триггеры DML в SQL Server?

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

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

Триггер SQL | База данных учащихся

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

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

Ниже приведены основные различия между триггерами и хранимыми процедурами:

1. Триггеры нельзя вызывать или выполнять вручную.
2. Нет шансов, что триггеры получат параметры.
3. Транзакция не может быть зафиксирована или отменена внутри триггера.

Синтаксис:

создать триггер [имя_триггера] 

[до | после]  

{вставить | обновить | delete}  

для [table_name]  

[для каждой строки]  

[trigger_body] 

Объяснение синтаксиса

1. Создать триггер [t rigger_name]: создает или заменяет существующий триггер на trigger_name.
2. [до | после]: указывает, когда будет выполнен триггер.
3. {вставить | обновить | delete}: указывает операцию DML.
4. On [table_name]: указывает имя таблицы, связанной с триггером.
5. [для каждой строки]: указывает триггер на уровне строки, т. е. триггер будет выполняться для каждой затронутой строки.
6. [trigger_body]: обеспечивает выполнение операции при срабатывании триггера

Зачем мы используем триггеры?

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

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

Существуют две категории триггеров:

1. Триггер DDL
2. Триггер DML
3. Триггеры входа в систему

Триггеры DDL 

События команд языка определения данных (DDL), такие как Create_table, Create_view, drop_table, Drop_view и Alter_table, вызывают активацию триггеров DDL.

SQL Server

  создание повышенной безопасности 
  по базе данных 
для
  создать_таблицу, изменить_таблицу, удалить_таблицу 
как
  print 'вы не можете создавать, удалять и изменять вкладку  

Выходные данные:

Триггеры DML

Данные используют командные события языка манипулирования (DML), которые начинаются с Insert, Update и Delete, запускающие триггеры DML. соответствующие insert_table, update_view и delete_table.

SQL Server

 глубокое создание триггера
на  эмп 
для
вставить, обновить, удалить
  как 
напечатать 'вы не можете вставлять, обновлять и удалять эту таблицу i'
откат; 

Выход:

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

Триггеры входа

срабатывают в ответ на событие ВХОД. Когда сеанс пользователя создается с экземпляром SQL Server после завершения процесса аутентификации ведения журнала, но до установления сеанса пользователя, происходит событие LOGON. В результате сообщения инструкции PRINT и любые ошибки, сгенерированные триггером, будут отображаться в журнале ошибок SQL Server. Ошибки аутентификации препятствуют использованию триггеров входа в систему. Эти триггеры можно использовать для отслеживания действий при входе в систему или для установки ограничения на количество сеансов, которые может иметь данный вход в систему, для аудита и управления сеансами сервера.

Как SQL Server показывает триггер?

Триггер show or list полезен, когда у нас много баз данных с большим количеством таблиц. Этот запрос очень полезен, когда имена таблиц одинаковы в нескольких базах данных. Мы можем просмотреть список всех триггеров, доступных в SQL Server, с помощью следующей команды:

Синтаксис:

FROM sys.triggers, SELECT name, is_instead_of_trigger    
IF type = ‘TR’;

SQL Server Management Studio позволяет очень просто отобразить или перечислить все триггеры, доступные для любой таблицы. Следующие шаги помогут нам в этом:

Перейдите в меню Базы данных , выберите нужную базу данных, а затем разверните ее.

• Выберите меню Tables и разверните его.
• Выберите любую конкретную таблицу и разверните ее.

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

Триггер BEFORE и AFTER

Триггеры BEFORE запускают действие триггера до запуска оператора триггера. Триггеры AFTER запускают действие триггера после запуска триггерного оператора.

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

Здесь триггер срабатывает до того, как запись будет вставлена, поэтому можно использовать тег BEFORE.

Предположим, схема базы данных

Запрос

 mysql>>desc Student; 

SQL-триггер к описанию проблемы.

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

Выход

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

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

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

1. Правила объектов базы данных устанавливаются триггерами, которые отменяют изменения, если они не выполняются.
2. Триггер проверит данные и при необходимости внесет изменения.
3. Мы можем обеспечить целостность данных благодаря триггерам.
4. Данные проверяются с помощью триггеров перед вставкой или обновлением.
5. Триггеры помогают нам вести журнал записей.
6. Благодаря тому, что их не нужно компилировать при каждом запуске, триггеры повышают производительность SQL-запросов.
7. Код на стороне клиента сокращен за счет триггеров, что экономит время и трудозатраты.
8. Обслуживание триггера простое.

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

Недостатки использования триггеров в SQL Server включают следующее:

1. Только триггеры позволяют использовать расширенные проверки.
2. Используются автоматические триггеры, и пользователь не знает, когда они выполняются. Следовательно, трудно устранять проблемы, возникающие на уровне базы данных.
3. Издержки сервера базы данных могут увеличиться в результате триггеров.
4. В одном операторе CREATE TRIGGER мы можем указать одно и то же действие триггера для нескольких действий пользователя, таких как INSERT и UPDATE.
5. Для создания триггеров доступна только текущая база данных, но они по-прежнему могут ссылаться на объекты вне базы данных.

Часто задаваемые вопросы

Вопрос 1. Что такое триггер SQL?

Ответ:

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

Q2: Как работают триггеры SQL?

Ответ:

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