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
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.
Insert keyword stands for the action what will activate the trigger. You can also use
For each row means this trigger will be activated for each inserted action.
NEW keywords enable you to access columns in the rows affected by a trigger. (
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
END construct you can define a trigger that executes multiple statements.
How can a trigger be removed?
Just execute command:
Drop trigger trigger_name.
I hope this little article will prove helpful for those with prior experience in MySQL but who haven’t studied its new features.
Just a short question. I it possible to update a table other than the one where the trigger is activated?
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…
CREATE TRIGGER testTrigger AFTER INSERT ON table1
UPDATE table2 SET field=NEW.table1Value
Comments are closed.