Trigger on table insertion

[John]

Hi

Is it possible to add a trigger on record insert on a table so I can check
for certain conditions when a record had been inserted into a table?

Thanks

Regards

 

[Allen Browne]

No. if your data is stored in Access tables, JET has never provided
table-level triggers.

If the data is attached from a different db such as SQL Server, you may be
able to add the triggers there.

Alternatively, if the data is entered through a form, you can use the form's
AfterInsert event procedure to simulate the trigger.

 

[Guest]

Triggers were the way that DBA's enforced referential
integrity in relational DBMS's. When Access/Jet was
designed 15-20 years ago, Access/Jet instead got a
new and better way: Declarative Referential Integrity.

When the rest of the world caught up with Access/Jet,
by retro-fitting DRI to their DBMS's and modernising their
SQL, triggers were left behind as the way to handle rare
complex calculations that could not be handled by declaration
in the DBMS.

So, two points: (1) Access/Jet is no longer the market leader
in DBMS design. You get more and better if you use
Access/SQL Server. (2) DB design used to be full of triggers.
It is not any more. Look to see if your 'certain conditions'
can be better handled by field restrictions or DRI.

10 years ago I would have said that the best way to handle
remaining cases was with VB code and DAO transactions,
but since DAO transactions against SQL Server were broken
8 years ago and never fixed, that leads you to a dead end
with no realistic upgrade path to SQL Server. If upgrade to
SQL Server is a future possibility, and you can't fix your
problem now with better design, better to do your transactions
now in SQL Server, rather than waiting till later.

(david)