neoartz237
Tensai
- Local time
- Today, 07:05
- Joined
- Feb 12, 2007
- Messages
- 65
How can I use it in Access? Please help 
Triggers
- Thread starter neoartz237
- Start date
- Local time
- Today, 09:05
- Joined
- Feb 28, 2001
- Messages
- 30,501
Triggers (in the ORACLE sense of the word) do not exist in Access.
You can have triggers in certain highly specific situations but not as part of a table. It would be part of a FORM on which you do data entry. You would put validation triggers on the individual controls in BeforeUpdate or AfterUpdate events. You would place overall validation triggers in the form's BeforeUpdate or AfterUpdate events. (In both cases, I vote for using the BeforeUpdate events, but there are arguments the other way.)
There are other events you can use to trap something, but here is the key to understanding what you can and can't do along these lines:
You cannot place code in a table or query. No code = no triggers.
You can place code in a FORM. Triggers can exist there as part of the form.
You can place code in a report but if the report tries to update something from its bound recordset, you could have trouble. Nevertheless, triggers can exist in a report.
You can run code from a MACRO if necessary, though such code is rarely bound to a recordset a priori. If you do explicit recordset operations, conditional code (triggers) can exist in that environment.
Access is a small-business tool and as such is great. When you get to a point where you start needing triggers, you are graduating to the need for a big-business tool.
NOTE: We have ORACLE at our shop and use triggers now and then. But I happen to be against ANY use of triggers when another method exists that is more localized. E.g. If you never allow data entry except through forms, then don't build a database trigger. Put the smarts in the forms. That way you remove excess baggage from your database. Put the code where it will do the most good with the least cost.
We have a database in another department where over 3300 business rules are implemented through triggers. But remember, triggers aren't automatic. (Purists, before you jump on my stuff, let me finish.) A trigger occurs when you touch a field and some criteria are met. Both the fact of the touch and the comparison of criteria require code to be executed in the database because triggers are not a hardware function, they are a software function. That is, you execute a trigger because that table entry has a software flag or link indicating the existence of a trigger and requiring execution of the trigger's qualifier code.
I.e. a trigger on Update of field X to a specific value requires that you know
1. whether field X was touched
2. whether it was an update (as opposed to, say, a delete or insert)\
3. whether the new value meets the trigger criteria.
And if any one of those tests fail, you go on to the NEXT trigger tests. Until you have run out of trigger tests. Putting too many triggers in a database, particularly on a popular table, means you will have to evaluate trigger conditions so often that performance drastically suffers.
Before anyone asks, it is a different project than the one I support. If anyone had consulted me on this issue, I would have told them to drop that design like a red-hot rock. But that didn't happen and the company that came up with that solution is now forced to support their own behemoth that is hard-pressed to do a simple personnel application. They have to buy top-of-the line Sun multi-processor boxes to keep up with the load. And they have to try to cluster them so the batch processing can finish before the next data load comes through.
Whereas on my project, with all "triggers" enforced at the data entry forms level, we have plenty of idle time on the one (non-Sun) box for my project. I'll leave it there so nobody's feathers get ruffled needlessly. But my experience with triggers is that they are dangerous if used unwisely.
On the other hand, if you have stock in vendors of high-end boxes, go ahead and use LOTS of triggers. Your stock will be worth more very soon.
You can have triggers in certain highly specific situations but not as part of a table. It would be part of a FORM on which you do data entry. You would put validation triggers on the individual controls in BeforeUpdate or AfterUpdate events. You would place overall validation triggers in the form's BeforeUpdate or AfterUpdate events. (In both cases, I vote for using the BeforeUpdate events, but there are arguments the other way.)
There are other events you can use to trap something, but here is the key to understanding what you can and can't do along these lines:
You cannot place code in a table or query. No code = no triggers.
You can place code in a FORM. Triggers can exist there as part of the form.
You can place code in a report but if the report tries to update something from its bound recordset, you could have trouble. Nevertheless, triggers can exist in a report.
You can run code from a MACRO if necessary, though such code is rarely bound to a recordset a priori. If you do explicit recordset operations, conditional code (triggers) can exist in that environment.
Access is a small-business tool and as such is great. When you get to a point where you start needing triggers, you are graduating to the need for a big-business tool.
NOTE: We have ORACLE at our shop and use triggers now and then. But I happen to be against ANY use of triggers when another method exists that is more localized. E.g. If you never allow data entry except through forms, then don't build a database trigger. Put the smarts in the forms. That way you remove excess baggage from your database. Put the code where it will do the most good with the least cost.
We have a database in another department where over 3300 business rules are implemented through triggers. But remember, triggers aren't automatic. (Purists, before you jump on my stuff, let me finish.) A trigger occurs when you touch a field and some criteria are met. Both the fact of the touch and the comparison of criteria require code to be executed in the database because triggers are not a hardware function, they are a software function. That is, you execute a trigger because that table entry has a software flag or link indicating the existence of a trigger and requiring execution of the trigger's qualifier code.
I.e. a trigger on Update of field X to a specific value requires that you know
1. whether field X was touched
2. whether it was an update (as opposed to, say, a delete or insert)\
3. whether the new value meets the trigger criteria.
And if any one of those tests fail, you go on to the NEXT trigger tests. Until you have run out of trigger tests. Putting too many triggers in a database, particularly on a popular table, means you will have to evaluate trigger conditions so often that performance drastically suffers.
Before anyone asks, it is a different project than the one I support. If anyone had consulted me on this issue, I would have told them to drop that design like a red-hot rock. But that didn't happen and the company that came up with that solution is now forced to support their own behemoth that is hard-pressed to do a simple personnel application. They have to buy top-of-the line Sun multi-processor boxes to keep up with the load. And they have to try to cluster them so the batch processing can finish before the next data load comes through.
Whereas on my project, with all "triggers" enforced at the data entry forms level, we have plenty of idle time on the one (non-Sun) box for my project. I'll leave it there so nobody's feathers get ruffled needlessly. But my experience with triggers is that they are dangerous if used unwisely.
On the other hand, if you have stock in vendors of high-end boxes, go ahead and use LOTS of triggers. Your stock will be worth more very soon.