Ok, a little more information.
Does the data macro run if the data is modified from outside the Access user interface, for example. through ODBC, OLE DB, etc.
Yes Yes.
These true triggers are at the data level. Thus, using ado, dao or even editing tables in the table view in ms-access means triggering triggers. These triggers will also include a scenario in which you do not even have access to ms-access on your computer.
Of course you will need JET (in the rest of this article I will use the term ACE). Remember that only in 2007 access did the access team acquire ownership of the JET. Now they can improve this engine for their needs. We started to see some changes in ACE in 2007, and now we will see LOT more changes in 2010. Some of these features still remain the NDA for me ... but I will spill as much as I can here.
For example, can they be created in code (ACEDAO, SQL DDL, etc.)?
In code, perahps, but not with SQL ddl. Triggers are not sql commands. Keep in mind that for Oracle or Sql servers or MOST systems on the market, they all still have their own custom language for their triggers.
In the new ACE, you thus write what is called a data macro. You are using the macro editor in ms-access. These are old-fashioned access macros, but the editor is completely redone. New macros also have routines that are cyclic, if then blocks and a record set type. Remember that we got the opportunity to create temporary variables in 2007 macros. In 2010, this extension also includes local variables and higher new functions.
The syntax of existing macros is similar to the syntax of previous versions. However, we have a new spot IDE along with all the commands added above. Now we have two types of macros. UI macros (as before), and now new so-called data macros. It would not be wise for a table-level macro to display error messages on the screen. Thus, any registered error is included in the table. There are also several new logging commands that allow you to write your own errors to this table. Brilliant and simple design here. Here is what this error log table looks like:
alt text http://www.kallal.ca/test/merrors.png
In the world of huge IDEs, massive frames and terrifying huge code libraries needed to update a simple row in a table, I can say that data macros are the most refreshing. They are very similar to how the original dbaseII works. They are very simple, but deadly power is full because they work at the table level.
For example, here is a data macro that will support an inventory of the summary table. In this example, I assume that master inventory entries exist. And then we have a table of order details. If the user edits any value in the order table, we want the main table to automatically update inventory levels.
So, we have: I have two tables
tblFruits: main data fruit inventory table id (autonumber) Fruit text OnHand (number value of fruit in inventory) tblFruitsOrder id (autonumber) Fruit_id (FK to tblFruts.ID) QtyOrder (number of fruit to order)
This is a simple test.
So, we will have a trigger in tblFruitsOrder that will update + maintain inventory levels in tblFruits when the QtyOrder field is edited or changed.
Here's what the macro looks like:
alt text http://www.kallal.ca/test/macro1.png
Notice how easy it is. Please note that in the IDE, if you click on the code section, you will get editing the old-style macro type in which you can enter values for the command (this is similar to pre 2010, but now the parameters and editing take place in the code, not the panel, which appears at the bottom of the screen in previous versions).
Here is another screenshot in which I play with the ability to loop and call a subroutine.
alt text http://www.kallal.ca/test/macro22.png
In the above example, we can use msgbox, etc., because it is not a table level macro. Thus, the commands available to you are automatically limited when you write a table-level macro, rather than a regular macro. The terminology we used to distinguish between two types of macros is user interface macros and data macros.
Above is all that I can talk about in my pubis. However, there are a few REALLY neat features o in these macros that I just can't talk about publicly, since I'm still under the NDA for those features. But here are even more pleasant surprises.
Here is a screenshot of the types of table triggers that we have:
alt text http://www.kallal.ca/test/macro3.png