effect of trigger in Access?

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

With Access 2000 or 2003, is there a trigger or onChanged method? I
would like to be able to do a mailto if certain fields change in my db.

Thanx.
 
Kevin said:
With Access 2000 or 2003, is there a trigger or onChanged method? I
would like to be able to do a mailto if certain fields change in my
db.
Click to expand...

When using the default Jet database (tables in an mdb file) there are no table
level events (triggers) as there are in server based systems like SQL Server.
Only when the changes are done via forms can use the varuious events that
automatically fire in forms for stuff like this.

Of course Access can easily be used with tables on a server database and then
Triggers on that system could be set up.
 
Rick, completely correct, but note that there is a more
complicated way of understanding that. :-).

When using the default Jet database (tables in an mdb file),
you can use declarative referential integrity to cascade
updates an deletions through your other tables. So unlike
other database systems, there was not a need to handle
table level events (triggers).

Other database systems like SQL Server which couldn't
handle declarative referential integrity have had it retro-fitted,
and also have retained table level events (triggers) for more
complex actions like those handled by form level events in
Access.

In contrast, Access has stagnated for 10 years. SQL Server
got DRI, Access/Jet did not get table level events.

(david)
 
What I was hoping for was record-level locking and record-level
triggers. I'm thinking I should be poking around in a MySQL or SQL
Server area.
 
You can do what you are asking with Access but you can't use a trigger. You
need to use a form or control event. Depending on your actual requirements
one of the following four events will work for you:
1. Form's AfterUpdate event
2. Form's BeforeUpdate event
3. Control's AfterUpdate event
4. Control's BeforeUpdate event

The Change event of a control is inappropriate for this task since it runs
for EVERY character typed in the field. So if your field has 5 characters,
the change event will run 5 times whereas the before and after update events
run only once per change.
 
Even if you use SQL Server, you want to avoid triggers as much as
possible. They are good for logging, etc., but not as part of a
transaction because they extend the life of a transaction. This can
cause unintended blocking and deadlocks, depending on your data and
application architecture.

--Mary
 
Back
Top