Hello all!

Here is a real world example for RowsAffected!
This process is generating an output file (in ASCII) that will be processed by a bank.

I am porting an SQL Server 7 script to IB/FB.
I have seen a lot of code using this information.
In terms of SQL Server 7, if you need to now the number of affected rows you have to read @@ROWCOUNT
variable.
The Deleted info is different from the Insert info that is diferent from update info. All of them
must be placed in the same structure. It´s a layout request. By the way, did I mention that I have
to limit the number of rows per file. The layout allows only 100k records (in fact 99,998 because
there´s a header and a trailer). So if I have 200k rows I must place them in files out.001, out.002
and out.003. (This file is growing every day. Actually there are 74. In order to do this I am using
SET ROWCOUNT 99998, with a loop . Hugly I know ).

Jason gave a very simple 'UPDATE' syntax. But I am having some troubles to match SQL Server 7 speed
without this feature. I can´t post the UPDATE code right now, but it´s huge. Lot´s of JOINs (from
the left, from the right and FULL too. )
Inserting and deleting are affected by a different where clause.

The fact is that I need to know the numbers of rows affected by Insert, Update, and Delete. I have
to place them in the layout.

Under SQL Server 7 the code looks like

UPDATE .....
SET @UPDT = @@ROWCOUNT

INSERT .....
SET @INST = @@ROWCOUNT

DELETE
SET @DLT = @@ROWCOUNT

The IB/FB code looks like

UPDATE ...
Select count(*)

INSERT
Select Count(*)

DELETE
SELECT COUNT(*)

SQL Server is wwaayyy faster than IB. (Needless to say that the IB implementation is riskier cause
UPDATE and SELECT count(*) must be identical. This is a concern I don´t have to worry about with SQL
Server 7)

Today I finish coding a trigger that will use GENERATORS to give me the Affected ROWS.
I will use Before Insert, Before Delete and Before Update to increment gInsRows, gDelRows ,
gUpdRows. I have created a new table with the structure I need + one field that will be 'I', 'D',
'U'. The triggers will increment the generators accordingly.
If this implementation won´t be closer to SQL Server 7 speed... I guess I will have to go to
IB-Support to ask for some help :-)
(In the new implementation under IB/FB I am using a better structure, I hope this will improve
performance too.)

Cheers
Clément