ASE15.0.2 Bulk insert problem...

[hitgon]

In my Banking Application.

I have one container table. DAILY_TRN

and after bank day end my daily trn table is truncate.

There are three trigger(INSERT/UPDATE/DELETE) on DAILY_TRN table

which insert Data in MAIN_TRN and There are also three trigger(INSERT/
UPDATE/DELETE)

on MAIN_TRN and after the effect of main_trn trigger final records
insert in BALANCE_MST.

Here problem is that ...

when i do nomal insert or one by one insert on my DAILY_TRN table then
every trigger effect on MAIN_TRN is ok.

but when i run the following types of bulk insert ...
...................................................................................................
example:

INSERT INTO DAILY_TRN
SELECT * FROM DAILY_TRN_DUMMY;
COMMIT;
....................................................................................................
here DAILY_TRN_DUMMY containt more then 56 records.

here Insert in DAILY_TRN is ok but INSERT trigger effect of DAILY_TRN
in MAIN_TRN (is not properly.)

is only final record which is insert in DAILY_TRN

so here only last record which is insert in DAILY_TRN, it's trigger
effect is available in MAIN_TRN instead

of all the records trigger effect which is inserting in DAILY_TRN.

Here actually..

According to my TRIGGER logic of DAILY_TRN and MAIN_TRN.
(which working fine in normall insert senario.i.e. one by one
inserts)

above bulk insert properly work in oracle but
any way .. in above bulk insert case not working properly in
ASE15.0.2.

because if i insert 50 records in DAILY_TRN then also 50 or more then
that records insert in MAIN_TRN.

please suggest me proper solutions.
--
Regards,
Gondalia Hitesh (D.B.A.)
Acute Softwares Pvt. Ltd.
Ahmedabad
www.acutesoftwares.in

[hitgon]

[Manish Negandhi]

>    According to my TRIGGER logic of DAILY_TRN and MAIN_TRN.
>   (which working fine in normall insert senario.i.e. one by one
> inserts) >   above bulk insert properly work in oracle but
>   any way .. in above bulk insert case not working properly in
> ASE15.0.2.

This is because triggers are not fired during bulk insert / bcp etc.
What you can do here is , you can insert record in while loop
something like

while @var1 < 100
begin
select @var1=@var1+1
insert into table1
select * from table2
end
go

-HTH
Manish Negandhi
[TeamSybase Intern]

[michael...@gmail.com]

Actually I don't think that the OP is using bulk insert (as ASE
understands it). He really means "batch" insert (ie. more than one row
at a time). If the trigger is coded in a way that assumes that it will
be fired once for each row then a multi-row insert will not be
handled correctly, and you will have the behavior that the OP
describes (assuming that I understood it correctly).

So the trigger needs to be written in a way that handles the case
where the "inserted" and/or "deleted" rows have multiple rows.

Michael