This content originally appeared on DEV Community and was authored by DEV Community
Photo by Jan Antonin Kolar on Unsplash
Keeping with the theme, here's another installment of Things I Google Each Week. Let's dive right into this one...
What is a MySQL trigger
In MySQL, a trigger is a stored statement that is invoked automatically in response to an event -- such as insert, update, or delete -- in the associated table.
As a bit of background, 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.
A statement-level trigger is executed once for each transaction with the associated table regardless of how many rows are inserted, updated, or deleted.
The important part to note is that MySQL supports only row-level triggers.
Here's the basic syntax:
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
trigger_body;
The trigger_body is where you specify the statement to execute when the trigger activates.
If you want to execute multiple statements, use the BEGIN END compound statement.
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
BEGIN
first_statement
second_Statement
END;
When/How would I use a trigger?
Triggers are used when you want to update/insert/delete a row either before or after a database transaction. Everything has pros and cons though. Here's a few advantages and disadvantages when considering triggers.
Advantages:
- provides another way to check the integrity of data being added/updated/removed
- some error handling
- an alternative way to run scheduled tasks, triggers are invoked automatically before or after a change is made
- useful for auditing the data changes in tables
Disadvantages:
- only provide extended validations, not all validations
- can be difficult to troubleshoot because they automatically execute in the database
- may increase the overhead of the MySQL Server.
What are some interesting things to take note about triggers?
The trigger body can access the values of the column being affected by the trigger statement.
To distinguish between the value of the columns BEFORE and AFTER the trigger has fired, use the NEW and OLD modifiers.
For example, if you update the column start_trek_rank, in the trigger body, you can access the value of star_trek_rank before the update using OLD.star_trek_rank and the new value using NEW.star_trek_rank.
Also, here's how MySQL handles errors during trigger execution:
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 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.
Where can I learn more?
This content originally appeared on DEV Community and was authored by DEV Community
DEV Community | Sciencx (2021-02-24T16:23:31+00:00) TIGEW: MySQL Triggers. Retrieved from https://www.scien.cx/2021/02/24/tigew-mysql-triggers/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.