Что такое триггер в программировании
Перейти к содержимому

Что такое триггер в программировании

  • автор:

Что такое триггер в программировании

Триггер — это PL/SQL блок, который хранится в базе данных и срабатывает (выполняется) в ответ на указанное событие.

Триггер может быть определён на таблицу, представление, схему (владельца схемы) или базу данных (всех пользователей).

Типы событий триггеров:

  • DML-команда ( DELETE , INSERT или UPDATE ).
  • DDL-команды ( CREATE , ALTER или DROP ).
  • Операции базы данных, такие как SERVERERROR , LOGON , LOGOFF , STARTUP или SHUTDOWN .

Пример создания триггера:

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

Триггеры делятся на:

  • Триггеры базы данных. Срабатывают при возникновении DML, DDL или системного события в схеме или базе данных.
  • Триггеры приложений. Срабатывают при возникновении события в конкретном приложении.

Триггеры можно использовать для:

  • Безопасности.
  • Аудита.
  • Целостности данных.
  • Ссылочной целостности.
  • Репликации (копирования) таблиц.
  • Автоматического вычисления производных данных.
  • Ведения журнала событий.

Допустимые типы триггеров:

  • Простые DML-триггеры ( BEFORE , AFTER , INSTEAD OF (только для view)).
  • Составные (compound) триггеры.
  • Не DML-триггеры (DDL-события, события базы данных).
  • Уровня команды (по умолчанию). Срабатывают один раз на событие триггера, даже тогда, когда не затронута ни одна из строк.
  • Строковыми. Нужно прописать FOR EACH ROW , срабатывают один раз на каждую затронутую строку по событию триггера, не срабатывают, если событие не затронуло ни одной строки.
3. Использование условных предикатов

Можно так же задавать условия для выполнения триггеров.

В теле триггера можно использовать конструкции (если триггер состоит из нескольких DML-операций):

  • IF DELETING ,
  • IF UPDATING ,
  • IF INSERTING ,

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

Можно так же использовать WHEN для строковых триггеров.

О NEW и OLD (доступны только в строковых триггерах):

DML-команда OLD NEW
INSERT NULL введённое значение
UPDATE значение до обновления значение после обновления
DELETE удаляемое значение NULL

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

  1. Выполняются все BEFORE триггеры уровня команды.
  2. Для каждой строки, затронутой триггером:
    1. Выполняются все строковые BEFORE триггеры
    2. Выполняется DML-команда и проверка ограничений целостности.
    3. Выполняются все строковые AFTER триггеры.

    У триггера есть 2 состояния: ENABLE и DISABLE . Синтаксис:

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

    Информацию о триггерах можно посмотреть в словарях USER_OBJECTS , USER/ALL/DBA_TRIGGERS . Синтаксис ошибок триггеров можно посмотреть в словаре USER_ERRORS .

    5. Составные (compound) триггеры

    Составной (compound) триггер — это один триггер на таблицу, позволяющий задать действия для каждой из следующих четырёх точек синхронизации:

    Общие сведения о триггерах

    где plsql_trigger_source, это такая конструкция:

    Конструкции simple_dml_trigger, instead_of_dml_trigger, compound_dml_trigger и system_trigger будут приведены в соответствующих разделах статьи.

    DML triggers

    • DML триггеры создаются для таблиц или представлений, срабатывают при вставке, обновлении или удалении записей.
    • Триггер может быть создан в другой схеме, отличной от той, где определена таблицы. В таком случае текущей схемой при выполнении триггера считается схема самого триггера.
    • При операции MERGE срабатывают триггеры на изменение, вставку или удаление записей в зависимости от операции со строкой.
    • Триггер – часть транзакции, ошибка в триггере откатывает операцию, изменения таблиц в триггере становятся частью транзакции.
    • Если откатывается транзакция, изменения триггера тоже откатываются.
    • В триггерах запрещены операторы DDL и управления транзакциями (исключения – автономные транзакции).

    Конструкция simple_dml_trigger:

    Где, dml_event_clause:

    referencing_clause:

    trigger_edition_clause:

    trigger_body:

    • На таблице
    • На представлении (instead of trigger)
    • Вставка записей (insert)
    • Обновление записей (update)
    • Удаление записей (delete)
    • Уровень всей команды (statement level triggers)
    • Уровень записи (row level triggers)
    • Составные триггеры (compound triggers)
    • Перед выполнением операции (before)
    • После выполнения операции (after)

    Crossedition triggers — служат для межредакционного взаимодействия, например для переноса и трансформации данных из полей, отсутствующих в новой редакции, в другие поля.

    Условные предикаты для определения операции, на которую сработал триггер:

    Предикат Описание
    Inserting True, если триггер сработал на операцию Insert
    Updating True, если триггер сработал на операцию Update
    Updating(‘colum’) True, если триггер сработал на операцию Update, которая затрагивает определенное поле
    Deleting True, если триггер сработал на операцию Delete

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

    Псевдозаписи

    Существуют псевдозаписи, позволяющие обратиться к полям изменяемой записи и получить значения полей до изменения и значения полей после изменения. Это записи old и new. С помощью конструкции Referencing можно изменить их имена. Структура этих записей tablename%rowtype. Эти записи есть только у триггеров row level или у compound триггеров (с секциями уровня записи).

    Операция срабатывания триггера OLD.column NEW.column
    Insert Null Новое значение
    Update Старое значение Новое значение
    Delete Старое значение Null
    • С псевдозаписями запрещены операции уровня всей записи ( :new = null;)
    • Нельзя изменять значения полей записи old
    • Если триггер срабатывает на delete, нельзя изменить значения полей записи new
    • В триггере after нельзя изменить значения полей записи new

    Instead of dml triggers

    • Создаются для представлений (view) и служат для замещения DML операций своим функционалом.
    • Позволяют производить операции вставки/обновления или удаления для необновляемых представлений.

    Конструкция instead_of_dml_trigger:

    • Это всегда триггер уровня записи (row level)
    • Имеет доступ к псевдозаписям old и new, но не может изменять их
    • Заменяет собой dml операцию с представлением (view)

    Instead of triggers on Nested Table Columns of Views

    Можно создать триггер для вложенной в представлении таблицы. В таком триггере также присутствует дополнительная псевдозапись – parent, которая ссылается на всю запись представления (стандартные псевдозаписи old и new ссылаются только на записи вложенной таблицы)

    Составные DML триггера (compound DML triggers)

    Появившиеся в версии 11G эти триггера включают в одном блоке обработку всех видов DML триггеров.
    Конструкция compound_dml_trigger:

    Где, compound_trigger_block:

    timing_point_section:

    https://amdy.su/wp-admin/options-general.php?page=ad-inserter.php#tab-8

    timing_point:

    tps_body:

    • Срабатывают такие триггера при разных событиях и в разные моменты времени (на уровне оператора или строки, при вставке/обновлении/удалении, до или после события).
    • Не могут быть автономными транзакциями.
    • Собирать в коллекцию строки для вставки в другую таблицу, чтобы периодически вставлять их пачкой
    • Избежать ошибки мутирующей таблицы (mutating-table error)

    Структура составного триггера

    • Before statement
    • After statement
    • Before each row
    • After each row
    • Нельзя обращаться к псевдозаписям old, new или parent в секциях уровня выражения (before statement и after statement)
    • Изменять значения полей псевдозаписи new можно только в секции before each row
    • Исключения, сгенерированные в одной секции, нельзя обрабатывать в другой секции
    • Если используется оператор goto, он должен указывать на код в той же секции

    Основные правила определения DML триггеров

    • Update of – позволяет указать список изменяемых полей для запуска триггера
    • Все условия в заголовке и When … проверяются без запуска триггера на стадии выполнения SQL
    • В операторе When можно использовать только встроенные функции
    • Можно делать несколько триггеров одного вида, порядок выполнения не определен по умолчанию, но его можно задать с помощью конструкции FOLLOWS TRIGGER_FIRST
    • Ограничения уникальности проверяются при изменении записи, то есть после выполнения триггеров before
    • Секция объявления переменных определяется словом DECLARE
    • Основной блок триггера подчиняется тем же правилам, что и обычные PL/SQL блоки

    Ограничения DML триггеров

    • нельзя выполнять DDL statements (только в автономной транзакции)
    • нельзя запускать подпрограммы с операторами контроля транзакций
    • не имеет доступа к SERIALLY_REUSABLE пакетов
    • размер не может превышать 32К
    • нельзя декларировать переменные типа LONG и LONG RAW

    Ошибка мутирования таблицы ORA-04091

    • использовать триггеры уровня операции
    • автономная транзакция в триггере
    • использовать сторонние структуры (коллекции уровня пакета)
    • использовать COMPOUND TRIGGER
    • изменение самого алгоритма с выносом функционала из триггера

    Системные триггеры (System triggers)

    Конструкция system_trigger:

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

    All You Need to Know about Triggers in SQL

    Sahiti Kappagantula

    A trigger in SQL is a procedural code that is automatically executed in response to certain events on a specified table. It is important to understand how these small codes make such a huge difference in database performance. In this article, you will learn how to implement triggers along with examples.

    The following topics will be covered in this article:

    • What is a Trigger?
    • Syntax and Example
    • Operation in Triggers
    • Advantage and Disadvantage

    What is a Trigger?

    Triggers are automatically executed in response to certain events on a particular table. These are used to maintain the integrity of the data. A trigger in SQL codes that are SQL works similar to a real-world trigger. For example, when the gun trigger is pulled a bullet is fired. We all know this, but how this is related to Triggers in SQL? To understand this let’s consider a hypothetical situation.

    John is the marketing officer in a company. When a new customer data is entered into the company’s database he has to send the welcome message to each new customer. If it is one or two customers John can do it manually, but what if the count is more than a thousand? Well in such a scenario triggers come in handy.

    Thus, now John can easily create a trigger that will automatically send a welcome email to the new customers once their data is entered into the database. So I hope you are clear with the introduction of Triggers in SQL.

    Always remember that there cannot be two triggers with similar action time and event for one table. For example, we cannot have two BEFORE UPDATE triggers for a table. But we can have a BEFORE UPDATE and a BEFORE INSERT trigger, or a BEFORE UPDATE and an AFTER UPDATE trigger.

    Before we dive further into the fundamentals of triggers I would suggest you to understand the concepts of SQL Basics and Normalization so that you get a better grip on Triggers in SQL.

    Syntax and Example

    Lets now look at the syntax of a trigger.

    Now let me break down this syntax and explain each and every part in detail.

    • Create Trigger
      These two keywords are used to specify that a trigger block is going to be declared.
    • Trigger_Name
      It specifies the name of the trigger. Trigger name has to be unique and shouldn’t repeat.
    • ( Before | AfterThis specifies when the trigger will be executed. It tells us the time at which the trigger is initiated, i.e, either before the ongoing event or after.
    • Before Triggers are used to update or validate record values before they’re saved to the database.
    • After Triggers are used to access field values that are set by the system and to effect changes in other records. The records that activate the after trigger are read-only. We cannot use the After trigger if we want to update a record because it will lead to a read-only error.
    • [ Insert | Update | Delete ]
      These are the DML operations and we can use either of them in a given trigger.
    • on [ Table_NameWe need to mention the table name on which the trigger is being applied. Don’t forget to use on keyword and also make sure the selected table is present in the database.
    • [ for each row | for each column ]
    1. Row-level trigger gets executed before or after any column value of a row changes
    2. Column Level Trigger gets executed before or after the specified column changes

    So this was all about a simple trigger. But we can also create a nested trigger that can do multi-process. Also handling it and terminating it at the right time is very important. If we don’t end the trigger properly it may lead to an infinite loop.

    You might be wondering in which scenario we can use the nested trigger. Rather than giving you a tailored answer let me share a scenario with you, which will help you in understanding the nested trigger in a better way. Continuing from the earlier scenario, John sent an email for every new customer that was added to the company’s database. Now, what if he wishes to keep track of the number of customers to whom the email was sent? Now John needs to create a nested trigger to keep the track of the count along with sending an email.

    So that was all about the syntax of triggers, lets now try to implement an example of triggers in SQL.

    Example for Trigger:

    In the below trigger, we are trying to calculate the percentage of the student as soon as his details are updated to the database.

    Here the “ NEW” keyword refers to the row that is getting affected.

    Operations in Triggers

    We can perform many operations using triggers. Some may be simple and some may be a little complex, but once if we go through the query its easy to understand.

    The below code will display all the triggers that are present.

    The below code will display all the triggers that are present in a particular database.

    In the above example, all the triggers that are present in the database named Edureka will be displayed.

    We also look at some major variants of the triggers that is Before insert and After insert. We have already seen a trigger in the example. But with the help of the table lets see how exactly this works.

    As we have already understood how to create a trigger, now let’s understand the two variants of the trigger are Before inserted and After insert. in order to implement them, let’s create a student table with various columns as shown below:

    Now if we execute this query we get the following table.

    Let’s try to use the first variant i.e, Before Insert

    Here when we insert data into the student table automatically the trigger will be invoked. The trigger will add 100 to the marks column into the student column.

    Now let’s use the second variant i.e, After Insert

    To use this variant we need one more table i.e, Percentage where the trigger will store the results. Use the below code to create the Percentage Table.

    Now let us use the after insert trigger

    That was all about the operation on triggers, lets now move ahead and look at its advantages and disadvantages.

    Advantages and Disadvantages of Triggers

    Advantages

    • Forcing security approvals on the table that are present in the database
    • Triggers provide another way to check the integrity of data
    • Counteracting invalid exchanges
    • Triggers handle errors from the database layer
    • Normally triggers can be useful for inspecting the data changes in tables
    • Triggers give an alternative way to run scheduled tasks. Using triggers, we 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

    Disadvantages

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

    Here when we insert data to the table, total_mark trigger will store the result in the Final_mark table.

    That was all about the operation on triggers. If you wish to check out more articles on the market’s most trending technologies like Artificial Intelligence, DevOps, Ethical Hacking, then you can refer to Edureka’s official site.

    Do look out for other articles in this series that will explain the various other aspects of SQL.

    Триггеры

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

    Формальное определение триггера:

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

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

    Затем устанавливается тип триггера. Мы можем использовать один из двух типов:

    AFTER : выполняется после выполнения действия. Определяется только для таблиц.

    INSTEAD OF : выполняется вместо действия (то есть по сути действие — добавление, изменение или удаление — вообще не выполняется). Определяется для таблиц и представлений

    После типа триггера идет указание операции, для которой определяется триггер: INSERT , UPDATE или DELETE .

    Для триггера AFTER можно применять сразу для нескольких действий, например, UPDATE и INSERT. В этом случае операции указываются через запятую. Для триггера INSTEAD OF можно определить только одно действие.

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

    Создадим триггер. Допустим, у нас есть база данных productsdb со следующим определением:

    Определим триггер, который будет срабатывать при добавлении и обновлении данных:

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

    Таким образом, триггер будет срабатывать при любой операции INSERT или UPDATE над таблицей Products. Сам триггер будет изменять цену товара, а для получения того товара, который был добавлен или изменен, находим этот товар по Id. Но какое значение должен иметь Id такой товар? Дело в том, что при добавлении или изменении данные сохраняются в промежуточную таблицу inserted. Она создается автоматически. И из нее мы можем получить данные о добавленных/измененных товарах.

    И после добавления товара в таблицу Products в реальности товар будет иметь несколько большую цену, чем та, которая была определена при добавлении:

    Триггеры в MS SQL Server

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

    Для удаления триггера необходимо применить команду DROP TRIGGER :

    Отключение триггера

    Бывает, что мы хотим приостановить действие триггера, но удалять его полностью не хотим. В этом случае его можно временно отключить с помощью команды DISABLE TRIGGER :

    А когда триггер понадобится, его можно включить с помощью команды ENABLE TRIGGER :

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *