[Firebird-devel] [ firebird-Bugs-436462 ] Rows affected incorrect with trigger

[<no...@so...>]

Bugs item #436462, was opened at 2001-06-26 11:32
You can respond by visiting: 
http://sourceforge.net/tracker/?func=detail&atid=109028&aid=436462&group_id=9028

Category: Client/GDS32
Group: Confirmed Bug
Status: Open
Resolution: None
Priority: 5
Submitted By: Nobody/Anonymous (nobody)
Assigned to: Nobody/Anonymous (nobody)
Summary: Rows affected incorrect with trigger

Initial Comment:

     This was reported in the Borland newsgroups.  
I've confirmed this (without involving IBO) with IB 
6.0.1.6 Open Edition, but I haven't tried it on 
Firebird.  Posted here at Jason Wharton's request.

     -Craig

--------------------------------------------

Subject: 
            Strange VIEW behavior - BUG or what?
       Date: 
            Mon, 25 Jun 2001 08:11:02 -0300
       From: 
            "Fabiano Bonin" <fa...@le...>
 Newsgroups: 
            borland.public.interbase.general




I'm having troubles with updatable views that have 
before update
triggers.
When i update just one record (using the PK), it 
returns 3 rows
affected.
When i update this view using IBO it gives the 
error: 'MULTIPLE ROWS
AFFECTED', because the number of rows affected is 
giving 3 rows!
PS: I can't put the before update in the table, 
because i have several
view
on one table and i expect that each view have an 
specific behavior.
Try this:

/* CREATE A TABLE */
create table test (
  test_id integer not null primary key,
  name varchar(30),
  address varchar(30) );

/* CREATE A VIEW */
create view v_test as
select test_id, name, address from test;

/* INSERT A RECORD */
insert into v_test values (1, 'name1', 'address1');
/* ROWS AFFECTED: 1 */

/* UPDATE THIS RECORD */
update v_test set name = 'name1' where test_id = 1;
/* ROWS AFFECTED: 1 */

/* CREATE A TRIGGER FOR THE VIEW */
set term !! ;
create trigger trg_v_test for v_test
before update
as
begin
  new.name = 'name1';
end !!
set term ; !!

/* UPDATE THE RECORD AGAIN - SAME AS ABOVE*/
update v_test set name = 'name1' where test_id = 1;
/* ROWS AFFECTED: 3 !!!!!!!!!!!!!!!!!!!!!!! */

----------------------------------------------------------------------

You can respond by visiting: 
http://sourceforge.net/tracker/?func=detail&atid=109028&aid=436462&group_id=9028