How to do the data validation directly from MySQL? Trigger mysql example.
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:
view sourceprint?
01.-- Firstly we create a test table
02.CREATE TABLE test (id INT, sum DECIMAL(10,2));
03.-- now we create the trigger for that table:
04.delimiter//
05.create trigger insert_trg before insert on test
06.for each row
07.begin
08.
if new.sum<0 then
09.
set new.sum=0;
10.
endif;
11.end//
12.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
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:
view sourceprint?
01.-- Firstly we create a test table
02.CREATE TABLE test (id INT, sum DECIMAL(10,2));
03.-- now we create the trigger for that table:
04.delimiter//
05.create trigger insert_trg before insert on test
06.for each row
07.begin
08.
if new.sum<0 then
09.
set new.sum=0;
10.
endif;
11.end//
12.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
