I'm having this problem:

The trigger in TABLE1 gets fired and loads TABLE2. That
part works beautifully. However when I try to manipulate
the data in TABLE2 (extract to a text file using DTS, etc)
it seems like TABLE2 is been affected by the trigger
because the load does not succeeded and the entire data in
the TABLE2 gets deleted and I'm not even touching the
TABLE1 where the trigger is.

I don't see anything in my trigger syntax that would
affected TABLE2 except if TABLE1.COL1 gets updated. Thanks
in advance for any help on this.

Here is the trigger:

CREATE TRIGGER [TR_MYTRIGGER] ON [dbo].[TABLE1]
FOR INSERT, UPDATE
AS
BEGIN TRANSACTION

IF UPDATE(COL2)
IF (SELECT COL2 FROM TABLE1)='WHATEVER'
DELETE FROM TABLE2
INSERT INTO TABLE2 (
COL1,
COL2,
COL3
)
SELECT
COL1,
COL2,
COL3
FROM TABLE3

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
PRINT 'Error ocurred. Transaction has been rolled
back'
END
ELSE
BEGIN
PRINT 'Transaction completed successfully'
END