Table 1: tblCountry (‘id’ INT PK, ‘countrynm’ VAECHAR)
Table 2: tblState (‘sid’ INT PK, ‘state’ VARCHAR, ‘country_name’ VARCHAR NOT NULL, ‘country_id’ INT FK NULL)
Both tables are not in Master/Detail relationship. I have created a MySQL Before INSERT Trigger on tblstate. I have frmSTATE with two VARCHAR fields (‘state’ and ‘countryname’).
The Analogy:_
I want user only enter State name with corresponding Country either from UI form or Excel import without Foreign Key (Country.id) on it. The application will validate the country name from the STATE entry form with the pre-existing country records sitting into Country Table. If the validation passes then the record will be saved in state table with enforced corresponded foreign key (countryid) into State Table. So that later on we can make easy query between country and state by joining them if necessary. In the same time if the validation fails (i.e. if country name field on STATE form or excel file is NULL or wrong country name for potential SQL injection attack or any reason) then the application should not proceed rather throw error message.
The Procedure to accomplish this Analogy:_
I have accomplished this at both application form and database table level events for security reason.
1.Put this following code in BEFORE RECORD ADDED/UPDATED event on State Add/Edit/Import pages:
global $conn;
$strSQLExists = "select * from country where countrynm='" . $values["country_name"] . "'";
$rsExists = db_query($strSQLExists,$conn);
$data=db_fetch_array($rsExists);
if($data)
{
// if record exists do something
return true;
}
else
{
$message = "Country" . $values["country_name"] . " is not exists. Add country first";
return false;
}
2.Then create a BEFORE INSERT TRIGGER on State table at MySQL db lavel with the following code:
DROP TRIGGER foo;
CREATE TRIGGER
foo BEFORE INSERT ON
stateFOR EACH ROW
BEGIN
IF NEW.country_name = ' ' THEN
SET NEW.country_name = NULL;
ELSEIF NEW.country_name !=' ' THEN
SELECT country.id INTO @country_id FROM country WHERE countrynm=NEW.country_name;
SET NEW.country_id=@country_id;
END IF;
END;
3.Then create a BEFORE UPDATE TRIGGER on State table at MySQL db lavel with the following code. This will change country.id when country_name is updated:
CREATE TRIGGER foo1 BEFORE UPDATE ON state
FOR EACH ROW
BEGIN
IF NEW.country_name != ' ' THEN
SELECT country.id INTO @country_id FROM country WHERE countrynm=NEW.country_name;
SET NEW.country_id=@country_id;
END IF;
END
That’s it. If anyone has best easy but most secured solution please let us knows. Thanks.
