Home » Developer & Programmer » Forms » How to drop and create constraints with forms_ddl (Forms 6i)
How to drop and create constraints with forms_ddl [message #565932] Sat, 08 September 2012 12:42 Go to next message
qanita786
Messages: 153
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 Go to previous message
cookiemonster
Messages: 11069
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.
Previous Topic: No_validation
Next Topic: forms lov change
Goto Forum:
  


Current Time: Tue Oct 21 06:27:51 CDT 2014

Total time taken to generate the page: 0.09654 seconds