Problems with Update Trigger

  • Hi, I have the following Trigger Code:

    CREATE TRIGGER [S1_Update] ON [dbo].[Spender]

    FOR UPDATE

    AS

    if update (empf_id)

    select * from inserted

    INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)

    (Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user,  'UPDATE' as Art    from inserted)

    if update ([Name])

    select * from inserted

    INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)

    (Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user,  'UPDATE' as Art    from inserted)

    if update (Vorname)

    select * from inserted

    INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)

    (Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user,  'UPDATE' as Art    from inserted)

    If  update (telefon)

    select * from inserted

    INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)

    (Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user,  'UPDATE' as Art    from inserted)

    If  update ([str])

    select * from inserted

    INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)

    (Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user,  'UPDATE' as Art    from inserted)

    If update (land)

    select * from inserted

    INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)

    (Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user,  'UPDATE' as Art    from inserted)

    If update(plz)

    select * from inserted

    INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)

    (Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user,  'UPDATE' as Art    from inserted)

    if update (ort)

    select * from inserted

    INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)

    (Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user,  'UPDATE' as Art    from inserted)

    If update  (bezirk)

    select * from inserted

    INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)

    (Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user,  'UPDATE' as Art    from inserted)

    If update (anrede)

    select * from inserted

    INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)

    (Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user,  'UPDATE' as Art    from inserted)

    If update (kto)

    select * from inserted

    INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)

    (Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user,  'UPDATE' as Art    from inserted)

    If  update(blz)

    select * from inserted

    INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)

    (Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user,  'UPDATE' as Art    from inserted)

    if update (Inaktiv)

    select * from inserted

    INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)

    (Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user,  'UPDATE' as Art    from inserted)

    if update  (gebdat)

    select * from inserted

    INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)

    (Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user,  'UPDATE' as Art    from inserted)

    That trigger works very slow . The table has 535286 rows. What can I do ?

     

    Thanks for help !

    Markus

  • Hi

    partition the table into smaller tables which

    contains the rows you need and then apply the trigger.

     

    Try this ..and please reply..

    regards,

    jyothirmayee.

     

     

     

  • hi ! thanks. it works now.

     

  • As written every time it runs it does one or more selects from inserted and 14 inserts

    If  update(blz)

    select * from inserted

    INSERT INTO [eGecko3_Tr].[dbo].[Änderungen_Spender] (Spender_ID, [Name], Vorname, Telefon, [Straße], Land, PLZ, Ort, Art, Anrede, Bezirk, Kto, BLZ,Inaktiv, GebDatum, Sachbearbeiter)

    (Select empf_id, [Name], Vorname, telefon, [str], land, plz, ort, anrede, bezirk, kto, blz, inaktiv, gebdat, aend_user,  'UPDATE' as Art    from inserted)

    There's no BEGIN...END block so the if only applies to the first select. The insert into will always run.

    Why are you doing the first select * from inserted?

    I think this should probably read

    IF UPDATE(blz)

     BEGIN

      SELECT *  FROM inserted --what's this for?

      INSERT INTO ....

     END

    How many rows are you updating at a time?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • i'd also advise the following changes

    if update (Inaktiv)

    begin

    if (select Inaktiv from inserted)(select Inaktiv from deleted)

    begin

    INSERT INTO ....

    end

    end

    that way your trigegr doesn't fire when you update a row but set it's value to the previous value - e.g update mytable set myfield=myfield

    some apps (mainly those using stored procedures) perform an update of every field in a table regardless of whether they have changed or not - this will fire your trigger every time - even though the data doesn't appear to change.

    MVDBA

  • That's not going to work very well for a multiple row update.

    Perhaps something more like

    Insert into <some tbl> (<field list> )

    SELECT <fields>

    FROM inserted inner join deleted ON inserted.<primary key field>=deleted.<primary key field>

    WHERE inserted.Inaktiv != deleted.Inaktiv

    This does assume that the pk doesn't change.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • agreed

    MVDBA

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

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