One from the mid tier. It would just require the insert. Internally, the trigger would work a bit like a stored procedure in that it would, if an "after" trigger, check the two tables. If the conditions were correct, that would be the end of it it. If not correct, the trigger would rollback the insert.
In SQL Server, you could write an "Instead Of" trigger to check the data and two tables before doing the insert. If everything is ok, then the insert would happen. If not ok, no insert and no rollback.
I DO wish that MS would build true BEFORE triggers like what Oracle has. Those are SO SIMPLE. "Instead of" triggers in SQL are a PITA, especially for UPDATEs (not applicable here, though).
So, the bottom line sounds like two trips through the NIC if it's coming from the mid tier and it has to do the same work as a trigger or 1 trip through the NIC and do the work in a trigger.
Because of the added benefit that the trigger would also enforce the rules for other apps and non-app procs and human driven inserts on the back end, the trigger wins IMHO.
If caching on the WebServer is done, I'm still thinking the trigger wins because the cache does not need to be kept up to date when using a trigger.
I believe that, unless you have some form of proof as to an advantage of doing all this through the mid-tier without the use of a trigger, I'll have to continue to be "contentious" and say the trigger wins. The only thing that would be better is a stored procedure because it would work very much like a true BEFORE trigger and not do the INSERT if conditions were wrong. The disadvantage there is there would be no enforcement of the rule if someone did a direct INSERT using the likes of SSMS or even some other proc.