Error 747 on create trigger

IDS 10.FC6 I'm trying to create a trigger on a table. The table has 2 columns (id integer, name char(3)); whenever a user inserts a name with blanks (' ') I want to update the table and replace the blanks with the null value. Whenever I run the following insert command: insert into test values(1,' '), I get the 747 error: Table or column matches object referenced in triggering statement. Any idea or workaround? Below the trigger and the stored procedure create trigger test_trig insert on testreferencing new as post for each row (execute procedure upd_test(post.id,post.name)) create procedure upd_test (l_id integer,l_name char(5))if (l_name==' ') then update test set name=null where id=l_id;end if; end procedure;

GEORGES MARTIN wrote: > IDS 10.FC6 > > I'm trying to create a trigger on a table. > The table has 2 columns (id integer, name char(3)); > whenever a user inserts a name with blanks (' ') I want to update the table > and replace the blanks with the null value. > Whenever I run the following insert command: insert into test values(1,' '), I > get the 747 error: Table or column matches object referenced in triggering > statement. > Any idea or workaround? > > Below the trigger and the stored procedure > > create trigger test_trig insert on test> referencing new as post > for each row (execute procedure upd_test(post.id,post.name)) > > create procedure upd_test (l_id integer,l_name char(5))> if (l_name==' ') then > update test set name=null where id=l_id;> end if; > end procedure; > What logging mode is your database? -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

From the Guide to SQL Syntax (v10.00 p 2-239): - If the trigger has an INSERT event, the trigger action can be an UPDATE statement that references a column in the triggering table, but this column cannot be a column for which a value was supplied by the trigger event. If the trigger has an INSERT event, and the trigger action updates the triggering table, the columns in both statements must be mutually exclusive. You are trying to update a column whose value was supplied by the triggering INSERT statements. A no-no. Art S. Kagel ----- Original Message ----- From: Georges Martin <ids@iiug.org> To: ids@iiug.org At: 10/09 12:27:35 IDS 10.FC6 I'm trying to create a trigger on a table. The table has 2 columns (id integer, name char(3)); whenever a user inserts a name with blanks (' ') I want to update the table and replace the blanks with the null value. Whenever I run the following insert command: insert into test values(1,' '), I get the 747 error: Table or column matches object referenced in triggering statement. Any idea or workaround? Below the trigger and the stored procedure create trigger test_trig insert on testreferencing new as post for each row (execute procedure upd_test(post.id,post.name)) create procedure upd_test (l_id integer,l_name char(5))if (l_name==' ') then update test set name=null where id=l_id;end if; end procedure; ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Thanks Art for your answer Any workaround for this issue? > To: ids@iiug.org> From: kagel@bloomberg.net> Subject: Re:Error 747 on create trigger [10083]> Date: Tue, 9 Oct 2007 12:42:00 -0400> > >From the Guide to SQL Syntax (v10.00 p 2-239): > > - If the trigger has an INSERT event, the trigger action can be an UPDATE > statement that references a column in the triggering table, but this column > cannot be a column for which a value was supplied by the trigger event. If the > trigger has an INSERT event, and the trigger action updates the triggering > table, the columns in both statements must be mutually exclusive. > > You are trying to update a column whose value was supplied by the triggering > INSERT statements. A no-no. > > Art S. Kagel > > ----- Original Message ----- > From: Georges Martin <ids@iiug.org> > To: ids@iiug.org > At: 10/09 12:27:35 > > IDS 10.FC6 > > I'm trying to create a trigger on a table. > The table has 2 columns (id integer, name char(3)); > whenever a user inserts a name with blanks (' ') I want to update the table > and replace the blanks with the null value. > Whenever I run the following insert command: insert into test values(1,' '), I > get the 747 error: Table or column matches object referenced in triggering > statement. > Any idea or workaround? > > Below the trigger and the stored procedure > > create trigger test_trig insert on test > referencing new as post > for each row (execute procedure upd_test(post.id,post.name)) > > create procedure upd_test (l_id integer,l_name char(5)) > if (l_name==' ') then > update test set name=null where id=l_id; > end if; > end procedure; > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Are you ready for Windows Live Messenger Beta 8.5 ? Get the latest for free today! http://entertainment.sympatico.msn.ca/WindowsLiveMessenger

Oops, that got away from me. Note that you can do what you like this: create function fix_blank( l_name char(5) ) returning char; if (l_name = ' ') then return char::NULL; else return l_name; end function; create trigger test_trig insert on testreferencing new as post for each row (execute function fix_blank(post.id,post.name) INTO name); That actually modifies the value of name with the return value of the function fix_blank() BEFORE performing the insert. Alternatively IB you can use: WHEN (name = ' ') (execute function fix_blank...) so that the function is only called when needed to reduce the overhead of the trigger. Art S. Kagel ----- Original Message ----- From: Georges Martin <ids@iiug.org> To: ids@iiug.org At: 10/09 12:27:35 IDS 10.FC6 I'm trying to create a trigger on a table. The table has 2 columns (id integer, name char(3)); whenever a user inserts a name with blanks (' ') I want to update the table and replace the blanks with the null value. Whenever I run the following insert command: insert into test values(1,' '), I get the 747 error: Table or column matches object referenced in triggering statement. Any idea or workaround? Below the trigger and the stored procedure create trigger test_trig insert on testreferencing new as post for each row (execute procedure upd_test(post.id,post.name)) create procedure upd_test (l_id integer,l_name char(5))if (l_name==' ') then update test set name=null where id=l_id;end if; end procedure; ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

It worked Art thank you very much for your help I appreciate > To: ids@iiug.org> From: kagel@bloomberg.net> Subject: Re:Error 747 on create trigger [10085]> Date: Tue, 9 Oct 2007 12:51:37 -0400> > Oops, that got away from me. Note that you can do what you like this: > > create function fix_blank( l_name char(5) ) returning char; > > if (l_name = ' ') then > > return char::NULL; > > else > > return l_name; > end function; > > create trigger test_trig insert on test > referencing new as post > for each row (execute function fix_blank(post.id,post.name) INTO name); > > That actually modifies the value of name with the return value of the function > fix_blank() BEFORE performing the insert. > > Alternatively IB you can use: WHEN (name = ' ') (execute function > fix_blank...) > so that the function is only called when needed to reduce the overhead of the > trigger. > > Art S. Kagel > > ----- Original Message ----- > From: Georges Martin <ids@iiug.org> > To: ids@iiug.org > At: 10/09 12:27:35 > > IDS 10.FC6 > > I'm trying to create a trigger on a table. > The table has 2 columns (id integer, name char(3)); > whenever a user inserts a name with blanks (' ') I want to update the table > and replace the blanks with the null value. > Whenever I run the following insert command: insert into test values(1,' '), I > get the 747 error: Table or column matches object referenced in triggering > statement. > Any idea or workaround? > > Below the trigger and the stored procedure > > create trigger test_trig insert on test > referencing new as post > for each row (execute procedure upd_test(post.id,post.name)) > > create procedure upd_test (l_id integer,l_name char(5)) > if (l_name==' ') then > update test set name=null where id=l_id; > end if; > end procedure; > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Are you ready for Windows Live Messenger Beta 8.5 ? Get the latest for free today! http://entertainment.sympatico.msn.ca/WindowsLiveMessenger