I started to work on a pet project for tracking deleted/pending domains (myDomainTracker) and after starting development I was facing a big problem: How to do the data validation directly from MySQL ?
Many times I wanted to do data validation directly when inserting into a MySQL table and leave the source code of my application nice, clean and very readable, but support for that was only in Oracle and other SQL servers but not in MySQL.
Beginning with version 5 of MySQL the development team has added “triggering” to their SQL server engine, to meet the growing demand of users.
But what are triggers ?
A trigger is a database object that is associated with a table and is activated when an event occurs for that table. Can be successfully used for database data validation and any other calculations done directly on the database.
When is a trigger activated ?
A trigger is activated when changes are made on that table where it is associated with, more precisely on Insert/Update/Deleted execution statements
A simple example:
Firstly we create a test table CREATE TABLE test (id INT, sum DECIMAL(10,2)); now we create the trigger for that table: delimiter// create trigger insert_trg before insert on test for each row begin if new.sum<0 then set new.sum=0; endif; end// delimiter;
What is this trigger doing ?
It is a simple field validation.
When we will try to insert into the table (ex insert into test values (1,-3)) this field sum will always be greater than 0 because we change negative values to 0.
How it works:
Create trigger
is creating the trigger with the name insert_trg
The keyword BEFORE
indicates the trigger action time. In this case, the trigger should activate before each row inserted into the table. You can use the AFTER
keyword to activate it after each inserted row in the table.
The Insert
keyword stands for the action what will activate the trigger. You can also use Update
or Delete
.
For each row
means this trigger will be activated for each inserted action.
The OLD
and NEW
keywords enable you to access columns in the rows affected by a trigger. (OLD
and NEW
are not case sensitive.) In an INSERT
trigger, only NEW.col_name
can be used as there is no old row. In a DELETE
trigger, only OLD.col_name
can be used as there is no new row. In an UPDATE
trigger, you can use OLD.col_name
to refer to the columns of a row before they were updated and NEW.col_name
to refer to the columns of the row after they were updated.
By using the BEGIN
… END
construct you can define a trigger that executes multiple statements.
How can a trigger be removed?
Just execute command: Drop trigger trigger_name
.
Conclusion
I hope this little article will prove helpful for those with prior experience in MySQL but who haven’t studied its new features.
3 Responses
Hi,
Just a short question. I it possible to update a table other than the one where the trigger is activated?
Thanks
Baris
No. The values for old and new values are refering on triggerred table.
Well… You can update another table with the data of the current table…
Example:
CREATE TRIGGER testTrigger AFTER INSERT ON table1
BEGIN
UPDATE table2 SET field=NEW.table1Value
END;
Comments are closed.