Select rows with AFTER INSERT trigger?

Question

Select rows with AFTER INSERT trigger?

simflex-897410

Hall of Fame

Points: 3881
More actions
December 3, 2014 at 10:26 am

#292589

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

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

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.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 2

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?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

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)

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 4

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.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

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.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 6

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.

g.britton SSChampion Points: 13863 More actions · Answer 7

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