stored Procedures or Triggers ???

N

Najm

Hi, I'm new to access 2003 and I was told that it supports triggers and
stored procedures. Can anyone confirm this info and if it's a yes, then how?
I've looked at the help and it doesnt say much about this issue. Thanks guys
 
A

Arvin Meyer

The JET engine does not support Triggers. Saved JET queries can do on the
workstation what stored procedures do on the server. The MSDE engine, being
a version of SQL-Server, supports both Triggers and Stored Procs.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
N

Najm

thanks for ur quick answer, but to synthesize all this, do u mean i should
use SQL Server, or is there a less radical way(workaround) to do it. Cause I
am not supposed to use any "server-based DB". for the stored procedures, how
can i do it?
Thanks again dude
 
A

Arvin Meyer

The MSDE is a "cut-down" version of SQL-Server that doesn't have the
development tools. Some of those are available using an ADP front-end. It
can be run on a local PC (but so can SQL-Server for development purposes)

Using JET with an MDB database, much of the Trigger functionality (Cascading
updates and deletes) are available from the Relationships window (DRI in
SQL-Server). Local saved Access queries do the exact same work as Stored
Procs, but do it on the workstation instead of the server. True Stored
Procedures, by definition, can only be run on a server.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
D

david epsom dot com dot au

True Stored Procedures, by definition, can only be run
on a server.
Click to expand...

????

I was taught a different definition: procedures come in
two types: interpreted SQL, and stored SQL. Interpreted
SQL came from your executable, and went to your SQL
translation layer. Stored SQL came from your database,
was already precompiled, and went to your database engine
layer. The ability to store SQL in the database was a
characteristic of a relational database, not a characteristic
of the connection or storage schema. ?????

(david)
 
A

Arvin Meyer

Perhaps definition is not the best choice of words. Stored Procedures are
run on a server database though. Even if that server service is running on a
local machine.

As far as precompiled. That's true after the first compilation. But that's
true of Access queries as well. They are compiled after first use, then
decompiled with each compaction of the front-end. Views are also compiled on
first use, but they are only cached for a session.

The query plan often changes with the number of records required, so
recompiling is often more efficient. Unlike Access queries, Stored Procs are
only compilable once, so they are often dropped and rebuilt like this:

IF EXISTS (Select * from sysobjects where id =
object_id('dbo.sp_DeleteKeyDups') and sysstat & 0xf = 4)
drop procedure dbo.sp_DeleteKeyDups
GO

Create Procedure sp_DeleteKeyDups
....

Writing the SQL like this assures that a new compile will take place each
time the procedure is run.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Top