Why the insert trigger doesnt fire?

Question

Post reply

Why the insert trigger doesnt fire?

Diema

SSC Enthusiast

Points: 161
More actions
July 13, 2004 at 5:23 am

#59716

Can you help me?
I have created a package on my MSSQL2000 which contains data from a table on another server.One row is inserted in a table (newly created on my server) regulary.I have created a insert trigger to delete the old records and leave just the last one.But the trigger doesn't firedespite the fact that I see the new inserted record . Why?
DD

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

what method are you using to write the record to the server?

can you post the trigger contents?

MVDBA

Diema SSC Enthusiast Points: 161 More actions · Answer 2

I have created a local package (using SQL Server Enterprise Manager - DataTransformationServices).It contains two providers (a source and a destination ).They are linked with Transform Data Task.In Transform Data Task property I choose the table from the source and create a new table (with the same structure) on the destination server.On the destination table one row is inserted every minute(I've made the schedule to run the package every minute). I want to have just 1 record in the table.

The trigger is simple:

CREATE TRIGGER Delete_old ON [dbo].[bl4_CalcPar_lastmin]

AFTER INSERT

AS

DELETE FROM bl4_CalcPar_lastmin

WHERE [time]<>(select [time] from inserted)

Adrienne250 Right there with Babe Points: 737 More actions · Answer 3

Have you tried to uncheck the "use fast load" found in "Transform Data Task properties" "Options" tab? I did a quick test and my trigger fired when I did.

Diema SSC Enthusiast Points: 161 More actions · Answer 4

I'll check,but I have found a new solution of my problem without using trigger.Thanks a lot!

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

quite right.

there are several options in DTS packages that avoid using Contraints, triggers and relationships.

you chould also consider adding the following

if @@rowcount=1

as your delete from ...where ...

will fail if more than one row is inserted.

infact - you should check that only one row is inserted.

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

good point-

why don't you get the DTS to remove all of the other rows

MVDBA

Diema SSC Enthusiast Points: 161 More actions · Answer 7

Thanks a lot for your advice!

My solution is the folowing:

I use Execute SQL Task from DTS and I execute Update statement against table. This way I always have only one record in a destination table, which is updated periodicaly.(without trigger ).

Dima