on re-build trigger causes errors
On HP-UX/IDS 11.50.FC6, a legacy ID-assignment scheme used circular triggers: an insert trigger on id_rec updated gc_next_id_table, whose update trigger then updated id_rec back. After rebuilding id_rec (for ERP vendor changes), inserts failed with error 747 "Table or column matches object referenced in triggering statement," leaving the id as 0. The poster isolated the insert trigger as the trigger-cascade culprit. Replies suggested dropping the circular design in favour of a SERIAL/BIGSERIAL column (Art Kagel) or a sequence (Richard Snoke), but the poster said a serial wouldn't suit and he needed the developers to explain the design. No resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
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
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=0id 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.
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
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 th
Hi,
If you can get the developer's attention, also take a look at sequences.
They offer a lot more control than serials.
Cheers,
Dick Snoke
IBM ChannelWorks
dsnoke@us.ibm.com
(404) 487-1595
From: "John Adamski" <adamski@graceland.edu>
To: ids@iiug.org
Date: 09/07/10 02:25 PM
Subject: RE: on re-build trigger causes errors [21201]
Sent by: ids-bounces@iiug.org
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