Triggers in SQLite

Although triggers are common in databases. I seldom use it. Recently there is a need to pre-check before an insertion whether the number of records in a certain condition is within the quota set. Also, there is a need to check whether the specific field value exists already. Finally, there is a need to check whether one field content is in the list of another.

Under normal condition, I would set up three queries to check for it before doing the insertion of records. It will be untidy to code if this is done the normal way. Triggers then is the more cleaner way to do so.

The setting up is quite straight forward.

Create trigger before insert on for each row begin select

It then followed by the list of conditions

case when ((select....) > ) then raise(abort, "error message")
when ((select from where = NEW. ) is not null) then  raise(abort, "error message")
when ((select from where = NEW.) is not null) then raise(abort, "error message") end;
end;

In the script we just need a normal insert SQL with a exception check, (PHP try and catch) to get the error message. Just get the different message and act accordingly. It is very much cleaner in coding without much coding in the application.

This trigger can be inserted externally using normal SQL procedure. It can also be removed the same way. Obviously, same name trigger must be removed first before insert.

There are much more type of triggers. This one only talks about INSERT BEFORE.