Создать триггер в MySQL

Создать триггер в MySQL Хостинг
На чтение 33 мин Просмотров 52 Опубликовано

В данной статье мы только слегка задели айсберг триггеров в MySQL. Они позволяют переносить правила работы с данными с уровня логики приложения на уровень данных.

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

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

Содержание
  1. Структура таблиц, инструменты и заметки
  2. Explanation of Syntax
  3. Введение что такое триггер
  4. Advantages of triggers
  5. Disadvantages of triggers
  6. Managing MySQL triggers
  7. Введение в MySQL AFTER INSERT триггеры
  8. Подготовка таблицы образцов
  9. Создание AFTER INSERT пример триггера
  10. Тестирование MySQL AFTER INSERT триггер
  11. Введение в MySQL CREATE TRIGGER заявление
  12. Преимущество триггеров
  13. Почему мы используем триггеры?
  14. # Типы триггеров
  15. # Время
  16. # Инициирующее событие
  17. # Пример триггера «Перед вставкой»
  18. # Пример триггера до обновления
  19. # Пример триггера после удаления
  20. # Синтаксис
  21. # ЗА КАЖДЫЙ РЯД
  22. # СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ТРИГГЕР
  23. # Базовый триггер
  24. Недостаток триггеров
  25. ДО и ПОСЛЕ триггера
  26. Чудесный простой триггер
  27. Целостность данных
  28. How does SQL Server Show Trigger?
  29. Different Trigger Types in SQL Server
  30. DDL Triggers 
  31. DML Triggers
  32. Logon Triggers
  33. Более сложный триггер
  34. Frequently Asked Questions
  35. Q1: What is an SQL trigger?
  36. Q2: How do SQL triggers work? 
  37. Q3: What are the benefits of using SQL triggers? 

Структура таблиц, инструменты и заметки

В статье мы будем работать с выдуманной системой для корзины покупок, каждый элемент которой будет иметь цену. Структура данных будет проста, насколько это возможно с целью продемонстрировать процедуры работы с триггерами. Наименования таблиц и столбцов придуманы с целью облегчения понимания, а не для реальной работы. Также используется TIMESTAMPS для облегчения учебного процесса. Таблицы имеют имена carts, cart_items, cart_log, items, items_cost.

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

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

Чтобы изменить разделитель, нужно выполнить команду перед командой триггера: 

   DELIMITER $

А после команды триггера надо ввести: 

    DELIMITER ;

A trigger is a stored procedure in a database that automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when specific table columns are updated in simple words a trigger is a collection of SQL
statements with particular names that are stored in system memory. It belongs to a specific class of stored procedures that are automatically invoked in response to database server events. Every trigger has a table attached to it.

Because a trigger cannot be called directly, unlike a stored procedure, it is referred to as a special procedure. A trigger is automatically called whenever a data modification event against a table takes place, which is the main distinction between a trigger and a procedure. On the other hand, a stored procedure must be called directly.

  1. Triggers cannot be manually invoked or executed.
  2. There is no chance that triggers will receive parameters.
  3. A transaction cannot be committed or rolled back inside a trigger.

Explanation of Syntax

  1. Create trigger [trigger_name]: Creates or replaces an existing trigger with the trigger_name.
  2. [before | after]: This specifies when the trigger will be executed.
  3. {insert | update | delete}: This specifies the DML operation.
  4. On [table_name]: This specifies the name of the table associated with the trigger.
  5. [for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for each affected row.
  6. [trigger_body]: This provides the operation to be performed as the trigger is fired

Введение что такое триггер

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

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

Триггеры были введены в MySQL начиная с версии 5.0.2. Синтаксис триггеров несколько чужероден. MySQL использует стандарт ANSI SQL:2003 для процедур и других функций. Если вы работаете с языками программирования, то понять его будет не сложно. Спецификация отсутствует в свободном доступе, поэтому мы постараемся использовать простые структуры и будем объяснять, что происходит в триггере. Будут использоваться такие же структуры, как и в любом языке программирования.

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

In MySQL, a trigger is a stored program invoked automatically in response to an event such as insert
, update
, or delete
that occurs in the associated table. For example, you can define a trigger that is invoked automatically before a new row is inserted into a table.

MySQL supports triggers that are invoked in response to the INSERT
, UPDATE
or DELETE
event.

The SQL standard defines two types of triggers: row-level triggers and statement-level triggers.

  • A row-level trigger is activated for each row that is inserted, updated, or deleted. For example, if a table has 100 rows inserted, updated, or deleted, the trigger is automatically invoked 100 times for the 100 rows affected.
  • A statement-level trigger is executed once for each transaction regardless of how many rows are inserted, updated, or deleted.

MySQL supports only row-level triggers. It doesn’t support statement-level triggers.

Создать триггер в MySQL

Advantages of triggers

  • Triggers provide another way to check the integrity of data.
  • Triggers handle errors from the database layer.
  • Triggers give an alternative way to run scheduled tasks
    . By using triggers, you don’t have to wait for the scheduled events
    to run because the triggers are invoked automatically before
    or after
    a change is made to the data in a table.
  • Triggers can be useful for auditing the data changes in tables.

Disadvantages of triggers

  • Triggers can only provide extended validations, not all validations. For simple validations, you can use the NOT NULL
    , UNIQUE
    , CHECK
    and FOREIGN KEY
    constraints.
  • Triggers can be difficult to troubleshoot because they execute automatically in the database, which may not be visible to the client applications.
  • Triggers may increase the overhead of the MySQL server.

Managing MySQL triggers

  • Create triggers
      – describe steps of how to create a trigger in MySQL.
  • Drop triggers
    – show you how to drop a trigger.
  • Create a BEFORE INSERT trigger
    – show you how to create a BEFORE INSERT
    trigger to maintain a summary table from another table.
  • Create an AFTER INSERT trigger
    – describe how to create an AFTER INSERT
    trigger to insert data into a table after inserting data into another table.
  • Create a BEFORE UPDATE trigger
    – learn how to create a BEFORE UPDATE
    trigger that validates data before it is updated to the table.
  • Create an AFTER UPDATE trigger
    – show you how to create an AFTER UPDATE
    trigger to log the changes of data in a table.
  • Create a BEFORE DELETE trigger
    – show how to create a BEFORE DELETE
    trigger.
  • Create an AFTER DELETE trigger
    – describe how to create an AFTER DELETE
    trigger.
  • Создание нескольких триггеров для таблицы с одним и тем же событием триггера и временем
    – MySQL 8.0 позволяет вам определять несколько триггеров для таблицы, которые имеют одно и то же событие триггера и время.
  • Показать триггеры
    – список триггеров в базе данных, таблица по определенным шаблонам.

Резюме
: в этом уроке вы узнаете, как создать MySQL AFTER INSERT
триггер для вставки данных в таблицу после вставки данных в другую таблицу.

Читайте также:  Dnsmasq-centos7

Введение в MySQL AFTER INSERT
триггеры

MySQL AFTER INSERT
триггеры автоматически вызываются после того, как в таблице происходит событие вставки.

    trigger_name
     
     table_name   
        trigger_body

  
    SQL (язык структурированных запросов)

В этом синтаксисе:

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

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

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

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

DELIMITER $

 имя_триггера
 
 имя_таблицы

 
$

РАЗДЕЛИТЕЛЬ ;
   SQL (язык структурированных запросов)

В AFTER INSERT
триггер, вы можете получить доступ к NEW
значения, но вы не можете их изменить. Кроме того, вы не можете получить доступ к OLD
ценности, потому что нет OLD
на INSERT
триггеры.

MySQL AFTER INSERT Trigger

Подготовка таблицы образцов

Сначала создайте новую таблицу
называется members
:

      members;

  members (
      AUTO_INCREMENT,
     ()  ,
    email (),
    birthDate ,
    PRIMARY  ()
);

  
    SQL (язык структурированных запросов)

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

      reminders;

  reminders (
      AUTO_INCREMENT,
    memberId ,
    message ()  ,
    PRIMARY  ( , memberId)
);

  
    SQL (язык структурированных запросов)

Создание AFTER INSERT
пример триггера

DELIMITER $

 after_members_insert
 
 члены

 НОВАЯ.дата рождения
 напоминания (memberId, сообщение)
 (new.id,(, NEW.name,  ', пожалуйста, обновите дату своего рождения.' 
));
 ;
$

РАЗДЕЛИТЕЛЬ ;
   SQL (язык структурированных запросов)

В этом триггере:

Во-первых, название триггера after_members_insert
указано в CREATE TRIGGER
пункт:

    after_members_insert

  
    SQL (язык структурированных запросов)

Во-вторых, триггерное событие:

   SQL (язык структурированных запросов)

В-третьих, таблица, с которой связан триггер: members
таблица:

  ON members FOR EACH ROW

  
    SQL (язык структурированных запросов)

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

Тестирование MySQL AFTER INSERT
триггер

Сначала вставьте две строки
в members
таблица:

   SQL (язык структурированных запросов)

Во-вторых, данные запроса
из members
таблица:

   *  members;    

  
    SQL (язык структурированных запросов)
MySQL AFTER INSERT Trigger example

В-третьих, запросите данные из reminders
таблица:

   *  reminders;    

  
    SQL (язык структурированных запросов)
MySQL AFTER INSERT Trigger Output

Мы вставили две строки в members
стол. Однако только первая строка со значением даты рождения NULL
, поэтому триггер вставил только одну строку в reminders
стол.

В этом руководстве вы узнали, как создать MySQL AFTER INSERT
триггер для вставки данных в таблицу после вставки данных в другую таблицу.

Было ли это руководство полезным?

Резюме
: в этом руководстве вы узнаете, как использовать MySQL CREATE TRIGGER
Оператор для создания триггера в базе данных .

Введение в MySQL CREATE TRIGGER
заявление

CREATE TRIGGER
Оператор создает новый триггер. Вот основной синтаксис CREATE TRIGGER
утверждение:

   SQL (язык структурированных запросов)

В этом синтаксисе:

  • Сначала укажите имя триггера, который вы хотите создать после CREATE TRIGGER
    ключевые слова. Обратите внимание, что имя триггера должно быть уникальным в базе данных.
  • Затем укажите время действия триггера, которое может быть BEFORE
    или AFTER
    что указывает на то, что триггер вызывается до или после изменения каждой строки.
  • Затем укажите операцию, активирующую триггер, которая может быть INSERT
    , ОБНОВЛЕНИЕ
    или УДАЛИТЬ
    .
  • После этого укажите имя таблицы, к которой относится триггер после ON
    ключевое слово.
  • Наконец, укажите оператор, который будет выполняться при срабатывании триггера. Если вы хотите выполнить несколько операторов, вы используете BEGIN END
    составное заявление.

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

Чтобы различать значения столбцов BEFORE
и AFTER
DML выстрелил, вы используете NEW
и OLD
модификаторы.

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

Давайте начнем создавать триггер в MySQL для регистрации изменений employees
стол.

Создать триггер в MySQL

Сначала создайте новую таблицу
по имени employees_audit
сохранить изменения в employees
таблица:

    employees_audit (
      AUTO_INCREMENT PRIMARY ,
    employeeNumber   ,
    lastname ()  ,
    changedat DATETIME  ,
     ()  
);
  
    SQL (язык структурированных запросов)

Затем создайте BEFORE UPDATE
триггер, который вызывается перед внесением изменений в employees
стол.

    before_employee_update 
       employees
       
   employees_audit
   = ,
     employeeNumber = OLD.employeeNumber,
     lastname = OLD.lastname,
     changedat = ();
  
    SQL (язык структурированных запросов)

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

Затем отобразите все триггеры в текущей базе данных с помощью SHOW TRIGGERS
заявление:

   SQL (язык структурированных запросов)
MySQL CREATE TRIGGER - show triggers

Кроме того, если вы посмотрите на схему с использованием MySQL Workbench под сотрудников > триггеры
, вы увидите  before_employee_update
триггер, как показано на скриншоте ниже:

MySQL create trigger example

После этого обновите строку в employees
таблица:

   employees 
 
    lastName = 

    employeeNumber = ;
  
    SQL (язык структурированных запросов)

Наконец, запросите employees_audit
таблица, чтобы проверить, был ли триггер запущен UPDATE
утверждение:

   *  employees_audit;
  
    SQL (язык структурированных запросов)
MySQL CREATE TRIGGER example

Как видно из вывода, триггер сработал автоматически и вставил новую строку в employees_audit
стол.

В этом руководстве вы узнали, как использовать MySQL CREATE TRIGGER
Оператор для создания нового триггера в базе данных.

Было ли это руководство полезным?

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

Пример –

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

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

Мы можем определить 6 типов триггеров для каждой таблицы: 

  1. ПОСЛЕ ВСТАВКИ
    активируется после вставки данных в таблицу.
  2. ПОСЛЕ ОБНОВЛЕНИЯ:
    активируется после изменения данных в таблице.
  3. ПОСЛЕ УДАЛЕНИЯ:
    активируется после удаления/удаления данных из таблицы.
  4. ПЕРЕД ВСТАВКОЙ:
    активируется перед вставкой данных в таблицу.
  5. ДО ОБНОВЛЕНИЯ:
    активируется перед изменением данных в таблице.
  6. ПЕРЕД УДАЛЕНИЕМ:
    активируется перед удалением/удалением данных из таблицы.

Примеры реализации триггеров: 

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

 разделитель $
СОЗДАЙТЕ TRIGGER Check_age ПЕРЕД ВСТАВКОЙ НА сотрудника
ДЛЯ КАЖДОГО РЯДА
НАЧИНАТЬ
ЕСЛИ NEW.age < 25 ТО
СИГНАЛ SQLSTATE '45000'
УСТАНОВИТЕ MESSAGE_TEXT = 'ОШИБКА:
 ВОЗРАСТ ДОЛЖЕН БЫТЬ НЕ МЕНЕЕ 25 ЛЕТ!';
КОНЕЦ ЕСЛИ;
КОНЕЦ; $
разделитель;

Пояснение:
Всякий раз, когда мы хотим вставить какой-либо кортеж в таблицу «employee», то перед вставкой этого кортежа в таблицу будет выполнен триггер с именем «Check_age». Этот триггер будет проверять возрастной атрибут. Если оно больше 25, то этот кортеж будет вставлен в кортеж, в противном случае будет напечатано сообщение об ошибке «ОШИБКА: ВОЗРАСТ ДОЛЖЕН БЫТЬ НЕ МЕНЕЕ 25 ЛЕТ!»

2. Создать триггер, который будет срабатывать до удаления в таблице сотрудников и создать дублирующую копию записи в другой таблице employee_backup.

Прежде чем писать триггер, нам нужно создать таблицу employee_backup.

 создать таблицу employee_backup (employee_no int,
 имя_сотрудника varchar
, задание varchar,
 дата найма, оклад,
 первичный ключ(employee_no)); 
 разделитель $
СОЗДАТЬ TRIGGER Backup ПЕРЕД УДАЛЕНИЕМ НА сотрудника
ДЛЯ КАЖДОГО РЯДА
НАЧИНАТЬ
ВСТАВИТЬ В employee_backup
ЗНАЧЕНИЯ (OLD.employee_no, OLD.name,
 СТАРАЯ.работа, СТАРАЯ.дата приема на работу, СТАРАЯ.зарплата);
КОНЕЦ; $
разделитель;

Пояснение:
Мы хотим создать резервную таблицу, в которой будут храниться значения тех сотрудников, которые больше не являются сотрудниками учреждения. Итак, мы создаем триггер с именем Backup, который будет выполняться перед удалением любого кортежа из таблицы employee. Перед удалением значения всех атрибутов таблицы employee будут храниться в таблице employee_backup.

3. Напишите триггер для подсчета количества новых кортежей, вставленных с помощью каждого оператора вставки. 

 Объявить count int
Установить счетчик=0;
разделитель $
СОЗДАТЬ ТРИГГЕР
 ПОСЛЕ ВСТАВКИ НА сотрудника
ДЛЯ КАЖДОГО РЯДА
НАЧИНАТЬ
УСТАНОВИТЬ количество = количество + 1;
КОНЕЦ; $
разделитель;

Пояснение:
Мы хотим отслеживать количество новых кортежей в таблице сотрудников. Для этого мы сначала создаем переменную count и инициализируем ее значением 0. После этого мы создаем триггер с именем Count_tuples, который будет увеличивать значение count после вставки любого нового кортежа в таблицу employee.

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

В MySQL существует 6 различных типов триггеров:

1. До запуска обновления:

Как следует из названия, это триггер, который срабатывает перед вызовом обновления. Если мы напишем оператор обновления, то действия триггера будут выполнены до реализации обновления.

 создать таблицу клиентов (целочисленный первичный ключ acc_no,
 cust_name varchar
,
 доступный_баланс десятичный);
создать таблицу mini_statement (целое число acc_no,
 доступный_баланс десятичный,
 внешний ключ (acc_no) ссылается на клиента (acc_no) при удалении каскада);

Вставка в них значений:

 вставить в значения клиентов (1000, "Фанни", 7000);
вставить в значения клиентов (1001, "Питер", 12000);

Триггер для вставки (старых) значений в запись mini_statement (включая номер счета и доступный баланс в качестве параметров) перед обновлением любой записи в записи/таблице клиента:

 разделитель //
создать триггер update_cus
 -> перед обновлением на клиенте
 -> для каждой строки
 -> начать
 -> вставить в mini_statement значения (old.acc_no, old.avail_balance);
 -> конец; //

Внесение обновлений для вызова триггера:

 разделитель;
обновить набор клиентов avail_balance = avail_balance + 3000, где acc_no = 1001;
обновить набор клиентов avail_balance = avail_balance + 3000, где acc_no = 1000; 
 выберите *из мини_оператора;
+--------+----------------+
| acc_no | доступный_баланс |
+--------+----------------+
| 1001 | 12000 |
| 1000 | 7000 |
+--------+----------------+
2 ряда в сете (0,0007 сек)

2. Триггер после обновления:

Как следует из названия, этот триггер вызывается после обновления. (т. е. он реализуется после выполнения оператора обновления).

Читайте также:  Зарубежный Хостинг в США - Рейтинг, Отзывы, Цены

Пример:

Создаем еще одну таблицу:

 создать таблицу micro_statement (целое число acc_no,
 доступный_баланс десятичный,
 внешний ключ (acc_no) ссылается на клиента (acc_no) при удалении каскада);

Вставьте другое значение в клиент:

 вставить в значения клиентов (1002, "Дворник", 4500);
Запрос выполнен успешно, затронута 1 строка (0,0786 с)

Триггер для вставки (новых) значений номера счета и доступного баланса в запись micro_statement после того, как произошло обновление:

 разделитель //
создать триггер update_after
 -> после обновления на клиенте
 -> для каждой строки
 -> начать
 -> вставить в micro_statement значения(new.acc_no, new.avail_balance);
 -> конец; //

Обновление триггера вызова:

 разделитель ;
обновить набор клиентов avail_balance = avail_balance + 1500, где acc_no = 1002; 
 выберите *из микро_оператора;
+--------+----------------+
| acc_no | доступный_баланс |
+--------+----------------+
| 1002 | 6000 |
+--------+----------------+
1 ряд в наборе (0,0007 сек)

3. Перед триггером вставки:

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

 создать контакты таблицы (contact_id INT

NOT NULL AUTO_INCREMENT,
last_name VARCHAR

NOT NULL, first_name VARCHAR ,
-> ДАТА дня рождения, created_date ДАТА,
созданный VARCHAR

,
ОГРАНИЧЕНИЕ contact_pk ПЕРВИЧНЫЙ КЛЮЧ (contact_id));

 разделитель //
создать триггер contact_before_insert
 -> перед вставкой
 -> по контактам для каждой строки
 -> начать
 -> ОБЪЯВИТЬ vUser varchar
;
 ->
 -> -- Найдите имя пользователя, выполняющего INSERT в таблицу
 -> выберите USER() в vUser;
 ->
 -> -- Обновить поле create_date до текущей системной даты
 -> УСТАНОВИТЬ NEW.created_date = SYSDATE();
 ->
 -> -- Обновить поле created_by до имени пользователя, выполняющего INSERT
 -> УСТАНОВИТЬ NEW.created_by = vUser;
 -> конец; // 
 Создание вставки для активации триггера:
 разделитель;
вставить в контакты значения (1, "Ньютон", "Энигма",
 str_to_date("19-08-1999", "%d-%m-%Y"),
 str_to_date("17-03-2018", "%d-%m-%Y"), "xyz"); 
 выбрать *из контактов;
+------------+-----------+------------+----------- -+--------------+----------------+
| контакт_идентификатор | фамилия | имя_имя | день рождения | дата_создания | created_by |
+------------+-----------+------------+----------- -+--------------+----------------+
| 1 | Ньютон | Энигма | 19 августа 1999 г. | 2019-05-11 | корень@локальный |
+------------+-----------+------------+----------- -+--------------+----------------+ 
  4. После триггера вставки: 
 
 Как следует из названия, этот триггер вызывается после реализации вставки.
 создать таблицу контактов (contact_id int 
 NOT NULL AUTO_INCREMENT,
 last_name VARCHAR НЕ NULL,
 first_name VARCHAR
, день рождения DATE,
 ->ОГРАНИЧЕНИЕ contact_pk ПЕРВИЧНЫЙ КЛЮЧ (contact_id));
создать таблицу contact_audit (contact_id целое число,
 дата создания_дата,
 created_by varchar ); 
 разделитель //
создать триггер contact_after_insert
 -> после вставки
 -> по контактам для каждой строки
 -> начать
 -> ОБЪЯВИТЬ vUser varchar(50);
 ->
 -> -- Найдите имя пользователя, выполняющего INSERT в таблицу
 -> ВЫБРАТЬ ПОЛЬЗОВАТЕЛЯ() в vUser;
 ->
 -> -- Вставить запись в таблицу аудита
 -> ВСТАВИТЬ в contact_audit
 -> ( contact_id,
 -> дата_создания,
 -> созданный_by)
 -> ЦЕННОСТИ
 -> ( NEW.contact_id,
 -> СИСДАТА(),
 -> vПользователь);
 -> КОНЕЦ; // 
 Создание вставки для вызова триггера:
 вставить в контакты значения (1, "Кумар", "Рупеш",
 str_to_date("20-06-1999", "%d-%m-%Y")); 
 выберите *из контактов_аудит;
+------------+---------------+----------------+
| контакт_идентификатор | дата_создания | created_by |
+------------+---------------+----------------+
| 1 | 2019-05-11 | корень@локальный |
+------------+---------------+----------------+
1 ряд в наборе (0,0006 сек) 
  5. Триггер перед удалением: 
 
 Как следует из названия, этот триггер вызывается до того, как произойдет удаление или до того, как будет реализован оператор удаления.
 создать контакты таблицы (contact_id int 
 NOT NULL AUTO_INCREMENT,
 last_name VARCHAR  NOT NULL, first_name VARCHAR
,
 день рождения DATE, дата создания DATE, created_by VARCHAR,
 ОГРАНИЧЕНИЕ contact_pk ПЕРВИЧНЫЙ КЛЮЧ (contact_id));
создать таблицу contact_audit (contact_id целое число, дата delete_date, delete_by varchar
);
 разделитель //
создать триггер contact_before_delete
 -> перед удалением
 -> по контактам для каждой строки
 -> начать
 ->
 -> ОБЪЯВИТЬ vUser varchar(50);
 ->
 -> -- Найдите имя пользователя, выполняющего DELETE в таблице
 -> ВЫБРАТЬ ПОЛЬЗОВАТЕЛЯ() в vUser;
 ->
 -> -- Вставить запись в таблицу аудита
 -> ВСТАВИТЬ в contact_audit
 -> ( contact_id,
 -> удаленная_дата,
 -> удалено_по)
 -> ЦЕННОСТИ
 -> ( СТАРЫЙ.contact_id,
 -> СИСДАТА(),
 -> vПользователь);
 -> конец; // 
 Создание вставки, а затем ее удаление для активации триггера:
 разделитель;
вставить в контакты значения (1, "Бонд", "Раскин",
 str_to_date("19-08-1995", "%d-%m-%Y"),
 str_to_date("27-04-2018", "%d-%m-%Y"), "xyz");
удалить из контактов, где last_name="Bond"; 
 выберите *from contact_audit;
+------------+---------------+----------------+
| контакт_идентификатор | удаленная_дата | удаленный_по |
+------------+---------------+----------------+
| 1 | 2019-05-11 | корень@локальный |
+------------+---------------+----------------+
1 ряд в наборе (0,0007 сек) 
  6. Триггер после удаления: 
 
 Как следует из названия, этот триггер вызывается после удаления или после реализации операции удаления.
  Пример: 
 
 Рассмотрим таблицы:
 создать контакты таблицы (contact_id int 
 NOT NULL AUTO_INCREMENT,
 last_name VARCHAR  NOT NULL, first_name VARCHAR
,
 день рождения ДАТА, дата_создания ДАТА, создано_по VARCHAR ,
 ОГРАНИЧЕНИЕ contact_pk ПЕРВИЧНЫЙ КЛЮЧ (contact_id));
создать таблицу contact_audit (contact_id целое число, дата delete_date, delete_by varchar
);
 создать триггер contact_after_delete
 -> после удаления
 -> по контактам для каждой строки
 -> начать
 ->
 -> ОБЪЯВИТЬ vUser varchar(50);
 ->
 -> -- Найдите имя пользователя, выполняющего DELETE в таблице
 -> ВЫБРАТЬ ПОЛЬЗОВАТЕЛЯ() в vUser;
 ->
 -> -- Вставить запись в таблицу аудита
 -> ВСТАВИТЬ в contact_audit
 -> ( contact_id,
 -> удаленная_дата,
 -> удалено_по)
 -> ЦЕННОСТИ
 -> ( СТАРЫЙ.contact_id,
 -> СИСДАТА(),
 -> vПользователь);
 -> конец; // 
 Вставка и удаление одного и того же для вызова триггера:
 разделитель;
вставить в контакты значения (1, "Ньютон", "Исаак",
 str_to_date("19-08-1985", "%d-%m-%Y"),
 str_to_date("23-07-2018", "%d-%m-%Y"), "xyz");
удалить из контактов, где first_name="Isaac"; 
 выберите *из контактов_аудит;
+------------+---------------+----------------+
| контакт_идентификатор | удаленная_дата | удаленный_по |
+------------+---------------+----------------+
| 1 | 2019-05-11 | корень@локальный |
+------------+---------------+----------------+
1 ряд в наборе (0,0009 сек) 
 Преимущество триггеров
  1.  Правила объектов базы данных устанавливаются триггерами, которые вызывают отмену изменений, если они не выполняются.
  2.  Триггер проверит данные и при необходимости внесет изменения.
  3.  Мы можем обеспечить целостность данных благодаря триггерам.
  4.  Перед вставкой или обновлением данные проверяются с помощью триггеров.
  5.  Триггеры помогают нам вести журнал записей.
  6.  Благодаря тому, что их не нужно компилировать при каждом запуске, триггеры повышают производительность SQL-запросов.
  7.  Код на стороне клиента сокращен за счет триггеров, что экономит время и трудозатраты.
  8.  Уход за спусковым крючком прост.
 Почему мы используем триггеры?
 Когда нам нужно выполнить некоторые действия автоматически в определенных желаемых сценариях, триггеры будут полезны. Например, нам необходимо знать частоту и время внесения изменений в таблицу, которая постоянно меняется. В таких случаях мы могли бы создать триггер для вставки необходимых данных в другую таблицу, если в основной таблице произошли какие-либо изменения.
 # Типы триггеров
  # 
 Время
 Есть два модификатора времени действия триггера:
  •  BEFORE
     триггер активируется перед выполнением запроса,
  •  AFTER
     вызвать огонь после изменения.
  # 
 Инициирующее событие
 Есть три события, к которым можно привязать триггеры:
  •  INSERT
  •  UPDATE
  •  DELETE
  # 
 Пример триггера «Перед вставкой»
  # 
 Пример триггера до обновления
  # 
 Пример триггера после удаления
  # 
 Синтаксис
  •  СОЗДАТЬ
     [DEFINER = {пользователь | ТЕКУЩИЙ ПОЛЬЗОВАТЕЛЬ }]
     TRIGGER имя_триггера
     триггер_время триггер_событие
     ON tbl_name ДЛЯ КАЖДОЙ СТРОКИ
     [триггер_порядок]
     trigger_body
    •  Два момента должны обратить ваше внимание, если вы уже используете триггеры на других БД:
      # 
     ЗА КАЖДЫЙ РЯД
      FOR EACH ROW
     является обязательной частью синтаксиса 
     Вы не можете сделать  утверждение 
     триггер (один раз по запросу), как это делает Oracle. Это больше проблема, связанная с производительностью, чем реальная отсутствующая функция
      # 
     СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ТРИГГЕР
      CREATE OR REPLACE
     не поддерживается MySQL 
     Будьте осторожны, это  не атомарная транзакция 
     :
    •  вы потеряете старый триггер, если CREATE
       провал
    •  при большой нагрузке, другие операции могут выполняться между DROP
       и CREATE
       , используйте LOCK TABLES myTable WRITE;
       во-первых, чтобы избежать несоответствия данных и UNLOCK TABLES;
       после CREATE
       освободить стол
     # Базовый триггер
     Оператор CREATE TRIGGER создает триггер с именем ins_sum, который связан с таблицей account. Он также включает пункты, определяющие время действия триггера, событие триггера и действия при срабатывании триггера
    .
     Если вы удаляете таблицу, все триггеры для таблицы также удаляются.
     Недостаток триггеров
    1.  Только триггеры позволяют использовать расширенные проверки.
    2.  Используются автоматические триггеры, и пользователь не знает, когда они выполняются. Следовательно, трудно устранять проблемы, возникающие на уровне базы данных.
    3.  Накладные расходы сервера базы данных могут увеличиться в результате триггеров.
    4.  В одном операторе CREATE TRIGGER мы можем указать одно и то же действие триггера для нескольких действий пользователя, таких как INSERT и UPDATE.
    5.  Для создания триггеров доступна только текущая база данных, но они по-прежнему могут ссылаться на объекты вне базы данных.
     ДО и ПОСЛЕ триггера
     Триггеры BEFORE запускают действие триггера до того, как будет запущен оператор триггера. Триггеры FTER запускают действие триггера после запуска оператора триггера.
      Пример 
     
     Данная база данных отчетов учащихся, в которой записываются оценки учащихся. В такой схеме создайте триггер, чтобы общее количество и процент указанных оценок автоматически вставлялись всякий раз, когда вставляется запись.
     Здесь триггер срабатывает до того, как запись будет вставлена, поэтому можно использовать тег BEFORE.
      Предположим, схема базы данных 
     mysql>>desc Студент; 
     Создать триггер в MySQL
     SQL-триггер к постановке проблемы.
     img2
     Приведенный выше оператор SQL создаст триггер в базе данных учащихся, в который всякий раз, когда вводятся оценки по предметам, перед вставкой этих данных в базу данных триггер вычисляет эти два значения и вставляет их с введенными значениями. то есть
     img3
     
     In this way, triggers can be created and executed in the databases.
     Чудесный простой триггер
      Следующий пример, который мы рассмотрим связан с журналированием событий.  Например, мы хотим наблюдать за каждым товаром, который помещается в корзину.  Возможно, мы хотим отслеживать рейтинг покупки товаров.  Возможно, мы просто хотим иметь копию каждого товара, помещенного в корзину, не обязательно для продажи, а для анализа поведения покупателей.  Какими бы ни были причины, давайте посмотрим на триггер INSERT, который открывает возможности для журналирования или аудита наших данных. 
      До использования триггера, вероятно мы делали что-то похожее: 
     Create with No Trigger
     
      Теперь мы можем создать очень простой триггер для процесса журналирования: 
     CREATE TRIGGER `after_insert_cart_items`  
      
AFTER INSERT ON `trigger_cart_items` FOR EACH ROW  
      
BEGIN  
      
INSERT INTO trigger_cart_log (cart_id, item_id)  
      
VALUES (NEW.cart_id, NEW.item_id);  
      
END  
      

      Первая строка  “CREATE TRIGGER `after_insert_cart_items`”.  Для MySQL задается команда, создать триггер с именем “after_insert_cart_items”.  Имя может быть “Foo”, или “BullWinkle” или какое-то другое, но лучше использовать ранее описанную схему имен триггера.  Далее следует “AFTER INSERT ON `trigger_cart_items` FOR EACH ROW”.  Снова  мы говорим, что после того, как что-то будет вставлено в trigger_cart_items, для каждой строки нужно выполнить операции между BEGIN и END. 
      Строка “INSERT INTO trigger_cart_log (cart_id, item_id) VALUES (NEW.cart_id, NEW.item_id);” является стандартным запросом с использованием двух переменных.  Здесь используются значения NEW, которые вставляются в таблицу cart_items. 
      Снова выполнение нашего запроса осуществляется быстрее: 
     Create with a Trigger
     
      Для проверки, что триггер работает, посмотрим значения в таблице: 
     Proof of my Trigger
     
     Целостность данных
      Если Вы захотите выполнить даже незначительную нормализацию структуры базы данных,  может получиться так, что нужно будет удалять источник основных данных, который имеет фрагменты, участвующие в общем потоке данных.  Например, у вас может быть cart_id, который ссылается на две или три таблицы без внешних ключей, особенно при использовании механизма MyISAM, который их не поддерживает. 
      Для такого случая раньше вы возможно выполняли следующие операции: 
     $sql = 'DELETE FROM no_trigger_cart_items WHERE cart_id = 1';  
      
$rs = $this->db->query($sql);  
      
$sql = 'DELETE FROM no_trigger_carts WHERE cart_id = 1';  
      
$rs = $this->db->query($sql);  
      

      Теперь, в зависимости от того, насколько вы сами организованы, у вас может быть одна  API или метод, который очищает ваши корзины.  Если это ваш случай, то у вас будет изолированная функция, которая выполняет два запроса.  Если самоорганизация — не ваш конек, то Вам придется всегда помнить, что нужно очищать элементы корзины, когда вы удаляете определенную корзину.  Не сложно, но если Вы забудете, то потеряете целостность данных. 
      Вернемся к триггерам.  Создадим простой триггер, который при удалении корзины будет удалять все элементы корзины, которые имеют такой же cart_id: 
     CREATE TRIGGER `tutorial`.`before_delete_carts`  
      
BEFORE DELETE ON `trigger_carts` FOR EACH ROW  
      
BEGIN  
      
DELETE FROM trigger_cart_items WHERE OLD.cart_id = cart_id;  
      
END  
      

      Очень простой синтаксис.  Давайте разберем триггер подробно. 
      Первая строка“CREATE TRIGGER `tutorial`.`before_delete_carts`”.  Это команда для  MySQL создать триггер для базы данных  “tutorial”, который будет иметь имя “before_delete_carts”.  Будем использовать схему имен для триггеров “Когда_Что_Таблица”. 
      Вторая строка указывает для MySQL определение триггера “BEFORE DELETE ON `trigger_carts` FOR EACH ROW”.  Мы говорим MySQL, что перед тем, как провести удаление из данной таблицы, для каждой строки нужно сделать что-то.  Что нужно сделать, объясняется далее между BEGIN и END.  “ DELETE FROM trigger_cart_items WHERE OLD.cart_id = cart_id;” Для MySQL задается, что перед тем, как удалить из trigger_carts, нужно взять  OLD.cart_id и также удалить из trigger_cart_items.  Синтаксис OLD определяет переменную.  Он будет обсуждаться в следующем разделе, где будут комбинироваться  OLD и NEW. 
      Преимущество использования триггера — целостность ваших данных перемещается с уровня логики на уровень данных, где она и должна быть.  Также есть и некоторый прирост производительности системы. 
     Удаление без использования триггеров
     
      Один запрос с триггером: 
     Delete with a Trigger
     
      Как вы можете видеть, существует небольшой прирост производительности, которого следовало ожидать.  База данных для примера использует тот же самый сервер, что и клиент.  Но если сервер баз данных расположен в другом месте, то следует ожидать более значительной разницы, так как ко времени выполнения запросов добавится время передачи данных между серверами. Также нужно отметить, что первый раз триггер может выполняться значительно медленнее, чем в следующие разы.  
      Перемещение логики данных на уровень данных подобно тому, как задание стиля перемещается с уровня разметки на уровень презентации, что известно всему миру как CSS. 
     How does SQL Server Show Trigger?
     The show or list trigger is useful when we have many databases with many tables.  This query is very useful when the table names are the same across multiple databases.  We can view a list of every trigger available in the SQL Server by using the command below:
    FROM sys.triggers, SELECT name, is_instead_of_trigger     
     
     IF type = ‘TR’; 
     Go to the  Databases 
     menu, select the desired database, and then expand it.
    •  Select the  Tables 
       menu and expand it.
    •  Select any specific table and expand it.
     We will get various options here.  When we choose the  Triggers 
     option, it displays all the triggers available in this table.
     Different Trigger Types in SQL Server
     Two categories of triggers exist:
    1.  DDL Trigger
    2.  DML Trigger
    3.  Logon Triggers
     DDL Triggers 
     The Data Definition Language (DDL) command events such as Create_table, Create_view, drop_table, Drop_view, and Alter_table cause the DDL triggers to be activated.
         create tigger safety    

    on database    

for
    create_table,alter_table,drop_table    

as
    print 'you can not create,drop and alter tab    
  
     Создать триггер в MySQL
     
     DML Triggers
     The Data uses manipulation Language (DML) command events that begin with Insert, Update, and Delete set off the DML triggers.  corresponding to insert_table, update_view, and delete_table.
      create trigger deep 
on    emp    

for
insert,update ,delete
    as    

print 'you can not insert,update and delete this table i'
rollback;  
     Создать триггер в MySQL
     
     Logon Triggers
     Более сложный триггер
      Начиная с этого момента мы перестанем рассматривать старый способ использования множественных запросов и их сравнение с техникой использования триггеров.  Давайте рассмотрим несколько более продвинутых примеров использования триггеров. 
      Бизнес логика — это место, где плодятся ошибки.  Не смотря на осторожность и внимание к организации процесса, всегда что-то идет не так.  Триггер для UPDATE позволяет несколько смягчить такое положение.  У нас есть возможность в триггере вычислить значение OLD и установить значение NEW на основе оценки.  Например, мы хотим всегда устанавливать цену на товар с 30% надбавкой к стоимости.  Это приводит к тому, что когда мы изменяем (UPDATE) стоимость, мы должны изменить (UPDATE) цену.  Давайте используем триггер. 
     CREATE TRIGGER `after_update_cost`  
      
AFTER UPDATE ON `trigger_items_cost` FOR EACH ROW  
      
BEGIN  
      
UPDATE trigger_items  
      
SET price = (NEW.cost * 1.3)  
      
WHERE item_id = NEW.item_id;  
      
END  
      

      Мы изменяем таблицу товаров с ценами, основанными на NEW.cost * 1.3.  Если ввести стоимость $50, то цена должна быть $65. 
     Update Trigger Price Change
     
      Данный триггер работает отлично. 
      Давайте рассмотрим более сложный пример.  У нас уже есть правило, которое изменяет цену товара на основе стоимости.  Теперь мы хотим установить некоторую ярусность в ценах.  Если цена меньше $50, то актуальное значение будет  $50.  Если цена больше $50, но меньше $100, то актуальное значение будет  $100.  
      Для того, чтобы решить задачу, мы снова будем работать с UPDATE, но в этот раз триггер будет выполняться до выполнения запроса.  Также будет использоваться выражение IF. 
      Вот текст триггера: 
     CREATE TRIGGER `before_update_cost`  
      
BEFORE UPDATE ON `trigger_items_cost` FOR EACH ROW  
      
BEGIN  
      
IF NEW.cost < 50 THEN  
      
SET NEW.cost = 50;  
      
ELSEIF NEW.cost > 50 AND NEW.cost < 100 THEN  
      
SET NEW.cost = 100;  
      
END IF;  
      
END  
      

      Это не запрос, а перекрытие значений.  Если цена меньше $50, то устанавливаем ее $50.  Если цена лежит между $50 и $100, то устанавливаем ее $100.  Если она выше, то просто оставляем ее такой, какая она есть.  Синтаксис не отличается от других серверных языков.  Нужно закрыть выражение IF с помощью END IF. 
      Проверим работу нашего триггера.  Если ввести значение стоимости  $30, то цена должна быть $50: 
     Cost Is 50
     
      Для значения стоимости  $85: 
     Cost Is 100
     
      Для проверки того, что триггер AFTER UPDATE все еще работает, цена должна быть $130: 
     Price is 130
     
     Frequently Asked Questions
     Q1: What is an SQL trigger?
     An SQL trigger is a database object that is associated with a table and automatically executes a set of SQL statements when a specific event occurs on that table.  Triggers are used to enforce business rules, maintain data integrity, and automate certain actions within a database.  They can be triggered by various events, such as inserting, updating, or deleting data in a table, and they allow you to perform additional operations based on those events.
     Q2: How do SQL triggers work? 
     SQL triggers are defined using SQL statements and are associated with a specific table.  When the defined trigger event (e.g., INSERT, UPDATE, DELETE) occurs on that table, the associated trigger code is executed automatically.  The trigger code can consist of SQL statements that can manipulate data in the same or other tables, enforce constraints, or perform other actions.  Triggers are executed within the transaction scope, and they can be defined to execute either before or after the triggering event.
     Q3: What are the benefits of using SQL triggers? 
     The benefits of using SQL triggers include:
     Data integrity: Triggers allow you to enforce complex business rules and constraints at the database level, ensuring that data remains consistent and accurate.
     Automation: Triggers can automate repetitive or complex tasks by executing predefined actions whenever a specified event occurs.  This reduces the need for manual intervention and improves efficiency.
     Audit trails: Triggers can be used to track changes made to data, such as logging modifications in a separate audit table.  This helps in auditing and maintaining a history of data changes.
     Data validation: Triggers can perform additional validation checks on data before it is inserted, updated, or deleted, ensuring that only valid and conforming data is stored in the database.
    Оцените статью
    Хостинги
    © 2024 Хостинги