Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2001
    Posts
    5

    Unanswered: trigger updating more than it should?

    I have a program developed in delphi using ADO connections to the database, and whenever I perform an insert or update action on a dataset, I get a message that the row has changed and cannot be updated (even though it usually works...) Anyway after several tests, I have found that it is one of my triggers that is causing this. The trigger is defined as follows
    Code:
    CREATE TRIGGER updateSousdossierInfoClassifications ON [dbo].[Item_Dossier] 
    FOR INSERT, UPDATE
    AS
    
    INSERT INTO sousdossier_info_classifications 
      SELECT sousdossier,case when classification = '' or classification is null then 'Autre' else classification end, (select taux from ratio where id = 'MARKUP')
      FROM item_dossier i
      WHERE NOT EXISTS ( select * from sousdossier_info_classifications where sousdossier = i.sousdossier and classification =  case when i.classification = '' or i.classification is null then 'Autre' else i.classification end)
     AND i.id in (select id from inserted)
    Now this updates the sousdossier_info_classifications table as it's supposed to, however the dataset on item_dossier also seems to be modified.

    Any ideas?

    Thanks
    Ben

  2. #2
    Join Date
    Jan 2002
    Location
    Villamalea (Spain)
    Posts
    4

    Instead of

    If you don't want to insert in the table where the trigger is defined you should use something like this:

    CREATE TRIGGER updateSousdossierInfoClassifications ON [dbo].[Item_Dossier]
    INSTEAD OF INSERT
    AS

    I hope this help you.

    See you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •