Update inserted record on a trigger

Question

I'm having troubles while trying to update a record when it is being inserted into a database via external application.

I need the record being inserted from the users in the application takes a value MinimumStock from a table AERO_LOCATIONSTOCKMIN on which are the parent location and the asset type

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

This is the purpose: When a user tries to insert a record on Asset table (via external app), the trigger must to check if the user has selected a location for that Asset. If so, the trigger must retrieve the parent location for the selected location and check if there is stock minimum defined for this AssetType and for this parent location.

If all is fulfilled, the trigger must to set the field UDFText01 to the minimum stock defined in the above table.

Here is what I've tried so far. The trigger is checking well the requirements, but it fails when trying to UPDATE and raising the following error:

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

I tried to modify the UPDATE statement in many ways but all of them raises the same error message.

EDIT

Following @Sean Lannge's suggestion I've edited my trigger in order to manage more than one inserts. The trigger is not longer showing the error message but the changes (insert) are not saved into database.

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;

Answer 1

Based on the extended discussion here see if something like this isn't a bit closer to what you are trying to do.

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