When we were doing a database project we faced to a little problem when we try to check some constraints on a table on insert , update, delete operations. There is a table called 'infos' and there is a column called age. The values of the age should be greater than 0. So to check that constraint we used SQL CHECK constrain. It appears to be accepted by mySQL as a valid one but when inserts happens we noticed that it is not working properly. (negative values can also be added as the age).
create table infos ( id varchar(10) NOT NULL, name varchar(30) NOT NULL, age int , PRIMARY KEY (id), CHECK (age > 0) )After some searching we found that CHECK constraint is not yet implemented in mySQL version that we have and we have to use SQL TRIGGERs for check those constraints. Here are the things what we found . This is not all about SQL TRIGGERs but this was enough to accomplish our task. Sometimes we need to control , check , monitor and manage a group of tables whenever an insert, update, or delete operation is performed. The SQL CREATE TRIGGER statement provides a way for the database management system to actively do those things. The statements specified in the SQL trigger are executed each time an SQL insert, update, delete operation is performed. These SQL triggers cannot be directly called from an application. They will automatically invoked by the database management system when a insert , update or delete operation take place in a relevant table. the definition of the SQL trigger is stored in the data base management system and is invoked by the data base management system. BEFORE SQL triggers These triggers may not modify the tables. Using these triggers we can check for some constraints before add data to the data base table. And we can use them to modify other values based on the data to be inserted or updated or deleted. AFTER SQL triggers These triggers may modify the tables. After make the changes to the table we can check for some constraints or we can update some other tables or the same table values based on the new state of the table. So let's see how we can use SQL triggers to accomplish that above task. First we have to create the table.
create table infos ( id varchar(10) NOT NULL, name varchar(30) NOT NULL, age int , PRIMARY KEY (id) )The Trigger can be define as follows.
delimiter $$ create trigger infos_insert BEFORE insert on infos for each row begin if( new.age <0 )then SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: Invalid age'; end if; end; $$ delimiter ;As the delimiter we define another new one. (the default one is the semicolon ) And this a BEFORE SQL trigger . So the constraints will be checked before the data is entered and data will be entered if and only if the age is greater than 0. (assuming other constraints are satisfied ). If error occurs (age is not valid) message will be displayed. We can catch this in our application also. Inside the 'if section' we can have complex SQL parts also. Assume that we want to insert persons to infos table only such that no 4 persons can have the same age. To check that constraint we can use the below trigger. (Some versions of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table')
delimiter $$ create trigger infos_insert BEFORE insert on infos for each row begin if( (select count(*) as counts from infos where age =new.age group by age )+1 >3 )then SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: Maximum ageGroup limit reached'; end if; end; $$ delimiter ;
