I've heard on the grapevine that the forthcoming Access2010 has a Data Macros feature which will for the first time add trigger-like functionality to Access data engine tables. Does anybody have an further details on this? For example, can they created in code (ACEDAO, SQL DDL, etc)? Does the Data Macro run if the data is modified from outside of the Access user interface e.g. via ODBC, OLE DB, etc?
Wow, thanks Albert. Great detail as usual. Exciting stuff! :)
onedaywhen
2009-07-22 05:17:14
Something perhaps to test: you say they are table level but when are they checked: at the row level, the SQL statement level or the transaction level? One of the complaints I have about Jet 4.0 CHECK constraints is that, although they are table-level, they get checked at the row level. Consider a somewhat daft CHECK that tests that the table can only have exactly five rows. It is not checked when the table is empty obviously. Then you run an INSERT INTO..SELECT TOP 5.. style SQL statement to INSERT exactly 5 rows all at once...
onedaywhen
2009-07-22 05:23:32
...HOWEVER the CHECK fires when the first row is created, obviously at this point there isn't five rows in the table, so the CHECK bites and the INSERT fails. This demonstrates that CHECK constraints are tested at the row level when they really should be tested at the SQL statement level at least, ideally at the transaction level (I'm thinking here of SQL-92's DEFERRABLE INITIALLY DEFERRED as seen in Oracle); as it is, there is enough loss of utility to consider this a bug, IMO. A Data Macro that similarly fired for each row of a bulk INSERT/UPDATE/DELETE could be a pain.
onedaywhen
2009-07-22 05:32:29
These triggers will not change behavior of the check constraint (unfortantly). If the constraint does not allow the update then the trigger will not fire. You could create a table level validation macro but that’s not sql anymore. These new data macros don’t care nor know about those other issues. Anything that prevents a update means that the data macro will not fire. Validation macros or RI, or check constraints means No update = no trigger. Wrapping the above in a transaction would also not change anything here since all tables updated always rolled back if you bail out of the transaction.
Albert D. Kallal
2009-07-22 05:50:53
Good point about the sequence of events (i.e. CHECK before Data Macro) but what I meant was the timing of the firing of the Data Macro when, for example, INSERTing five rows all at once using a INSERT INTO..SELECT TOP 5.. construct (or similar). Does the Data Macro fire five times i.e. once for each new row? This is what happens for a CHECK constraint which is a problem/bug. I'm not in a position to critique Data Macros (but all sounds good from here), just something you could test so any potential problem could fixed if identified early. Thanks.
onedaywhen
2009-07-23 05:36:53
The trigger would fire 5 times. You could perhaps write a table validation macro which would also fire each time and it could stop things from inserting after 5 records. However this is not sql anymore. You writing code so it seems that wrapping this in a transaction and checking at the end of the code to commit is the best workaround I can suggest. Keep in mind that ACE is not really a true atomic data engine and I don’t think it ever will be. At least now we can make suggestions to the access team where as before it was a deaf ears type of thing. I put this in my list of suggestions.
Albert D. Kallal
2009-07-23 07:49:00
Firing five times is not so good but was what I was expecting to be honest: I've long suspected there is something at the engine level that can't 'see' beyond the row level (the same is still true of SQL Server from what I can tell). Thanks for raising it as a suggestion though. As you say, at least the Access team are listening. I note that in ACE they fixed the DECIMAL sort bug (http://support.microsoft.com/kb/837148) which surprised and impressed me. Thanks again.
onedaywhen
2009-07-24 09:44:20
The list of events pretty much demonstrates that it fires at the row level, don't you think?
David-W-Fenton
2009-08-14 03:38:05
@David W. Fenton: yes, I think you are correct but I think the firing at the row level is less useful than firing at the statement level.
onedaywhen
2009-08-14 07:17:30
@onedaywhen: A file-based database could probably never handle that. You'd need a "traffic cop" serializing the updates in order to avoid major problems with locks from multiple users triggering such events at the table level simultaneously (or close enough to overlap). This feature is excellent feature, and the way they've implemented it has much in common with form events, which are by definition record-based. There's a certain consistency there, and it means a lot of things that previously had to be done in form events can now be done by the db engine, and be accessible by non-Access apps.
David-W-Fenton
2009-08-15 02:31:59
@David W. Fenton: "accessible by non-Access apps" -- but you need Access to create/update/delete the Macro, right? I want to write my database code in SQL of course ;)
onedaywhen
2009-08-17 07:52:15
@David W. Fenton: "This feature is excellent feature, and the way they've implemented it has much in common with form events, which are by definition record-based" -- but not a great fit with SQL code which is statement-based and can obviously involve multiple rows. Are we finally at the point where the Access database engine is only fit for Access Forms applications?
onedaywhen
2009-08-17 08:01:59
@David W. Fenton: "A file-based database could probably never handle that. You'd need a "traffic cop" serializing the updates" -- you could be correct. I guess a 'server' database will always be the correct solution for me :) This could be where I part company with the Access database engine...
onedaywhen
2009-08-17 08:03:12
A:
More information on this new feature was posted today on the MS Access development team's blog and it definitely provides more information on the crucial question raised in the comments above.
David-W-Fenton
2009-08-14 03:32:26