IDS Forum

RE: on re-build trigger causes errors

Posted By: John Adamski
Date: Tuesday, 7 September 2010, at 2:24 p.m.

In Response To: Re: on re-build trigger causes errors (Art Kagel)

Art,

If this was an ideal world I think I could get by with a serial, but that won't work. I'm trying to get the developers to explain what contraption they created as there is more to this story. I just don't know what yet.

As it looks like 11.50 no longer allow this circular triggering, it might give me enough leverage to get the developers to talk to me.

I love my job I love my Job I nnnnnneeeeeeed my pay check.

John

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel
Sent: Tuesday, September 07, 2010 9:23 AM
To: ids@iiug.org
Subject: Re: on re-build trigger causes errors [21187]

You do know that you could just alter the id column to be a SERIAL or BIGSERIAL and it would automatically insert the next number without any triggers? If somewhere the software required the get_next_index_id column in get_next_id_table updated, you could just put a trigger in to do that without the circular relationship and everything would probably keep working.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.

On Mon, Sep 6, 2010 at 2:31 PM, John Adamski <adamski@graceland.edu> wrote:

> I was using a tool called senter2 (ERP provider tool?? - hack on
> dbaccess/isql
> I think)
>
> Well I just tried a simple insert of the primary key and fullname in
> dbaccess,
> letting all other fields to be null and get the error, with a number now -
> woohoo.
>
> DBACCESS
> SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
> Modify the current SQL statements using the SQL editor.
>
> ----------------------- cars@carsitcp ---------- Press CTRL-W for Help
> --------
>
> insert into id_rec (id, fullname) values (0, "jda, test,,Dr.")
>
> 747: Table or column matches object referenced in triggering statement.
>
> Now if this had worked the id of 0 would have been updated to the next
> available id# (gc_next_index_id + 1), but the zero id is still there.
>
> select * from id_rec where id=0
> id 0
> prsp_no 0
> fullname jda, test,,Dr.
> name_sndx
> lastname
> firstname
> middlename
> suffixname
> addr_line1
> addr_line2
>
> John
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Fernando
> Nunes
> Sent: Monday, September 06, 2010 12:31 PM
> To: ids@iiug.org
> Subject: Re: on re-build trigger causes errors [21180]
>
> On Mon, Sep 6, 2010 at 5:08 PM, John Adamski <adamski@graceland.edu>
> wrote:
>
> > HPUX B.11.31, Itanium
> > IDS 11.50.FC6
> >
> > I have a situation and am not sure the best course to resolve.
> >
> > <<Declaimer: I take no responsibility for what the developers created
> > here>>
> >
> > We have a table that is the primary user information table (id_rec)
> > which currently has an insert trigger that updates the next available
> > ID# on another table (gc_next_id_table). -also has an audit trigger--
> > gc_next_id_table has a update trigger that then updates the ID# on the
> > original table (id_rec).
> > This
> > has been working since 1999 when put in place, even through upgrades.
> > It is even working now after upgrading to 11.50 earlier this year.
> >
> > The situation is I now need to update the table per our ERP provider
> > as they are making change to id_rec and adding their own trigger. I
> > loaded their changes on our test server and rebuilt the table. When I
> > try to add a new record in got this error along with the ID# not being
> > updated -is zero:
> >
> > Table or column matches object referenced in triggering statement.
> >
> > Not knowing what exactly was causing the error I tried one trigger at
> > a time until I verified that the insert trigger that updates
> > gc_next_id_table is the problem.
> >
> > I do not fully understand the cause of this error, but assume has to
> > do with a trigger triggering a trigger that updates the original
> > record, and 11.50 being more selective on allowing this. Whatever is
> > the cause I need some way to resolve this.
> >
> > What are my options?
> >
> > here is an abridged version of id_rec schema and the full schema of
> > gc_next_id_rec.
> >
> > create table id_rec
> > (
> >
> > id integer
> >
> > default 0 not null ,
> >
> > prsp_no integer
> >
> > default 0 not null ,
> >
> > fullname char(32)
> >
> > default '' not null ,
> >
> > name_sndx char(4)
> >
> > default '' not null ,
> >
> > zip char(10)
> >
> > default '' not null ,
> >
> > ss_no char(11)
> >
> > default '' not null ,
> >
> > phone char(12),
> > .... A BUNCH OF OTHER FILEDS .....
> >
> > cass_cert_date date,
> >
> > primary key (id) constraint id_id
> > ) extent size 245954 next size 49190 lock mode row;
> >
> > create index id_fullname on id_rec (fullname)
> >
> > using btree in dbs0;
> > create index id_key1 on id_rec (name_sndx,
> >
> > fullname) using btree in dbs0;
> > create index id_phone on id_rec (phone)
> >
> > using btree in dbs0;
> > create index id_prsp_no on id_rec (prsp_no)
> >
> > using btree in dbs0;
> > create index id_ss_no on id_rec (ss_no)
> >
> > using btree in dbs0;
> > create index id_zip on id_rec (zip) using
> >
> > btree in dbs0;
> >
> > create trigger id_rei insert on id_rec referencing
> >
> > new as n
> >
> > for each row
> >
> > when ((n.fullname != 'dbman' ) )
> >
> > (
> >
> > update gc_next_id_table set gc_next_id_table.gc_next_index_id
> >
> > = (gc_next_index_id + 1 ) );
> >
> > create table gc_next_id_table
> > (
> >
> > gc_next_index_id integer
> >
> > default 70000000 not null
> > ) extent size 16 next size 16 lock mode row;
> >
> > create index gc_next_id_index on gc_next_id_table
> >
> > (gc_next_index_id) using btree in dbs0;
> >
> > create trigger gc_next_id_tablu update on "informix"
> >
> > ..gc_next_id_table referencing old as o new as n
> >
> > for each row
> >
> > (
> >
> > update id_rec set id_rec.id = (select
> >
> > x0.id from gc_new_id_table x0 where (x0.gc_index_id =
> > o.gc_next_index_id
> >
> > ) ) where ((id = 0 ) OR (id IS NULL ) ) );
> >
> > John Adamski
> > Sr. Network Specialist
> > Graceland University
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> Could you provide a working SQL that reproduces the problem?
> Regards.
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --0015175cb2621e3032048f9aa541
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--0016e644ccf82ddb75048fac2302

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• on re-build trigger causes errors
  John Adamski -- Monday, 6 September 2010, at 12:08 p.m.
  • Re: on re-build trigger causes errors
    Fernando Nunes -- Monday, 6 September 2010, at 1:31 p.m.
    • RE: on re-build trigger causes errors
      John Adamski -- Monday, 6 September 2010, at 2:31 p.m.
      • Re: on re-build trigger causes errors
        Art Kagel -- Tuesday, 7 September 2010, at 10:23 a.m.
        • RE: on re-build trigger causes errors
          John Adamski -- Tuesday, 7 September 2010, at 2:24 p.m.
          • RE: on re-build trigger causes errors
            Richard Snoke -- Tuesday, 7 September 2010, at 2:50 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.