By using database triggers that run BEFORE an Insert, values that are being Inserted can be modified immediately before the database is actually modified. A common use of the BEFORE trigger is to verify the input values of the INSERT, or modify the values accordingly, or return a valid Foreign Key from somewhere else, that can be used in the subsequent INSERT, all in one hit.

There are two types of Before-Insert Triggers – ‘for each row’ level and ‘statement’ level.

The distinction between the two is how many times the code within the trigger is executed, and at what time.

 ‘For-each-row’ triggers fire on INSERT and execute once for each row that is affected by the INSERT. If no rows are affected by the INSERT command, the trigger will not execute any code within the trigger. ‘Statement’ level triggers are fired once regardless of how many rows are affected by the INSERT.

Surprisingly few databases support true ‘Before-Insert-Triggers’ !

Least of all, both ‘row-level’ and ’statement’ level types.

Gupta SQLBase supports both ‘row-level’ and ’statement’ level types, as does Oracle.

MS SQLServer does not. (At least not without major compromises using an ‘Instead-of-Trigger’, which is a completely different thing).

Here's a sample where a value is retrieved from another table and inserted directly into the Table firing the INSERT Trigger, with no other coding required. Magic.