I have a form which has three parts, a key block and two data blocks. The first data block is connected to a table called majors. The second data block is connected to a table called course. The relationship between majors and courses is one to many. Currently how it works is that I enter a set of values in the key block and when I page down, it retrieves a single record from the majors table which matches the criteria I entered in the keyblock section. When I page down again to the courses data block, it retrieves a set of records (10 to a block) from the courses table and displays it in the bottom section of the form. It does this through the WHERE clause in the properties section of each of these blocks. Currently the form can insert a brand new record or records and edit existing records without any problem at both the majors and courses data blocks. When I enter a year code in the key block, what happens is that it fetches the maximum year less than or equal to the entered year. For instance, if I enter 2009 year in the key block and the record in the majors block only has a maximum year of 2008 with all the other criteria entered being equal (or matching), then it will display the particular 2008 majors record.
The problem I am facing is this:
I have a maintenance button in the majors data block. When I click on the maintenance button, what I would like it to do is to make a copy of the current record in the majors block and duplicate it. Then change the year in the majors record to the same year as the one entered in the key block. At the same time, it will also execute a user defined trigger, COPY-FORWARD, to query the records from the courses table. Then, when I page down to the courses block, it will display the matching courses records. It seems to work the first time, but after that starts to do weird things, such as the following error (see attached). Inside the COPY-FORWARD trigger are two statements "Insert Record" and "Duplicate Record". In the ON-INSERT trigger, is the code to insert a new record in the courses table and query the records from the courses table so that when I page down to the courses data block they will show up. I also have a queries in the WHERE clauses of the properties section of the majors and courses data blocks (see code section below). Maybe the COPY-FORWARD and ON-INSERT triggers are clashing with the WHERE clause of the data block properties or perhaps I am not using the correct triggers for my purpose. I am not well experienced with forms and would appreciate someone's help!
One of the experts had suggested that I shouldn't be using the ON-INSERT trigger for a related problem, so maybe I am not using the correct triggers.
Inside COPY-FORWARD trigger
create_record;
duplicate_record;
:majors_acyr_code := :key_block.acyr_code;
Inside ON-INSERT trigger
:majors_code := :key_block.sbgi_code;
:majors_degt_trns := :key_block.degt_trns;
:majors_degc_inst := :key_block.degc_inst;
:majors_majr_code := :key_block.majr_code;
:majors_conc_code := :key_block.conc_code;
insert_record;
insert into courses(
COURSES_CODE,
COURSES_DEGT_TRNS,
COURSES_DEGC_INST,
COURSES_MAJR_CODE,
COURSES_CONC_CODE,
COURSES_SEQNO,
COURSES_SUBJ_CODE,
COURSES_CRSE_NUMB,
COURSES_CREDITS,
COURSES_ACYR_CODE,
COURSES_CSTA_CODE,
COURSES_COUNTER,
COURSES_ACTIVITY_DATE
)
select COURSES_CODE,
COURSES_DEGT_TRNS,
COURSES_DEGC_INST,
COURSES_MAJR_CODE,
COURSES_CONC_CODE,
COURSES_SEQNO,
COURSES_SUBJ_CODE,
COURSES_CRSE_NUMB,
COURSES_CREDITS,
:MAJORS_ACYR_CODE,
COURSES_CSTA_CODE,
COURSES_COUNTER,
SYSDATE
from COURSES
where courses_acyr_code = (
select max(courses_acyr_code)
from courses
where courses_acyr_code < :majors_acyr_code
and courses_code = :majors_code
and courses_degt_trns = :majors_degt_trns
and courses_degc_inst = :majors_degc_inst
and courses_majr_code = :majors_majr_code
and courses_conc_code = :majors_conc_code
)
and courses_code = :majors_code
and courses_degt_trns = :majors_degt_trns
and courses_degc_inst = :majors_degc_inst
and courses_majr_code = :majors_majr_code
and courses_conc_code = :majors_conc_code;
WHERE courses_code = :majors_code
AND courses_degt_trns = :majors_degt_trns
AND courses_degc_inst = :majors_degc_inst
AND courses_majr_code = :majors_majr_code
AND courses_conc_code = :majors_conc_code
AND courses_acyr_code = :majors_acyr_code
ORDER BY courses_seqno, courses_subj_code, courses_crse_numb
The WHERE clause in the properites data block of the majors data block is as follows:
WHERE majors_code = :key_block.code
AND majors_degt_trns = :key_block.degt_trns
AND majors_degc_inst = :key_block.degc_inst
AND majors_majr_code = :key_block.majr_code
AND ((majors_conc_code = :key_block.conc_code) OR (:key_block.conc_code IS NULL))
AND majors_acyr_code = (SELECT MAX (B.majors_acyr_code)
FROM majors B
WHERE B.majors_acyr_code <= :key_block.acyr_code
AND B.majors_code = :key_block._code
AND B.majors_majr_code = :key_block.majr_code
AND B.majors_degt_trns = :key_block.degt_trns
AND B.majors_degc_inst = :key_block.degc_inst
AND ((B.majors_conc_code = :key_block.conc_code) OR (:key_block.conc_code IS NULL)))
The WHERE clause in the courses properties data block is as follows:
WHERE courses_code = :majors_code
AND courses_degt_trns = :majors_degt_trns
AND courses_degc_inst = :majors_degc_inst
AND courses_majr_code = :majors_majr_code
AND courses_conc_code = :majors_conc_code
AND courses_acyr_code = :majors_acyr_code
ORDER BY courses_seqno, courses_subj_code, courses_crse_numb
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
Select allOpen in new window
by: HenkaPosted on 2009-10-11 at 22:45:25ID: 25548788
I think that it is true that you would't use ON-INSERT trigger for it - see this description of the trigger:
Fires during the Post and Commit Transactions process when a record is inserted. Specifically, it fires after the Pre-Insert trigger fires and before the Post-Insert trigger fires, when Oracle Forms would normally insert a record in the database. It fires once for each row that is marked for insertion into the database.
You have no COMMIT (or commit_form) in the code you have posted.
Maybe you can try to move your code from the ON-INSERT trigger to a Post-Text-Item trigger of an item where you change the year.