在触发器上更新插入的记录

ɐsɹǝʌǝɔıʌ

通过外部应用程序将记录插入数据库时，尝试更新记录时遇到麻烦。

我需要从应用程序中的用户插入的记录MinimumStock从一个表中获取一个值，该表AERO_LOCATIONSTOCKMIN中是父位置和资产类型

LOCATIONSTOCKMINID  ASSETTYPEID   LOCATIONID    MINIMUMSTOCK
   54000000001      54000000043   43200000357      12.00
   54000000002      54000000043   43200000883      6.00

目的是：当用户尝试Asset通过外部应用程序在表上插入记录时，触发器必须检查用户是否为此选择了位置Asset。如果是这样，触发器必须检索所选位置的父位置，并检查是否为此AssetType和该父位置定义了最小库存量。

如果满足所有条件，则触发器必须将字段设置UDFText01为上表中定义的最小库存。

到目前为止，这是我尝试过的。触发器正在很好地检查需求，但是在尝试更新并引发以下错误时失败：

The transaction ended in the trigger. The batch has been aborted。

我试图以多种方式修改UPDATE语句，但它们均引发相同的错误消息。

编辑

遵循@Sean Lannge的建议，我已编辑触发器以管理多个插入。触发器不再显示错误消息，但是更改（插入）未保存到数据库中。

ALTER TRIGGER [spectwosuite].[TRI_ASSET_STOCKMIN] ON [spectwosuite].[ASSET]
FOR INSERT AS 
BEGIN
  IF (UPPER(USER) != 'SPECTWOREPLENG')

    DECLARE @assettypeid numeric(15);
    DECLARE @assetid numeric(15);
    DECLARE @locationid numeric(15);
    DECLARE @parentlocationid numeric(15);
    DECLARE @stockmin numeric(9,2);
    DECLARE @mistock varchar(100);


    DECLARE crs_ROWS CURSOR FOR
    SELECT ASSETID, ASSETTYPEID, LOCATIONID
    FROM inserted;

    OPEN crs_ROWS;
      FETCH NEXT FROM crs_ROWS INTO @assetid, @assettypeid, @locationid 
      WHILE (@@FETCH_STATUS = 0)
      BEGIN

            SELECT @parentlocationID = ParentLocationID FROM Location 
                    LEFT JOIN Asset ON Asset.LocationID = Location.LocationID
                    WHERE Asset.LocationID = @locationid;                   


            IF NOT EXISTS 
                (SELECT * FROM Location LEFT JOIN INSERTED AS i ON Location.LocationID = i.LocationID 
                    WHERE Location.LocationID = i.LocationID)
                BEGIN

                    RAISERROR ('Please fill the Location for the Asset.',16,1);
                    ROLLBACK;
                END
            ELSE            

                    IF EXISTS (SELECT MinimumStock FROM AERO_LOCATIONSTOCKMIN
                        WHERE AssetTypeID = @assettypeid AND LocationID = (SELECT ParentLocationID FROM Location WHERE LocationID = @locationID))

                        BEGIN

                            SELECT @stockmin = MinimumStock FROM AERO_LOCATIONSTOCKMIN
                                WHERE AssetTypeID = @assetTypeID AND LocationID = 
                                        (SELECT ParentLocationID FROM Location WHERE LocationID = @locationID);

                                SELECT @mistock= CONVERT(varchar(100),@stockmin);   
                                --RAISERROR (@mistock,16,1);            


                                UPDATE spectwosuite.ASSET
                                SET UDFText01 = @mistock 
                                FROM
                                     INSERTED I
                                INNER JOIN spectwosuite.ASSET T ON
                                     T.AssetID = I.AssetID

                            --UPDATE spectwosuite.ASSET SET UDFText01 = @mistock
                            --  FROM spectwosuite.ASSET AS A INNER JOIN inserted AS I
                            --  ON A.AssetID = I.AssetID;                   

                        END

                    ELSE
                            RAISERROR ('The parent Location for the Asset Type doesn't have minimum stock defined',16,1);
                            ROLLBACK;

       FETCH NEXT FROM crs_ROWS INTO @assetid, @assettypeid, @locationid; 
      END;
      CLOSE crs_ROWS;
      DEALLOCATE crs_ROWS;

END;

肖恩·兰格

根据此处的扩展讨论，看一下这样的事情是否与您尝试做的事情不太接近。

ALTER TRIGGER [spectwosuite].[TRI_ASSET_STOCKMIN] ON [spectwosuite].[ASSET] FOR INSERT AS 
IF (UPPER(USER) != 'SPECTWOREPLENG')
BEGIN

    create table #MyInsertedCopy
    (
        --whatever columns go here that you want to display for error rows
        ErrorMessage varchar(50)
    )

    insert #MyInsertedCopy
    select i.* --use your real columns, not *
        , 'Please fill the Location for the Asset' as ErrorMessage
    from inserted i
    left join Location l on l.LocationID = i.LocationID 
    where l.LocationID IS NULL

    insert #MyInsertedCopy
    select i.* --use your real columns, not *
        , 'The parent location for that Asset doesn''t have minimum stock defined' as ErrorMessage
    from inserted i
    left join AERO_LOCATIONSTOCKMIN a on a.AssetTypeID = i.AssetID
    left join Location l on l.ParentLocationID = i.LocationID 
    where a.AssetTypeID is NULL

    update a
    set UDFText01 = MinimumStock 
    FROM INSERTED I
    INNER JOIN spectwosuite.ASSET T ON T.AssetID = I.AssetID
    INNER JOIN AERO_LOCATIONSTOCKMIN a on a.AssetTypeID = i.AssetID
    INNER JOIN Location l on l.ParentLocationID = i.LocationID 

    IF EXISTS(select * from #MyInsertedCopy) 
        --do something to report that there are rows that failed
        select * from #MyInsertedCopy
END