0

I was looking at a tutorial on triggers for SQLite. The tutorial said that a use of triggers could be for data validation.

The following code was given as an example:

CREATE TRIGGER validate_email_before_insert_leads BEFORE INSERT ON leads
BEGIN
    SELECT
    CASE
        WHEN NEW.email NOT LIKE '%_@__%.__%' THEN
        RAISE (ABORT, 'Invalid email address')
    END;
END;

My first inclination would be to use a check constraint for validation instead. I did a Google search for situations where one should use a check constraint and when a trigger would be preferable.

Question
My question is, what specific situations should one use a check constraint and what situations should one opt to use a trigger?

  • This is a general question and not related to SQLite specifically. – Dodzi Dzakuma Oct 10 '16 at 16:51
2

You haven't specified a tag in your question so I'll assume that you're asking a generic question despite the SQLite reference (if you're asking a SQLite-specific question, please update your tags).

Generally, I'd suggest using a trigger when you can't use a check constraint. Different database engines have different levels of support for what you can do in a check constraint. I don't know of any database engine, for example, that would currently allow you to define a check constraint that references data in a different table or data in a different row of the same table.

If your database engine allows you to define the email format rule in a check constraint, I would use a check constraint. If your email validation rules were more complex, however, or you were going to have the same validation rules in many places, you may want to encapsulate them in a function. Once you do that, most database engines are going to require you to use a trigger. For example, you might want to perform more detailed validations that involve checking to see if the domain is valid, whether the email domain on the row in the leads table was not already in the customer table, etc. At that point, you're doing something more complicated than what a check constraint can manage.

  • 2
    " I don't know of any database engine, for example, that would currently allow you to define a check constraint that references data in a different table or data in a different row of the same table." Firebird says that it can (reference other tables). Haven't tried it. But Postgres has EXCLUDE constraints which is a generalization of UNIQUE constraints and this is related to other rows in the same table. – ypercubeᵀᴹ Oct 10 '16 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.