before update trigger [message #417099] |
Thu, 06 August 2009 01:21  |
 |
didiera
Messages: 134 Registered: August 2007 Location: Mauritius
|
Senior Member |
|
|
Hello everyone,
it's been a while heh? I have a question for you guys. In a forms application I have to implement a security mecanism against update of rows in a table. To implement this, I have used a before update trigger right on the table in the database to check the value of a field which tells if the record is protected or not against update (field = 0 it's open to updates, field =1 no more updates on this record). This works fine so far. However I have this condition to consider too : Even if a record is locked, all users should yet be free to modify only one specific field on that same table. Let's call it 'remarks' for instance.
I thought of having another trigger before update of remarks on the same table to allow the changes on 'remarks' to pass through.
My questions :
1) What code should I place in my before update of remarks stuff?
2) Won't the initial before update on my_table override this provision and cause any attempt of update of emarks to fail ?
regards,
Didier
|
|
|
Re: before update trigger [message #417106 is a reply to message #417099] |
Thu, 06 August 2009 01:37   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
You cannot handle this in two separate triggers as described: the general one will prevent the update.
You will have to code for the exception in the general trigger.
|
|
|
|
Re: before update trigger [message #417144 is a reply to message #417099] |
Thu, 06 August 2009 05:02   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
In this case I'd be tempted to ignore third normal form and put the remarks on a seperate table.
Otherwise you're going to have to code the trigger so that it checks every column to see if remarks is the only one modified.
|
|
|
Re: before update trigger [message #417148 is a reply to message #417144] |
Thu, 06 August 2009 05:07   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
An alternative approach might be (not sure if it fits requirements, though) to create a view with an INSTEAD OF trigger.
The actual (rewritten) update may update the REMARKS column any time it's changed, ignoring any other changes for non-updatable records, whereas all changes are sent for updatable records.
|
|
|
|