Re: SQL Server Express versus Access (In the World Series of Love)

If your need involve a form, then triggers are not an appropriate tool: they
can do something about the database, but they won't ... trigger ... any
action in the form itself, after a change in the database. The trigger
domain of influence, scope, is limited to the database realm. An EVENT, a
form event, can solve the problem but that is the domain of the User
Interface, not of the domain of the database. Sure, you supplied an example,
and I end up with a discussion about the example rather than on the object
itself, "a trigger", but like Joe Celko, I claim that triggers are quite
often a bad choice, to not say an inappropriate choice, or just half a
solution that cannot be completed efficiently. Again in the case of a form
displaying summary, VBA-events based solution could be simpler and more
appropriate than trigger and ... and what would you use to redisplay the
form when the trigger action, in the database, completed its intended job?


Vanderghast, Access MVP


"dch3" <David H@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3EB59C35-7DD9-40EF-BA1D-A1024BECF3C1@xxxxxxxxxxxxxxxx
Ah, but storing computed values while not the theoretical ideal is
sometimes
necessary. Case in point if you're working with a hotel reservation system
and need an screen that displays rooms available for sale a summary table
would be acceptable as the amount of time necessary to compute the
availability (or quantity sold) would take (seemingly) forever.

"Michel Walsh" wrote:

As far as the triggers are concerned, the first case is something to
avoid
(storing computed values), unless you want to archive something, but
then,
if you archive your data, you don't expect data to change and have other
mechanics to cover such exceptional cases. For the second example, it is
something related to the UI, not to the database engine. Sure, triggers
may
have their usefulness, for updateability, but Jet has already
updateability
covered automatically over saved query while MS SQL Server requires
INSTEAD
OF triggers to achieve the same, say, over view, so, advantage to Jet.
Sure,
MS SQL Server may have a horse shoe maker right out of the box, and Jet
does
not, but if we don't own horse... is that an advantage?

Vanderghast, Access MVP


"dch3" <dch3@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B012073F-7738-47C4-B273-1B5568C88675@xxxxxxxxxxxxxxxx
I'm a bit concerned that most of the posts seemed to deal with the
interface
and not the inner workings of the underlying Engines - perhaps the
thread
should have been entitled SSE versus Jet - as I now realize that was
the
question at hand. No one mentioned that SQLServer supports triggers
(how
many
times have you had a summary table that you've had to update a value
when
the
detail changes? and how often do you have to remember that if you build
a
new
screen that allows the detail to be edited that you also have to call
the
code that updates the corresponding header record), or the fact that
SQL
Server security can be integrated with Windows/Active Directory - not
to
mention that it can be setup at the COLUMN level, I would assume that
upsizing from SQL Server Express to Big Daddy SQL Server would be
easier
(than Access to SQL Server) as Express is for all basic purposes SQL
Server
with limitations (only 1 processor, max database size 4GB).






.

Relevant Pages

  • Re: New to SQL server
    ... it is called triggers. ... ANYTHING THAT YOU CAN DO IN JET IS BABY TALK COMPARED TO WHAT WE CAN DO ... anything that you can do in JET that I can't do in SQL Server ... self-referencing FKs and inline constraints. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: SQL Server Express versus Access (In the World Series of Love)
    ... As far as the triggers are concerned, the first case is something to avoid ... have their usefulness, for updateability, but Jet has already updateability ... OF triggers to achieve the same, say, over view, so, advantage to Jet. ... MS SQL Server may have a horse shoe maker right out of the box, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Audit trail for web application
    ... This is more of a SQL Server question than an ASP.NET question. ... with implenting auditing at the application level or using table triggers is ... There are also 3rd party database auditing tools that should do ... > I am trying one approach with the help of triggers. ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Write conflict error after adding trigger to table
    ... I have inherited an Access adp that directly connects to SQL Server. ... that database. ... stored proc in the database window instead of through the form, ... is preventing me from rolling these triggers to production where other apps ...
    (microsoft.public.access.adp.sqlserver)
  • Re: trigger counter part
    ... the Jet data engine does not support triggers or similar. ... I said constraints in Jet 4.0 are better than SQL Server ...
    (microsoft.public.access.tablesdbdesign)