在MS SQL服务器中编写触发器与在MS Access中编写触发器相同吗？

I have written the following trigger in SQL server:

我在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?

这可以在MS Access中运行还是语法不同?

5 个解决方案

#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.

Access数据库引擎(以前称为Jet)没有触发器,并且无论如何都没有控制流语法,例如一个程序必须只包含一个SQL语句。

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).

例如,您可以使用发票上的UNIQUE约束(cust_num,Inv_Amt),引用新密钥的FOREIGN KEY客户(id,金额)创建新密钥,在FOREIGN KEY列上连接两个表的VIEW和暴露所有四列,然后插入VIEW而不是表'发票';您可能希望使用特权来阻止INSERT到基表,但是从新的Access 2007引擎(称为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.

但是,如果你不介意我说,我认为你的触发器并不反映现实生活中的情况。表'客户'中的一个名为'amount'的列,用于保存最新的发票金额?插入的逻辑表何时包含多个客户的行?正如我所说,我认为你需要告诉我们你真正想要实现的目标。

#2

Access doesn't have triggers

Access没有触发器

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

每批不触发每行触发,请阅读DML触发器的多行注意事项

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

加入插入的伪表和发票表来更新值...适用于1行和1行以上

#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

他们可能会进入Access 2010? http://blogs.msdn.com/access/archive/2009/08/13/access-2010-data-macros-similar-to-triggers.aspx

#4

MS Access doesn't have triggers.

MS Access没有触发器。

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.

也就是说,Access Jet引擎(创建.mdb文件)。如果Access连接到数据库服务器,那么它将使用该数据库中的任何触发器。

#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.

除非它在SQL Server上处理ADP,否则我从未遇到Access中的触发器。所以你的答案是肯定的,如果你在后端使用SQL Server,那就是一样的,如果表存储在Access中则不是。

#1