Can this be done with a trigger?
in [Oracle Help]
|
From: DA Morgan on 6 Aug 2008 21:27 artmerar(a)yahoo.com wrote: > On Aug 5, 6:28 pm, DA Morgan <damor...(a)psoug.org> wrote: >> artme...(a)yahoo.com wrote: >>> Hi, >>> We have a table like this: >>> CUSTOMER_ID >>> PRODUCT_ID >>> FORMAT >>> STATUS >>> DATE_ADDED >>> DATE_MODIFIED >>> We also have a history table like this: >>> CUSTOMER_ID >>> PRODUCT_ID >>> FORMAT >>> STATUS >>> SOURCE >>> DATE_ADDED >>> We want to put a trigger on the first table such that when a record is >>> inserted or updated it will make the entry to the history table. >>> However, the problem is the 'SOURCE' column on the history table. It >>> is not present or needed in the first table. >>> Entries into the first table are done through a PL/SQL program. So, >>> what we want is that when the entry is made into the first table, it >>> fires the trigger to make the second entry. But, how can this be done >>> if the columns do not match? >>> We do not want to code it in the PL/SQL program because then someone >>> can make an entry to the first table and we cannot record the history >>> on it, yet the SOURCE column only gets populated in the PL/SQL >>> program.... >>> And, to make things more complicated, depending on what is being >>> UPDATED, we may need the values of :NEW or :OLD........ >>> Is there a way to do this? >> In what version of Oracle? >> >> Assuming 9i or above you are reinventing the wheel: Use Fine Grained >> Auditing. >> -- >> Daniel A. Morgan >> Oracle Ace Director & Instructor >> University of Washington >> damor...(a)x.washington.edu (replace x with u to respond) >> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text - >> >> - Show quoted text - > > > We are running Oracle 10g R2. 10.2.0.1.0 to be exact.......does this > tell you anyhting?? > It does ... you are reinventing the wheel: http://www.psoug.org/reference/dbms_fga.html -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan(a)x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org
From: Malcolm Dew-Jones on 7 Aug 2008 00:45 DA Morgan (damorgan(a)psoug.org) wrote: : artmerar(a)yahoo.com wrote: : > On Aug 5, 6:28 pm, DA Morgan <damor...(a)psoug.org> wrote: : >> artme...(a)yahoo.com wrote: : >>> Hi, : >>> We have a table like this: : >>> CUSTOMER_ID : >>> PRODUCT_ID : >>> FORMAT : >>> STATUS : >>> DATE_ADDED : >>> DATE_MODIFIED : >>> We also have a history table like this: : >>> CUSTOMER_ID : >>> PRODUCT_ID : >>> FORMAT : >>> STATUS : >>> SOURCE : >>> DATE_ADDED : >>> We want to put a trigger on the first table such that when a record is : >>> inserted or updated it will make the entry to the history table. : >>> However, the problem is the 'SOURCE' column on the history table. It : >>> is not present or needed in the first table. : >>> Entries into the first table are done through a PL/SQL program. So, : >>> what we want is that when the entry is made into the first table, it : >>> fires the trigger to make the second entry. But, how can this be done : >>> if the columns do not match? : >>> We do not want to code it in the PL/SQL program because then someone : >>> can make an entry to the first table and we cannot record the history : >>> on it, yet the SOURCE column only gets populated in the PL/SQL : >>> program.... : >>> And, to make things more complicated, depending on what is being : >>> UPDATED, we may need the values of :NEW or :OLD........ : >>> Is there a way to do this? : >> In what version of Oracle? : >> : >> Assuming 9i or above you are reinventing the wheel: Use Fine Grained : >> Auditing. : >> -- : >> Daniel A. Morgan : >> Oracle Ace Director & Instructor : >> University of Washington : >> damor...(a)x.washington.edu (replace x with u to respond) : >> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text - : >> : >> - Show quoted text - : > : > : > We are running Oracle 10g R2. 10.2.0.1.0 to be exact.......does this : > tell you anyhting?? : > : It does ... you are reinventing the wheel: : http://www.psoug.org/reference/dbms_fga.html Perhaps I misunderstand it, but it seem to me that the FGA data you get doesn't seem to make it easy to answer questions such as "Did user X edit record Y?". "Which people have edited record X?". "What was the value of column Z in record X at 8:00 am on July 1st, what were the previous and next values, and who set them?" "What are all the edits that have been made to record X?" http://www.oracle.com/technology/pub/articles/nanda_fga_pt3.html mentions a couple of potential problems, - ... may lead to ... false positive entries in the audit trail - ... FGA records ... SQL statements ... but not the values before and after the change (which many of us might assume would be among the most important details you would want to know). The fga auditing seems most appropriate for "hacker" or "naughty programmer" security, along with numerous other log files, web logs, login logs, etc etc. $0.10
From: DA Morgan on 7 Aug 2008 14:33
Malcolm Dew-Jones wrote: > DA Morgan (damorgan(a)psoug.org) wrote: > : artmerar(a)yahoo.com wrote: > : > On Aug 5, 6:28 pm, DA Morgan <damor...(a)psoug.org> wrote: > : >> artme...(a)yahoo.com wrote: > : >>> Hi, > : >>> We have a table like this: > : >>> CUSTOMER_ID > : >>> PRODUCT_ID > : >>> FORMAT > : >>> STATUS > : >>> DATE_ADDED > : >>> DATE_MODIFIED > : >>> We also have a history table like this: > : >>> CUSTOMER_ID > : >>> PRODUCT_ID > : >>> FORMAT > : >>> STATUS > : >>> SOURCE > : >>> DATE_ADDED > : >>> We want to put a trigger on the first table such that when a record is > : >>> inserted or updated it will make the entry to the history table. > : >>> However, the problem is the 'SOURCE' column on the history table. It > : >>> is not present or needed in the first table. > : >>> Entries into the first table are done through a PL/SQL program. So, > : >>> what we want is that when the entry is made into the first table, it > : >>> fires the trigger to make the second entry. But, how can this be done > : >>> if the columns do not match? > : >>> We do not want to code it in the PL/SQL program because then someone > : >>> can make an entry to the first table and we cannot record the history > : >>> on it, yet the SOURCE column only gets populated in the PL/SQL > : >>> program.... > : >>> And, to make things more complicated, depending on what is being > : >>> UPDATED, we may need the values of :NEW or :OLD........ > : >>> Is there a way to do this? > : >> In what version of Oracle? > : >> > : >> Assuming 9i or above you are reinventing the wheel: Use Fine Grained > : >> Auditing. > : >> -- > : >> Daniel A. Morgan > : >> Oracle Ace Director & Instructor > : >> University of Washington > : >> damor...(a)x.washington.edu (replace x with u to respond) > : >> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text - > : >> > : >> - Show quoted text - > : > > : > > : > We are running Oracle 10g R2. 10.2.0.1.0 to be exact.......does this > : > tell you anyhting?? > : > > > : It does ... you are reinventing the wheel: > : http://www.psoug.org/reference/dbms_fga.html > > Perhaps I misunderstand it, but it seem to me that the FGA data you get > doesn't seem to make it easy to answer questions such as > > "Did user X edit record Y?". > "Which people have edited record X?". > "What was the value of column Z in record X at 8:00 am on July 1st, what > were the previous and next values, and who set them?" > "What are all the edits that have been made to record X?" > > http://www.oracle.com/technology/pub/articles/nanda_fga_pt3.html mentions > a couple of potential problems, > - ... may lead to ... false positive entries in the audit trail > - ... FGA records ... SQL statements ... but not the values before and > after the change (which many of us might assume would be among the most > important details you would want to know). > > The fga auditing seems most appropriate for "hacker" or "naughty > programmer" security, along with numerous other log files, web logs, login > logs, etc etc. > > $0.10 It does if you follow the examples available from Oracle and my website. -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan(a)x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org |