Как вызвать триггер в sql
Here is a simple example that associates a trigger with a table, to activate for INSERT operations. The trigger acts as an accumulator, summing the values inserted into one of the columns of the table.
The CREATE TRIGGER statement creates a trigger named ins_sum that is associated with the account table. It also includes clauses that specify the trigger action time, the triggering event, and what to do when the trigger activates:
The keyword BEFORE indicates the trigger action time. In this case, the trigger activates before each row inserted into the table. The other permitted keyword here is AFTER .
The keyword INSERT indicates the trigger event; that is, the type of operation that activates the trigger. In the example, INSERT operations cause trigger activation. You can also create triggers for DELETE and UPDATE operations.
The statement following FOR EACH ROW defines the trigger body; that is, the statement to execute each time the trigger activates, which occurs once for each row affected by the triggering event. In the example, the trigger body is a simple SET that accumulates into a user variable the values inserted into the amount column. The statement refers to the column as NEW.amount which means “ the value of the amount column to be inserted into the new row. ”
To use the trigger, set the accumulator variable to zero, execute an INSERT statement, and then see what value the variable has afterward:
In this case, the value of @sum after the INSERT statement has executed is 14.98 + 1937.50 — 100 , or 1852.48 .
To destroy the trigger, use a DROP TRIGGER statement. You must specify the schema name if the trigger is not in the default schema:
If you drop a table, any triggers for the table are also dropped.
Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name.
It is possible to define multiple triggers for a given table that have the same trigger event and action time. For example, you can have two BEFORE UPDATE triggers for a table. By default, triggers that have the same trigger event and action time activate in the order they were created. To affect trigger order, specify a clause after FOR EACH ROW that indicates FOLLOWS or PRECEDES and the name of an existing trigger that also has the same trigger event and action time. With FOLLOWS , the new trigger activates after the existing trigger. With PRECEDES , the new trigger activates before the existing trigger.
For example, the following trigger definition defines another BEFORE INSERT trigger for the account table:
This trigger, ins_transaction , is similar to ins_sum but accumulates deposits and withdrawals separately. It has a PRECEDES clause that causes it to activate before ins_sum ; without that clause, it would activate after ins_sum because it is created after ins_sum .
Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case-sensitive.
In an INSERT trigger, only NEW. col_name can be used; there is no old row. In a DELETE trigger, only OLD. col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD. col_name to refer to the columns of a row before it is updated and NEW. col_name to refer to the columns of the row after it is updated.
A column named with OLD is read only. You can refer to it (if you have the SELECT privilege), but not modify it. You can refer to a column named with NEW if you have the SELECT privilege for it. In a BEFORE trigger, you can also change its value with SET NEW. col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or used to update a row. (Such a SET statement has no effect in an AFTER trigger because the row change has already occurred.)
In a BEFORE trigger, the NEW value for an AUTO_INCREMENT column is 0, not the sequence number that is generated automatically when the new row actually is inserted.
By using the BEGIN . END construct, you can define a trigger that executes multiple statements. Within the BEGIN block, you also can use other syntax that is permitted within stored routines such as conditionals and loops. However, just as for stored routines, if you use the mysql program to define a trigger that executes multiple statements, it is necessary to redefine the mysql statement delimiter so that you can use the ; statement delimiter within the trigger definition. The following example illustrates these points. It defines an UPDATE trigger that checks the new value to be used for updating each row, and modifies the value to be within the range from 0 to 100. This must be a BEFORE trigger because the value must be checked before it is used to update the row:
It can be easier to define a stored procedure separately and then invoke it from the trigger using a simple CALL statement. This is also advantageous if you want to execute the same code from within several triggers.
There are limitations on what can appear in statements that a trigger executes when activated:
The trigger cannot use the CALL statement to invoke stored procedures that return data to the client or that use dynamic SQL. (Stored procedures are permitted to return data to the trigger through OUT or INOUT parameters.)
The trigger cannot use statements that explicitly or implicitly begin or end a transaction, such as START TRANSACTION , COMMIT , or ROLLBACK . ( ROLLBACK to SAVEPOINT is permitted because it does not end a transaction.).
MySQL handles errors during trigger execution as follows:
If a BEFORE trigger fails, the operation on the corresponding row is not performed.
A BEFORE trigger is activated by the attempt to insert or modify the row, regardless of whether the attempt subsequently succeeds.
An AFTER trigger is executed only if any BEFORE triggers and the row operation execute successfully.
An error during either a BEFORE or AFTER trigger results in failure of the entire statement that caused trigger invocation.
For transactional tables, failure of a statement should cause rollback of all changes performed by the statement. Failure of a trigger causes the statement to fail, so trigger failure also causes rollback. For nontransactional tables, such rollback cannot be done, so although the statement fails, any changes performed prior to the point of the error remain in effect.
Triggers can contain direct references to tables by name, such as the trigger named testref shown in this example:
Suppose that you insert the following values into table test1 as shown here:
Триггеры
Триггеры представляют специальный тип хранимой процедуры, которая вызывается автоматически при выполнении определенного действия над таблицей или представлением, в частности, при добавлении, изменении или удалении данных, то есть при выполнении команд 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 в реальности товар будет иметь несколько большую цену, чем та, которая была определена при добавлении:
Удаление триггера
Для удаления триггера необходимо применить команду DROP TRIGGER :
Отключение триггера
Бывает, что мы хотим приостановить действие триггера, но удалять его полностью не хотим. В этом случае его можно временно отключить с помощью команды DISABLE TRIGGER :
А когда триггер понадобится, его можно включить с помощью команды ENABLE TRIGGER :
Триггеры в MySQL
— Удаляем триггер
DROP TRIGGER `update_test`;
— Cоздадим еще одну таблицу,
— в которой будут храниться резервные копии строк из таблицы test
CREATE TABLE `testing`.` backup ` (
`id` INT ( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`row_id` INT ( 11 ) UNSIGNED NOT NULL ,
`content` TEXT NOT NULL
) ENGINE = MYISAM
— триггеры
DELIMITER |
CREATE TRIGGER `update_test` before update ON `test`
FOR EACH ROW BEGIN
INSERT INTO backup Set row_id = OLD .id, content = OLD .content;
END ;
CREATE TRIGGER `delete_test` before delete ON `test`
FOR EACH ROW BEGIN
INSERT INTO backup Set row_id = OLD .id, content = OLD .content;
END * This source code was highlighted with Source Code Highlighter .
Теперь если мы отредактируем или удалим строку из test она скопируется в backup.
зы: надеюсь статья была интересной и полезной
UPD: для создания триггеров в версии до 5.1.6 требуются полномочия суперпользователя.
# TRIGGERS
The CREATE TRIGGER statement creates a trigger named ins_sum that is associated with the account table. It also includes clauses that specify the trigger action time, the triggering event, and what to do when the trigger activates
Insert Value
To use the trigger, set the accumulator variable (@sum) to zero, execute an INSERT statement, and then see what value the variable has afterward:
In this case, the value of @sum after the INSERT statement has executed is 14.98 + 1937.50 — 100, or 1852.48.