Help! Reentrant Triggers

Environment: IDS 7.24UC7 I'm trying to implement a temporary fix in the database till the application gets fixed. The problem is that if a user inserts a row in the table that already has a record which has the same primary key, I am to delete existing rows by means of a Trigger/Stored Procedure. Is this possible? If not, can you suggest any possible workarounds? Thanks in advance, Lyzander Here's my code: CREATE TRIGGER trg_ins_account INSERT ON account REFERENCING NEW AS new FOR EACH ROW ( EXECUTE PROCEDURE sp_ins_account(new.tel_num) ); CREATE PROCEDURE sp_ins_account(xtel_num LIKE account.tel_num)DEFINE sql_err, isam_err INT; DEFINE error_info CHAR(70); DEFINE vbill_tel_num LIKE account.bill_tel_num; DEFINE vbill_cust_cd LIKE account.bill_cust_cd; BEGIN ON EXCEPTION SET sql_err, isam_err, error_info RAISE EXCEPTION sql_err, isam_err, error_info; END EXCEPTION; FOREACH mycursor FOR SELECT bill_tel_num, bill_cust_cd INTO vbill_tel_num, vbill_cust_cd FROM account WHERE account.tel_num = xtel_num DELETE FROM account WHERE CURRENT OF mycursor; END FOREACH; END; END PROCEDURE; Sent via Deja.com http://www.deja.com/ Before you buy.

Lyzander Marantal wrote: > > Environment: IDS 7.24UC7 > > I'm trying to implement a temporary fix in the database till the > application gets fixed. > > The problem is that if a user inserts a row in the table that already > has a record which has the same primary key, I am to delete existing > rows by means of a Trigger/Stored Procedure. Is this possible? If not, > can you suggest any possible workarounds? > > Thanks in advance, > Lyzander > > Here's my code: > > CREATE TRIGGER trg_ins_account > INSERT ON account > REFERENCING NEW AS new> > FOR EACH ROW ( > EXECUTE PROCEDURE sp_ins_account(new.tel_num) > );> > CREATE PROCEDURE sp_ins_account(xtel_num LIKE account.tel_num)> DEFINE sql_err, isam_err INT; > DEFINE error_info CHAR(70); > DEFINE vbill_tel_num LIKE account.bill_tel_num; > DEFINE vbill_cust_cd LIKE account.bill_cust_cd; > > BEGIN > ON EXCEPTION SET sql_err, isam_err, error_info > RAISE EXCEPTION sql_err, isam_err, error_info; > END EXCEPTION; > > FOREACH mycursor FOR > SELECT bill_tel_num, bill_cust_cd > INTO vbill_tel_num, vbill_cust_cd > FROM account > WHERE account.tel_num = xtel_num> > DELETE FROM account WHERE CURRENT OF mycursor;> END FOREACH; > END; > END PROCEDURE; > > Sent via Deja.com http://www.deja.com/ > Before you buy. It is not possible solving Your problem that way, because You can not reference the triggered table itself. I think You have change Your sources. Wolfgang

By the way, the error I'm getting is: 747: Table or column matches object referenced in triggering statement. Looking up Error 747: This error is returned when a triggered SQL statement acts on the triggering table, or when both statements are updates, and the column that is updated in the triggered action is the same as the column that the triggering statement updates. I've also tried creating a copy of the orig. table and have the insert trigger on the orig. table insert a row to the copy table which then has a trigger that would delete the existing rows form the original table, but that didn't work either. I'm getting the same error. Somehow, I think the engine know that I would still be performing a delete in the original table! (though indirectly) Please, can anybody suggest a workaround? TIA Lyzander In article <7uno9q$fr4$1@nnrp1.deja.com>, Lyzander Marantal <zandy@my-deja.com> wrote: > Environment: IDS 7.24UC7 > > I'm trying to implement a temporary fix in the database till the > application gets fixed. > > The problem is that if a user inserts a row in the table that already > has a record which has the same primary key, I am to delete existing > rows by means of a Trigger/Stored Procedure. Is this possible? If not, > can you suggest any possible workarounds? > > Thanks in advance, > Lyzander > > Here's my code: > > CREATE TRIGGER trg_ins_account > INSERT ON account > REFERENCING NEW AS new> > FOR EACH ROW ( > EXECUTE PROCEDURE sp_ins_account(new.tel_num) > );> > CREATE PROCEDURE sp_ins_account(xtel_num LIKE account.tel_num)> DEFINE sql_err, isam_err INT; > DEFINE error_info CHAR(70); > DEFINE vbill_tel_num LIKE account.bill_tel_num; > DEFINE vbill_cust_cd LIKE account.bill_cust_cd; > > BEGIN > ON EXCEPTION SET sql_err, isam_err, error_info > RAISE EXCEPTION sql_err, isam_err, error_info; > END EXCEPTION; > > FOREACH mycursor FOR > SELECT bill_tel_num, bill_cust_cd > INTO vbill_tel_num, vbill_cust_cd > FROM account > WHERE account.tel_num = xtel_num> > DELETE FROM account WHERE CURRENT OF mycursor;> END FOREACH; > END; > END PROCEDURE; > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.

In article <7uno9q$fr4$1@nnrp1.deja.com>, Lyzander Marantal <zandy@my-deja.com> wrote: > Environment: IDS 7.24UC7 > > I'm trying to implement a temporary fix in the database till the > application gets fixed. > > The problem is that if a user inserts a row in the table that already > has a record which has the same primary key, I am to delete existing > rows by means of a Trigger/Stored Procedure. Is this possible? If not, > can you suggest any possible workarounds? > > Here's my code: > > CREATE TRIGGER trg_ins_account > INSERT ON account > REFERENCING NEW AS new> > FOR EACH ROW ( > EXECUTE PROCEDURE sp_ins_account(new.tel_num) > ); > CREATE PROCEDURE sp_ins_account(xtel_num LIKE account.tel_num)> DEFINE sql_err, isam_err INT; > DEFINE error_info CHAR(70); > DEFINE vbill_tel_num LIKE account.bill_tel_num; > DEFINE vbill_cust_cd LIKE account.bill_cust_cd; > > BEGIN > ON EXCEPTION SET sql_err, isam_err, error_info > RAISE EXCEPTION sql_err, isam_err, error_info; > END EXCEPTION; > > FOREACH mycursor FOR > SELECT bill_tel_num, bill_cust_cd > INTO vbill_tel_num, vbill_cust_cd > FROM account > WHERE account.tel_num = xtel_num> > DELETE FROM account WHERE CURRENT OF mycursor;> END FOREACH; > END; > END PROCEDURE; Lyzander, I have heard that some database products have an "Insert-or-Update" command to do what you want - Insert a new row or, if the primary key exists already, overwrite the current row. I do not know if there an a ANSI standard for this kind of database DML command and I would have reservations about the safety of using such a command. But that is clearly what you need in this case. But, IMHO, the very priciple of what you are trying to do is inherently flawed. Consider: in insert trigger can fire only AFTER the insert has completed. If your trigger were allowed to work and this is the first time the phone number (primary key) has been inserted, your procedure call would delete the row that your insert statement just inserted. (I have heard that in 7.3, the "re-entrant" trigger restriction was lifted but that would just have let you shoot yourself in the foot in this case.) It seems to me that the easiest solution would be to rewrite the stored procedure to accept a full row of data. ALL insert operations for this table would be handled by the procedure. The procedure could check for the existence of the primary key and choose its actions: - If the primary key does not exist yet, insert the new row. - If the primary key does exist, update all columns besides the PK column[s]. It occured to me that you might want use this algorithm in the procedure: - Perform the insert. - If this raises an exception related to duplicate key, have the exception handler perform the update. This is NOT a good idea. Recall that: (1) The statement is not over until the procedure has finished. (2) The default enforcement mode for constraints is "immediate", which means after the completion of the statement. This means that the exception will *NOT* be raised while the procedure is executing. If you follow my idea and some wise guy (of either gender ;-) uses a direct insert, no problem. If the key value exists already, the statement will be rejected and you can say "I told you so". +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ |------------------- Bulletin Board Announcement ----------------------| | Congregants will please note that the bowl at the back of the church | | bearing the sign "For the Sick" is for monetary contributions only. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Before you buy.

In article <7uq6p7$713$1@nnrp1.deja.com>, Jacob Salomon <jakesalomon@my-deja.com> wrote: > In article <7uno9q$fr4$1@nnrp1.deja.com>, > Lyzander Marantal <zandy@my-deja.com> wrote: > > Environment: IDS 7.24UC7 > > > > I'm trying to implement a temporary fix in the database till the > > application gets fixed. > > > > The problem is that if a user inserts a row in the table that already > > has a record which has the same primary key, I am to delete existing > > rows by means of a Trigger/Stored Procedure. Is this possible? If not, > > can you suggest any possible workarounds? > > > > Here's my code: > > > > CREATE TRIGGER trg_ins_account > > INSERT ON account > > REFERENCING NEW AS new> > > > FOR EACH ROW ( > > EXECUTE PROCEDURE sp_ins_account(new.tel_num) > > ); > > CREATE PROCEDURE sp_ins_account(xtel_num LIKE account.tel_num)> > DEFINE sql_err, isam_err INT; > > DEFINE error_info CHAR(70); > > DEFINE vbill_tel_num LIKE account.bill_tel_num; > > DEFINE vbill_cust_cd LIKE account.bill_cust_cd; > > > > BEGIN > > ON EXCEPTION SET sql_err, isam_err, error_info > > RAISE EXCEPTION sql_err, isam_err, error_info; > > END EXCEPTION; > > > > FOREACH mycursor FOR > > SELECT bill_tel_num, bill_cust_cd > > INTO vbill_tel_num, vbill_cust_cd > > FROM account > > WHERE account.tel_num = xtel_num> > > > DELETE FROM account WHERE CURRENT OF mycursor;> > END FOREACH; > > END; > > END PROCEDURE; > > Lyzander, > > I have heard that some database products have an "Insert-or-Update" > command to do what you want - Insert a new row or, if the primary key > exists already, overwrite the current row. I do not know if there an a > ANSI standard for this kind of database DML command and I would have > reservations about the safety of using such a command. But that is > clearly what you need in this case. > > But, IMHO, the very priciple of what you are trying to do is inherently > flawed. Consider: in insert trigger can fire only AFTER the insert has > completed. If your trigger were allowed to work and this is the first > time the phone number (primary key) has been inserted, your procedure > call would delete the row that your insert statement just inserted. (I > have heard that in 7.3, the "re-entrant" trigger restriction was lifted > but that would just have let you shoot yourself in the foot in this > case.) > > It seems to me that the easiest solution would be to rewrite the stored > procedure to accept a full row of data. ALL insert operations for this > table would be handled by the procedure. The procedure could check for > the existence of the primary key and choose its actions: > - If the primary key does not exist yet, insert the new row. > - If the primary key does exist, update all columns besides the PK > column[s]. > > It occured to me that you might want use this algorithm in the > procedure: > - Perform the insert. > - If this raises an exception related to duplicate key, have the > exception handler perform the update. > > This is NOT a good idea. Recall that: > (1) The statement is not over until the procedure has finished. > (2) The default enforcement mode for constraints is "immediate", > which means after the completion of the statement. > This means that the exception will *NOT* be raised while the procedure > is executing. > > If you follow my idea and some wise guy (of either gender ;-) uses a > direct insert, no problem. If the key value exists already, the > statement will be rejected and you can say "I told you so". > > +----- Jacob Salomon - DBA JSalomon@bn.com - ------------------------- -+ > |------------------- Bulletin Board Announcement --------------------- -| > | Congregants will please note that the bowl at the back of the church | > | bearing the sign "For the Sick" is for monetary contributions only. | > +--------------------------------------------------------------------- -+ > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Thanks for the reply Jacob. But the thing is, the table ONLY gets populated only through the application, which is handled by another group and which we have no control over. :-( Don't ask why. I'm thinking of running of an ESQL/C application in the stored procedure to delete the existing rows except the row that has just been inserted. But how will I know which one? The table doesn't have a serial key that I could look up to. I also couldn't get it its rowid. Any other suggestions? Thanks again, Lyzander Sent via Deja.com http://www.deja.com/ Before you buy.

In article <7uqb6t$aa2$1@nnrp1.deja.com>, Lyzander Marantal <zandy@my-deja.com> wrote: > In article <7uq6p7$713$1@nnrp1.deja.com>, > Jacob Salomon <jakesalomon@my-deja.com> wrote: > > In article <7uno9q$fr4$1@nnrp1.deja.com>, > > Lyzander Marantal <zandy@my-deja.com> wrote: > > > Environment: IDS 7.24UC7 > > > > > > I'm trying to implement a temporary fix in the database till the > > > application gets fixed. > > > > > > The problem is that if a user inserts a row in the table that > already > > > has a record which has the same primary key, I am to delete existing > > > rows by means of a Trigger/Stored Procedure. Is this possible? If > not, > > > can you suggest any possible workarounds? I have suggestion that could work. Try to create table account2 with additional timestamp column and primary index on tel_num and timestamp. create table account2 ( tel_num char(10), bill_tel_num char(10), bill_cust_cd char(10), stamp datetime year to second default current year to second, primary key (tel_num, stamp) ); Then create view account like create view account ( tel_num, bill_tel_num, bill_cust_cd ) as select tel_num, bill_tel_num, bill_cust_cd from account2 a where stamp = ( select max(stamp) from account2 b where b.tel_num = a.tel_num ); Or try to think in this direction. Regards Vardan > > > > > > Here's my code: > > > > > > CREATE TRIGGER trg_ins_account > > > INSERT ON account > > > REFERENCING NEW AS new> > > > > > FOR EACH ROW ( > > > EXECUTE PROCEDURE sp_ins_account(new.tel_num) > > > ); > > > CREATE PROCEDURE sp_ins_account(xtel_num LIKE account.tel_num)> > > DEFINE sql_err, isam_err INT; > > > DEFINE error_info CHAR(70); > > > DEFINE vbill_tel_num LIKE account.bill_tel_num; > > > DEFINE vbill_cust_cd LIKE account.bill_cust_cd; > > > > > > BEGIN > > > ON EXCEPTION SET sql_err, isam_err, error_info > > > RAISE EXCEPTION sql_err, isam_err, error_info; > > > END EXCEPTION; > > > > > > FOREACH mycursor FOR > > > SELECT bill_tel_num, bill_cust_cd > > > INTO vbill_tel_num, vbill_cust_cd > > > FROM account > > > WHERE account.tel_num = xtel_num> > > > > > DELETE FROM account WHERE CURRENT OF mycursor;> > > END FOREACH; > > > END; > > > END PROCEDURE; > > > > Lyzander, > > > > I have heard that some database products have an "Insert-or-Update" > > command to do what you want - Insert a new row or, if the primary key > > exists already, overwrite the current row. I do not know if there an > a > > ANSI standard for this kind of database DML command and I would have > > reservations about the safety of using such a command. But that is > > clearly what you need in this case. > > > > But, IMHO, the very priciple of what you are trying to do is > inherently > > flawed. Consider: in insert trigger can fire only AFTER the insert has > > completed. If your trigger were allowed to work and this is the first > > time the phone number (primary key) has been inserted, your procedure > > call would delete the row that your insert statement just inserted. (I > > have heard that in 7.3, the "re-entrant" trigger restriction was > lifted > > but that would just have let you shoot yourself in the foot in this > > case.) > > > > It seems to me that the easiest solution would be to rewrite the > stored > > procedure to accept a full row of data. ALL insert operations for this > > table would be handled by the procedure. The procedure could check for > > the existence of the primary key and choose its actions: > > - If the primary key does not exist yet, insert the new row. > > - If the primary key does exist, update all columns besides the PK > > column[s]. > > > > It occured to me that you might want use this algorithm in the > > procedure: > > - Perform the insert. > > - If this raises an exception related to duplicate key, have the > > exception handler perform the update. > > > > This is NOT a good idea. Recall that: > > (1) The statement is not over until the procedure has finished. > > (2) The default enforcement mode for constraints is "immediate", > > which means after the completion of the statement. > > This means that the exception will *NOT* be raised while the procedure > > is executing. > > > > If you follow my idea and some wise guy (of either gender ;-) uses a > > direct insert, no problem. If the key value exists already, the > > statement will be rejected and you can say "I told you so". > > > > +----- Jacob Salomon - DBA JSalomon@bn.com - ------------------------- > -+ > > |------------------- Bulletin Board Announcement --------------------- > -| > > | Congregants will please note that the bowl at the back of the > church | > > | bearing the sign "For the Sick" is for monetary contributions > only. | > > +--------------------------------------------------------------------- > -+ > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > > > > Thanks for the reply Jacob. But the thing is, the table ONLY gets > populated only through the application, which is handled by another > group and which we have no control over. :-( Don't ask why. > > I'm thinking of running of an ESQL/C application in the stored > procedure to delete the existing rows except the row that has just been > inserted. But how will I know which one? The table doesn't have a > serial key that I could look up to. I also couldn't get it its rowid. > > Any other suggestions? > > Thanks again, > Lyzander > > Sent via Deja.com http://www.deja.com/ > Before you buy. > -- Vardan Aroustamian vaar@geocities.com Sent via Deja.com http://www.deja.com/ Before you buy.