CODEWITHSUNDEEP
sqlsql-serverms-accesstriggers
Draco
Draco

Reputation: 16364

Are writing triggers in MS SQL server the same as writing them in MS Access?

I have written the following trigger in SQL server:

create trigger test_trigger
on invoice -- This is the invoice table
for insert
as
declare @invoiceAmount int    -- This is the amount specified in the invoice 
declare @custNumber int       -- This is the customer's id

--use the 'inserted' keyword to access the values inserted into the invoice table
select @invoiceAmount = Inv_Amt from inserted 
select @custNumber = cust_num from inserted


update customer
set amount = @invoiceAmount
where Id = @custNumber

Will this be able to run in MS Access or is the syntax different?

Upvotes: 0

Views: 919

Answers (5)

JeffO
JeffO

Reputation: 8043

They may be coming in Access 2010? http://blogs.msdn.com/access/archive/2009/08/13/access-2010-data-macros-similar-to-triggers.aspx

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135141

Access doesn't have triggers

Your trigger that you show here will bomb out since it does not take into account multirow updates the moment someone updates more than one row (and don't say it won't happen because it will better to practice some defensive coding)

Triggers fire per batch not per row, please read Multirow Considerations for DML Triggers

join inserted pseudo table and the invoice table instead to update the values...that works for 1 and more than 1 row

Upvotes: 2

onedaywhen
onedaywhen

Reputation: 57073

The Access database engine (formerly called Jet) does not have triggers and regardless has no control-of-flow syntax e.g. a PROCEDURE must consist of exactly one SQL statement.

Tell us what you really want to do and there could be an alternative syntax.

For example, you could create a new key using a UNIQUE constraint on invoice, (cust_num, Inv_Amt), a FOREIGN KEY customer (id, amount) to reference the new key, a VIEW that joins the two tables on the FOREIGN KEY columns and exposing all four columns, then INSERT into the VIEW rather than the table 'invoice'; you may want to use privileges to prevent INSERTs to the base table but user level security was removed from the new Access 2007 engine (called ACE).

But, if you don’t mind me saying, I think your trigger doesn't reflect a real life scenario. A column vaguely named 'amount' in table 'customer' to hold the most recent invoice amount? What about when the inserted logical table contains rows for more than one customer? As I say, I think you need to tell us what you are really trying to achieve.

Upvotes: 3

user114600
user114600

Reputation:

I've never come across triggers in Access unless it's dealing with ADP on SQL Server. So your answer is yes, it's the same if you're on SQL Server for the backend, and no if the table is stored in Access.

Upvotes: 0

Adam Ruth
Adam Ruth

Reputation: 3655

MS Access doesn't have triggers.

That is, the the Access Jet engine (which creates .mdb files). If Access is connecting to a database server, then it will use whatever triggers are in that database.

Upvotes: 0

Related Questions