Home » SQL & PL/SQL » SQL & PL/SQL » before update trigger (oracle 10g)
before update trigger [message #417099] Thu, 06 August 2009 01:21 Go to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 #417136 is a reply to message #417106] Thu, 06 August 2009 04:50 Go to previous messageGo to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hi,

thanks for the tip. In fact it is just as I feared. Ok, i'll thing of something.


cheers,
Didier
Re: before update trigger [message #417144 is a reply to message #417099] Thu, 06 August 2009 05:02 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: before update trigger [message #417187 is a reply to message #417148] Thu, 06 August 2009 07:21 Go to previous message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
hmmm, worth digging! Wink

thanks,
Didier
Previous Topic: sql query to find fourdays before the current date excluding weekends
Next Topic: SQL NULL
Goto Forum:
  


Current Time: Fri Dec 02 16:30:01 CST 2016

Total time taken to generate the page: 1.05038 seconds