| How to drop and create constraints with forms_ddl [message #565932] |
Sat, 08 September 2012 12:42  |
qanita786
Messages: 229
Registered: May 2007
Location: PAKISTAN
|
Senior Member |
|
|
forms_ddl('ALTER TABLE ACCOUNT.VOC_DETAIL DROP CONSTRAINT FK_VOC_NO');
forms_ddl('ALTER TABLE ACCOUNT.VOC_MASTER DROP CONSTRAINT PK_VOC_MASTER');
forms_ddl('ALTER TABLE ACCOUNT.VOC_MASTER ADD CONSTRAINTS PK_VOC_MASTER primary key(VOC_NO)');
forms_ddl('ALTER TABLE ACCOUNT.VOC_DETAIL ADD CONSTRAINTS FK_VOC_NO FOREIGN KEY(VOC_NO) REFERENCES VOC_MASTER(VOC_NO)');
the trigger works very well but when i add some trigger between drop and create constraints then first step drop constraint sucessfully but 2nd trigger when create new constraints not working examples of trigger are as under:-
declare
s_number varchar2(7);
cursor masters is
select voc_type,voc_date,voc_no from account.voc_master order by voc_type,voc_date,voc_no;
abc masters%rowtype;
cursor detail is
select voc_no,ref_no from account.voc_detail group by voc_no,ref_no;
xyz detail%rowtype;
begin
set_application_property(cursor_style,'busy');
open masters;
loop
fetch masters into abc;
exit when masters%notfound;
select count(*) into :global.cnt from account.voc_detail where voc_no=abc.voc_no;
if :global.cnt=0 then
delete from account.voc_master where voc_no=abc.voc_no;
commit;
end if;
end loop;
close masters;
update account.numb set voc_no='00000' where voc_type='CR' or voc_type='BR' or voc_type='CP' or voc_type='BP' or voc_type='PC' ;
update account.voc_detail set ref_no=voc_no;
update account.voc_detail set voc_no='';
commit;
forms_ddl('ALTER TABLE ACCOUNT.VOC_DETAIL DROP CONSTRAINT FK_VOC_NO');
forms_ddl('ALTER TABLE ACCOUNT.VOC_MASTER DROP CONSTRAINT PK_VOC_MASTER');
------------------------
open masters;
loop
fetch masters into abc;
exit when masters%notfound;
select abc.voc_type||lpad(nvl(voc_no,0)+1,5,'0') into s_number from account.numb where voc_type=abc.voc_type;
update account.voc_detail set voc_no = s_number where ref_no=abc.voc_no;
update account.numb set voc_no = substr(s_number,3,7) where voc_type = abc.voc_type;
commit;
end loop;
close masters;
-----------------------
open detail;
loop
fetch detail into xyz;
exit when detail%notfound;
update account.voc_master set voc_no = xyz.voc_no where voc_no = xyz.ref_no;
commit;
end loop;
close detail;
forms_ddl('ALTER TABLE ACCOUNT.VOC_MASTER ADD CONSTRAINTS PK_VOC_MASTER primary key(VOC_NO)');
forms_ddl('ALTER TABLE ACCOUNT.VOC_DETAIL ADD CONSTRAINTS FK_VOC_NO FOREIGN KEY(VOC_NO) REFERENCES VOC_MASTER(VOC_NO)');
set_application_property(cursor_style,'default');
next_field;
set_item_property('d_voucher',enabled,property_off);
end;
|
|
|
|
| Re: How to drop and create constraints with forms_ddl [message #565933 is a reply to message #565932] |
Sat, 08 September 2012 14:13  |
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Stop doing that. If you're having to drop constraints to allow forms to alter data then your system design is completely flawed.
Probably you can't recreate the constraints because you've added data that violates them.
Having an application do this is always wrong.
So change it so you can alter the data without dropping the constraints.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
