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



Question:

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?


Solution:1

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.


Solution:2

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


Solution:3

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


Solution:4

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.


Solution:5

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »