trigger ? Forms6i

sun77
sun77 used Ask the Experts™
on
Hello, I am using Forms 6i.  

What type of trigger and what syntax  should I write if I need to automatically update foreign key field (loc_no for example)  in the TEST table (for example) when the use make changes to the primary key (loc_no) in the Locations table

Thanks a lot!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Define a relation between them (based on the FK between them). Forms generates code to manage it.

If there is no FK try the synchronize item property
write an PRE-UPDATE trigger, write something like(havn't tested it it!):


DECLARE
  l_oldloc  number;

BEGIN
 
  l_oldloc := Get_Item_Property('myblock.loc_no',DATABASE_VALUE);

  if l_oldloc <> :myblock.loc_no then
    update test
      set loc_no = :myblock.loc_no
     where loc_no =  l_oldloc;
  end if;
 
END;



Cheers, Stefan

Commented:
@stemu2000

This is only necessay if Forms runs in Primary Key Mode. The normal case is the use of rowids so that even updates on PK-Column work as intended.

Relations between blocks are intended to take care of FK-relations. So it is better to use then, unless you hate Forms and want to make the worst of it.

Anyway, if updates of the PK happen (I write in any project design guidline: "Updates on PK must not happen at any time") the FKs referencing it should be declared DEFERRABLE and also be set to DEFERRED. Otherwise your update get to the hell of statements:
insert new PK
update old FKs
delete old PK
(or you disable the constraint - no kidding that the solution an oracle representative gave to me...)
csar,

updating your pk in your forms mastertable doesn't automatically update the childsreferences (even if you have a relation defined!). you have to do this manually during the update.

Also not all databases have referential FK's implemented ;-)! i don't really want to argue if this is good database design or not - but it certainly makes life much easier!

Cheers,
Stefan
You need to use both the Pre-Update and Post-Update Triggers.

In the Pre-Update Trigger store the previous value of the location (primary key) at the Form Level, so that it is easily accessible.
And in the Post-Update Trigger write and Update Statement to the respective table where the loc_no is used as a foreign key and set the loc_no value with the new value which can be acquired easily and check for the earlier value in the where clause.
For Eg : Update <table_name>
         Set loc_no = <new value>
         WHERE loc_no = <old value - stored in the pre-update trigger>;

And use commit after this statement.

Hope this helps...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial