Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2012
    Posts
    1

    Unanswered: Reset all rows when one row changes

    Hello,

    I'm trying to create a trigger that will clear the "checked" field when all items with the same classification have been checked.

    The purpose of this is to do cycle counting of inventory, which is when you count the stock levels of a proportion of stock that have the same classification.

    The checked field will be used to record the date it was lasted checked and will cleared once all stock have counted, putting back into the pool of stock to be counted.

    Therefore, the trigger is suppose to count the number of rows that share the same classification as the amended row, and compare that with the number of rows in that classification, that have a date in the checked date field. When the two values are the same, reset all the checked dates to blank.

    But when I tested it, nothing happened :( Does anyone know why?

    ALTER TRIGGER [dbo].[UDEF_Last_Checked_Reset] ON [dbo].[ASC_PMA_TBL]
    AFTER INSERT
    AS

    BEGIN

    DECLARE @PartOnly varchar(16) -- Part Number
    DECLARE @Rev varchar(4) -- Part Revision
    DECLARE @PartCode as varchar(2) -- Part Classification
    DECLARE @Last_Checked as datetime -- Checked Date

    SELECT @PartOnly = PMA_PART_ONLY,
    @Rev = PMA_PART_REV,
    @PartCode = PMA_PART_CODE,
    @Last_Checked = [PMA_LAST_CHECK_DATE]


    FROM Inserted


    -----------------------------------------------------------------------------
    declare @countpartcode as decimal(18,5) --Counter for part classification
    declare @countlastcheck as decimal(18,5) -- Counter for completed parts
    --set counters
    set @countpartcode = (SELECT SUM(case when [PMA_PART_CODE]=@PartCode THEN 1 ELSE 0 END)from ASC_PMA_TBL)
    set @countlastcheck = (select SUM(case when [PMA_LAST_CHECK_DATE] IS NULL AND PMA_PART_CODE = @PartCode THEN 1 ELSE 0 END) from asc_pma_tbl)
    --compare counters
    if @countpartcode = @countlastcheck
    -- clear checked date field on all parts with same classifcation
    UPDATE [ASC_PMA_TBL]
    SET [PMA_LAST_CHECK_DATE] = ''
    WHERE [PMA_PART_CODE] = @PartCode


    END

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your trigger will certainly fail on multi-record transactions.
    NEVER write triggers that cannot handle multi-record transactions.
    Rewrite your trigger using set-based logic, and then try debugging it. There's really no point in any of us assisting in debugging code that should not be implemented even if it did work.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

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