| updating records values and post trigger causing issues [message #590163] |
Mon, 15 July 2013 08:26  |
cornwall
Messages: 36
Registered: June 2009
|
Member |
|
|
I have a multi record block based on a view. All records in the view are displayed in the block by use of Post-Query trigger when entering the form.
The block has 5 items as follows:
1) RECORD_STATUS = a non-base table column which is a checkbox.
2) ITEM_TYPE = a text-item which has an LOV attached.
3) ITEM_TEXT = a text-item which is free format text.
4) LAST_UPDATE_DATE a date column
5) STATUS = a text item either 'Open' or 'Closed'
The LOV is based on a table of Item Types with values say, 'Type1', upto 'Type9'.
I have a Wnen-New-Record-Instance trigger which 'Posts' changes to the database. This has been included as i want to limit the values of the ITEM_TYPE column to values which have not been previously used.
Consider this scenario...
The block has 3 records.
record 1 has 'Closed' status so no updates are allowed.
record 2 has 'Open' status so updating of Item_Text is allowed.
record 3 has 'Open' status so updating of Item_Text is allowed.
I check the RECORD_STATUS checkbox on record2.
(This sets the RECORD_STATUS checkbox to a checked value and changes the STATUS column to 'Closed' by When-Checkbox-Changed trigger.) At this point the record has not been saved so if you uncheck the checkbox , then the STATUS column will go back to 'Open'. However at this point i will leave it as Checked (Closed).
I then insert a new record, only values Item4 to Item 9 are correctly shown in the LOV. I select Item4.
I then go back to the previous record and uncheck the Checkbox to say that i wish to leave it 'Open' after all (in effect no changes have occurred), then the STATUS column correctly reverts back to 'Open' by my WCC trigger. If i then SAVE the changes, the new record has been inserted on the database correctly, however the LAST_UPDATED_DATE from the record which was checked and then unchecked has also been updated incorrectly even though no net changes have actually occurred.
(because i am using WNRI trigger to limit the List of Values on the LOV column, this has incorrectly set the previous records LAST_UPDATED_DATE column to be Sysdate.)
How can i stop this from happening?
|
|
|
|
| Re: updating records values and post trigger causing issues [message #590195 is a reply to message #590163] |
Mon, 15 July 2013 14:56  |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
One option is to create a hidden ORIGINAL_LAST_UPDATED_DATE and populate its value at fetch time so that it has the same value as LAST_UPDATED_DATE. In PRE-INSERT and PRE-UPDATE check checkbox state and set LAST_UPDATED_DATE to its original value (if checkbox is unchecked) or leave it "as is" (i.e. SYSDATE) (if checkbox is checked).
Another option is to create a database trigger which would utilize a WHEN clause and set LAST_UPDATED_DATE if necessary. Here's an example: I'll create a simple table and insert some values in there. Checkbox values are 0 or 1.SQL> create table test
2 (id number(1),
3 cbox number(1),
4 last_updated_date date
5 );
Table created.
SQL> insert into test values (1, 1, date '2013-07-10');
1 row created.
SQL> insert into test values (2, 0, date '2013-07-15');
1 row created.
SQL> create or replace trigger trg_bu_test
2 before update on test
3 for each row
4 when (new.cbox <> old.cbox)
5 begin
6 :new.last_updated_date := sysdate;
7 end;
8 /
Trigger created. SQL> select * from test;
ID CBOX LAST_UPDATED_DATE
---------- ---------- -------------------
1 1 10.07.2013 00:00:00
2 0 15.07.2013 00:00:00
SQL> update test set cbox = 1 where id = 1;
1 row updated.
SQL> update test set cbox = 1 where id = 2;
1 row updated.
SQL> select * from test;
ID CBOX LAST_UPDATED_DATE
---------- ---------- -------------------
1 1 10.07.2013 00:00:00
2 1 15.07.2013 21:53:40
SQL> |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
