I am transferring records from one table to another table using a Datapump
task.each record looks like the following:

Field1 Field2 Field3 Field4

While transferring the field1 to 3 ,I update Field4 (it's my flag) and when
the control gose to the next record field4 has a value of either 1 or 0.Now
I'd like to insert records which their Field4 is equal to 1 to a different
table ,but I'd like to do it through a tirgger so while I'm transferring the
records ,that insert takes place as weel.I was thinking about using
trigger.Is it possible to use trigger for that scenario?

Thanks

You can use a trigger yes but then your trigger code would have to
handle batch size. This may also be slow.

Why not make two passes over the source inserting into the correct table
each time. Your SELECT would then need to become qualified (WHERE)

Allan

"RayAll" <RayAll (AT) microsft (DOT) com> wrote

Thanks for yuor reply.

I didn't get this.
What I am currently doing is using a sp to do so.but the problem is that I
run the sp after I insert (and validate) records to a buffer table and then
I go through the records and decide what to do with them.I was looking for a
better and faster solution to this that I rememberd the triggers.so you
think trigger might slow down this process right?

Thanks again

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Oh yes.

The way to tell is test.

The thing about the DTS way without triggers is that anything else
inserting into that table will not fire a trigger because there isn't
one.

Allan

"RayAll" <RayAll (AT) microsft (DOT) com> wrote

But,I 's like to use the trigger in the buffer table and any insert should
fire the trigger.If I don't use the trigger than I'll have to come back and
itterate through all records once more (the way I am doing this now) to
enforce the rules (I am doing this using a stored procedure now) Is it
better than having the trigger on each insert?

Thanks for following this thread up.
Ray
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

why not push your records to your buffer table, then do 2 insert
statements, both with a qualified where statement. The first insert
would push your records to the first table, and the second insert would
push only those records WHERE Field4 = 1.
Are you actually looping through each record in the buffer table
(iterate through all records) or just running an insert statement with a
where clause in it?

Simon Worth

RayAll wrote:

No,I don't iterate through the records:-)

I simply use an insert statement( which is quite fast) ,but I was thinking
that if I enforce the business rules through trigger.that makes more
sence,but if you think that would slow down the whole process.I have
absolutely no problem with using the sp after I have inserted everything to
the buffer table.
Another question here:

in buffer table I have for example 4 fields Field1 Filed2 Field3 Field4

I insert each record into different tables Field1 and Filed2 go to one table
,where Field3 gose to another Table and Field4 as well.Is there a way to
combine all these actions and have only one insert statement which dose care
of all the insertes into different tables? or I have to write different
insert statements?


Question#2) When I use insert statement with a qualified where clause ,dose
it itereate through all records to find out the records which match with the
criteria in where clause? how dose it find the records??


Thanks
"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote