There are multiple ways to accomplish validation prior to insert or save.
1) Unique Index or other Database Constraint
The simplest and most effective validation solution is to create database constraints that enforce the conditions you want. The database will automatically raise an error if a constraint is violated and dbFront can show the error.
To prevent duplicates create a unique constraint on the selected fields. To prevent customers with duplicate firstname and lastname combinations add the following.
ALTER TABLE Customers ADD CONSTRAINT UC_Customer UNIQUE (FirstName, LastName);
To prevent invalid column data you can add a check contraint. The following validates that any active Customers must be at least 18 years old.
ALTER TABLE Customers ADD CONSTRAINT CHK_CustomerAge CHECK (Age>=18 AND Status='ACTIVE');
The available validation constraint types are:
- NOT NULL Constraint
- CHECK Constraint
- UNIQUE Constraint
- PRIMARY KEY Constraint
- FOREIGN KEY Constraint
2) Table Trigger
Another option is to add a table trigger. Depending upon your needs this could be an Insert, Update or an Delete trigger. Triggers need to be very fast so the checks should be as simple as possible.
Table triggers have the ability to look across multiple tables to perform their validation. The table trigger can correct data on the fly and if necessary revert the changes and return an error.
NOTE: The examples are simplistic and could idealy be done via a UNIQUE contraint.
A complete MySQL trigger looks as follows.
CREATE TRIGGER `customer_BEFORE_INSERT` BEFORE INSERT
ON `customer` FOR EACH ROW
IF exists (select 1 from customer c where c.lastname = NEW.lastname) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT =
'Duplicate Customer Lastname not allowed';
A complete SQL Server version of the trigger looks as follows...
CREATE TRIGGER tiuCustomerTrigger ON Customer AFTER INSERT, UPDATE AS
-- Prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
declare @counter INT;
-- Note: With an AFTER INSERT, UPDATE trigger the record is already inserted or updated
-- so we are looking for existing duplicates. (count > 1)
SELECT @counter=count(*) FROM saleslt.Customer c JOIN inserted n on c.FirstName = n.FirstName AND c.LastName = n.LastName;
IF (@counter > 1)
RAISERROR ('Duplicate Firstname, Lastname found.', 16 /* Severity */, 1 /* State */);
3) Run Procedure Button
You can also use a Stored Procedure to perform validation and call it from a RunProcedure action button. See: Run Procedure
Any form changes will be saved prior to running the Stored Procedure and any error will automatically roll-back all changes.
Similar to a Table Trigger, the table procedure can perform any validation or correction as desired and it can return an error.
In addition, Run Procedure action buttons support multiple return types including a table listing or even a custom report.
4) Wizard Interface
For very complex forms or validation, it might be a good idea to implement a wizard interface. This basically adds a status field to the table record and allows intermediate saves at any stage.
As the user moves the record closer to the final stage more information is required. There is also the option of a detailed custom listing of record issues.
For implementation details see: Wizard for Data Entry