Post reply

Why the insert trigger doesnt fire?

  • 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

  • July 13, 2004 at 6:08 am

    #514374

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

    can you post the trigger contents?

    MVDBA

  • July 14, 2004 at 1:22 am

    #514508

    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)

  • July 14, 2004 at 5:40 pm

    #514571

    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.

  • July 15, 2004 at 2:13 am

    #514601

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

  • July 15, 2004 at 2:19 am

    #514603

    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

  • July 15, 2004 at 2:23 am

    #514604

    good point-

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

    MVDBA

  • July 16, 2004 at 1:20 am

    #514795

    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

     

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

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