SQL Server Performance

NEED HELP WITH THIS TRIGGER

Discussion in 'T-SQL Performance Tuning for Developers' started by hdocardsystems, Aug 9, 2007.

  1. hdocardsystems New Member

    Folks, thank you in advance for any input or suggestion.
    I got the trigger below working, but for some reason, during the record update to the main table - it suppose to pull the min(iso) from the isobank table. For some reason, it decremented double records from the isobank table - and is not the right min record.
    Can anybody read over the trigger and let me know if I'm missing anything?? The syntax seems okay and the trigger worked, but it depleting my isobank numbers twice faster.
    CREATE Trigger MAIN_Lost_Stolen
    On dbo.MAIN
    FOR UPDATE
    AS
    declare @viso varchar(16)
    declare @viso_parse varchar(19)
    declare @vidnum varchar (16)
    declare @voldiso varchar (16)
    declare @vlostcardcode varchar (20)
    select @vidnum = idnum, @voldiso = iso, @vlostcardcode = lostcardcode from inserted
    select @viso = isobank.iso, @viso_parse = isobank.isoparse from isobank where isobank.iso = (select min(iso) from isobank)
    if @vlostcardcode = 'Lost' or @vlostcardcode = 'Stolen'
    begin
    update dbo.main set ISO = @viso, isoparse = @viso_parse, lostcardcode = 'Printed Lost/Stolen' where main.idnum = @vidnum
    delete from isobank where iso = @viso
    end

    select @viso = NULL
    select @viso_parse = NULL
    select @vidnum = NULL
    Again thank you for any assistance
  2. Adriaan New Member

    Are you absolutely sure that only one row gets updated at any time? Otherwise your trigger will produce incorrect results, as the inserted snapshot contains all the rows affected by the action that fired it, meaning that you're only handling one idnum value picked at random from inserted.
    Your problem is that you're updating the underlying table from its own update trigger, which will fire the trigger once more, eating up the extra value from your isobank. Assuming that the ISO column on main is null before the trigger is firing, just add a check around your update code in the body of the trigger:
    IF EXISTS (SELECT * FROM inserted WHERE ISO IS NULL)
    BEGIN
    your update code
    END
    Let us know if you need the set-based logic for handling multi-row updates.
  3. hdocardsystems New Member

    Adriaan, Thank you for your input.
    Yes, only one row get updated at a time. The trigger ran only when column lostcardcode is updated. It updated the iso column and several other columns from the value in the inserted table.
    The trigger seems running as it should, it run in conjuction of an id software. when an id is lost, we update the lostcard field from the software. when the lostcardcode is updated, an ISO (card nunber) field is updated, which the trigger ran and pull information from the ISO table. It should only pull one row from the iso table, but it pulled two rows. I added the check code, and so far has no luck yet - I am still testing the code. thanks again for the input.
  4. Adriaan New Member

    I notice you're deleting from the isobank table based on one value on the iso column. I would assume that all values on the iso column are unique, but are they really?
    SELECT ISO FROM isobank GROUP BY ISO HAVING COUNT(*) > 1
  5. hdocardsystems New Member

    Yes, they are unique in both tables.
  6. dineshasanka Moderator

    how do you update this table. I reckon there are two updates for same record.
  7. hdocardsystems New Member

    When the lostcardcode is being updated to a 'LOST' or 'STOLEN', the trigger ran and updated the ISO, ISOparse,
    and the lostcardcode = 'Printed lost/stolen' in the main table. The value of the ISO, and ISO parse comes from the ISOBANK table. The update is a manual process ran from the software. There's a selection parameter within the software to change the lostcardcode if another idcard needed to be created. the selection parameter are 'lost' or 'stolen', when the value of that field is change... it suppose to triggered the trigger and update the fields with new values from the isobank table.
    Adriaan, I really appreciate again of all your assistance. thanks
  8. Adriaan New Member

    Well, that was why I suggested that he adds:
    IF EXISTS (SELECT * FROM inserted WHERE ISO IS NULL)
    ... but that didn't seem to help. Perhaps the column has a default of an zero-length string, or a fixed number of spaces?
    IF EXISTS (SELECT * FROM inserted WHERE ISO IS NULL OR ISO = '' OR ISO = ' ')
  9. hdocardsystems New Member

    Maximum field length is 16, and the column has zero length default.
    I put the check statement as below
    IF EXISTS (SELECT * FROM inserted WHERE ISO IS NULL)
    begin
    update dbo.main set ISO = @viso, isoparse = @viso_parse, lostcardcode = 'Printed Lost/Stolen' where main.idnum = @vidnum
    delete from isobank where iso=@viso
    end
    It doesn't seems to update the fields needed to be updated, the only field being updated is only the lostcardcode, and it doesnt pulled anything at all from the isobank table. where should I insert my criteria statement? is it before or after the check statement.
    if @vlostcardcode = 'Lost' or @vlostcardcode = 'Stolen'
  10. hdocardsystems New Member

    Adriaan,
    this one seems to work better. thanks again for your assistance.
    CREATE Trigger MAIN_Lost_Stolen
    On dbo.MAIN
    FOR UPDATE
    AS
    declare @viso varchar(16)
    declare @viso_parse varchar(19)
    declare @vidnum varchar (16)
    declare @voldiso varchar (16)
    declare @vlostcardcode varchar (20)
    set rowcount 1
    select @vidnum = idnum, @voldiso = iso, @vlostcardcode = lostcardcode from inserted
    select @viso = isobank.iso, @viso_parse = isobank.isoparse from isobank where isobank.iso = (select min(iso) from isobank)

    if @vlostcardcode = 'Lost' or @vlostcardcode = 'Stolen'
    begin
    update dbo.main set ISO = @viso, isoparse = @viso_parse, lostcardcode = 'Printed Lost/Stolen' where main.idnum = @vidnum
    delete from isobank where iso = @viso
    end

    select @viso = NULL
    select @viso_parse = NULL
    select @vidnum = NULL
    set rowcount 0

Share This Page