Auto-generating PK through a trigger

Hey guys, Can you please tell me whether I can auto-generate the PK through a trigger? The PK is based on two fields from the same record. For example: table : (field1_pk CHAR(6), field2 CHAR(2), field3 SERIAL) Now thru a trigger on INSERT FOR EACH ROW, we want to INSERT (field2,field3) and then the trigger will pick up these two values, concatenate them and insert the PK. I have already try it and I get: 391: Cannot insert a null into a column (mytab.field1_pk) When I specify a dummy value for my PK (not good!!) and expect it to be updated it I get: 747: Table or column matches object referenced in triggering statement. Then I changed the trigger to AFTER, and inserted a dummy PK, which I plan to update through my trigger (I think this may have a separate set of issues) but I get: 730: Cannot specify REFERENCING if trigger does not have FOR EACH ROW. Is there anyway I can do this?!?! Or am I wasting my time. thank you guys!! George

"George Karabotsos" <karabot@canada.com> wrote in message news:hbNza.9971$eo1.909787@news20.bellglobal.com... > Hey guys, > > Can you please tell me whether I can auto-generate the PK through a trigger? > The PK is based on two fields from the same record. > > For example: > > table : (field1_pk CHAR(6), field2 CHAR(2), field3 SERIAL) > > Now thru a trigger on INSERT FOR EACH ROW, we want to INSERT (field2,field3) > and then the trigger will pick up these two values, concatenate them and > insert the PK. > > I have already try it and I get: > 391: Cannot insert a null into a column (mytab.field1_pk)> > When I specify a dummy value for my PK (not good!!) and expect it to be > updated it I get: > 747: Table or column matches object referenced in triggering statement.> > > Then I changed the trigger to AFTER, and inserted a dummy PK, which I plan > to update through my trigger (I think this may have a separate set of > issues) but I get: > 730: Cannot specify REFERENCING if trigger does not have FOR EACH ROW.> > > Is there anyway I can do this?!?! Or am I wasting my time. > > thank you guys!! Of course there is a way, though I don't understand why u need field1_pk when the field3 is already a primary key (since it is a serial field). create trigger ins_table1insert on table1 referencing new as n for each row (execute procedure sp_ins_table(n.field2,n.field3) into field1_pk); create procedure sp_ins_table (p_field2 char(2), p_field3 integer) returning char(6) ; return p_field2 || p_field3 ; end procedure ; I wrote the above on the fly without checking for any syntax. Couple of assumptions:- 1. The database is a logged one. 2. You have to SET CONSTRAINS ALL DEFERRED. 3. You are using IDS 9.x. If you are using IDS 7.x, the trigger may work only after 7.3 version. That INTO in trigger wasn't supported in the older version. Also I am not sure 7.x supports p_field2 || p_field3 (because p_field3 is an integer and p_field2 is a char(2)). If it gives error, then change the stored procedure as follows:- define w char(4); let w = p_field3 ; return p_field2 || w ; Also u need to understand that the maximum value of p_field3 serial field can be 9999. After that the resultant string will be more than 6 char. I am sorry to say that the design seem to be very poor.