Select rows with AFTER INSERT trigger?

  • Hi experts,

    Is it possible to do a SELECT in a trigger?

    I have the following:

    [dbo].[TR_Insert] ON [dbo].[Customer]

    AFTER INSERT

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- get the last id value of the record inserted or updated

    DECLARE @cid INT

    SELECT @cid = [CustID]

    FROM INSERTED

    SELECT CustomerFirstName, CustomerLastName

    FROM Customers

    WHERE CustID = @cid

    Thanks in advance

  • Currently it is possible, but that functionality is deprecated. It also isn't a good idea as triggers are part of the transaction and returning data from the trigger will extend the length of the transaction.

  • We have a vendor app that can't be modified.

    However, we are allowed to manipulated what they call custom fields.

    These custom fields are literally called field1, field2... up to filed5.

    Right now, we are tasked with grabbing CustomerName associated with just inserted CustID from the customer table and display on the vendor app.

    Only idea we can think of is Trigger.

    Do you have any other ideas of recognizing when a new record is inserted and how to grab that new record based on the just inserted ID?

  • simflex-897410 (12/3/2014)


    We have a vendor app that can't be modified.

    However, we are allowed to manipulated what they call custom fields.

    These custom fields are literally called field1, field2... up to filed5.

    Right now, we are tasked with grabbing CustomerName associated with just inserted CustID from the customer table and display on the vendor app.

    Only idea we can think of is Trigger.

    Do you have any other ideas of recognizing when a new record is inserted and how to grab that new record based on the just inserted ID?

    Did you ask the vendor how to do this?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi Jeff,

    Yes, I did but since we don't have a support agreement with them, they are asked us what we tried and we told them the trigger I posted here but they said they don't know much about it which I know is a lie.

  • simflex-897410 (12/3/2014)


    Hi Jeff,

    Yes, I did but since we don't have a support agreement with them, they are asked us what we tried and we told them the trigger I posted here but they said they don't know much about it which I know is a lie.

    Since you don't have a support agreement, I would do whatever I wanted. Obviously it needs to go through full life cycle testing. I don't know enough about how the application works to suggest any other options. I don't even understand why returning values from a trigger would cause those values to be displayed by the application if the application isn't expecting those specific results to be returned by the operation that is doing the insert.

    Additionally, I just really looked at the trigger code. I didn't look at it earlier because I didn't need to know what the code is doing to state that returning results from a trigger is a bad idea. Now having looked at it, the trigger itself is not one I would allow in a database I am responsible for even if it didn't return results. YOU are usinga scalar variable to hold results from the virtual inserted table which means that the trigger cannot correctly deal with a set-based insert. I'm sure the application only inserts one row at a time, but as a best practice every trigger should be written to deal with set-based actions.

  • Since you don't have a support agreement, I would do whatever I wanted.

    I think this is a lousy, albeit, silly assumption on your part. Forgive me for being blunt.

    The owners of the app wanted to have a way to display both Customer ID and Customer name that were not available with current state of the app.

    Since the app in its current state does not display those fields, and they recognize that there are instances when the clients who bought their software might to try use some additional fields for whatever additional needs they might have, they added 5 fields they call customs fields, fields1 through fields5.

    Yes, they are intended to force you to pay them for their support but again, they didn't pay them for support.

    So, I am not trying to do whatever I want. I am trying to find a way to give them what they asked for.

    I will have to find a way to get this done.

    This site has been generous in helping me in the past and I remain grateful for that but every once in a while, you run into someone who criticizes your approach but provide no tangible alternative solution.

  • I think Jack simply meant do whatever it takes to satisfy the customer request. You don't need to worry about the vendor's product.

    Gerald Britton, Pluralsight courses

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

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