Valid way to bail out of After Insert Trigger?

Hello, is this a good/valid way to bail out of a Before Insert trigger before continuing other work? Will it allow the trigger to continue processing?

DECLARE @TripType AS Varchar(150)

-- Make sure this is a student trip, else bail out and allow the insert to proceed.
SELECT @TripType = TripType FROM tblTrips WHERE TripNo = (SELECT TripNo FROM inserted);
IF @TripType <> 'Student (ST)' RETURN 0
LVL 3
Jon JaquesInformation TechnologistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
SQL Server doesn't have "BEFORE INSERT" triggers.

It's an "INSTEAD OF INSERT" trigger.  If you bail out, the row will not be inserted.
0
Jon JaquesInformation TechnologistAuthor Commented:
My bad! Meant to say "After Trigger"... I've sent a request for attention to rename the question!
0
Scott PletcherSenior DBACommented:
SQL triggers only fire once per statement -- even if, say, 10M rows were affected -- not once per row.  Therefore, there could be a mix of types in the same trigger, so 'ST' and some not.

To exit when there are no 'ST' types, you can do this:

IF NOT EXISTS(SELECT 1 FROM inserted WHERE TripType = 'Student (ST)')
    RETURN

RETURN will exit the trigger gracefully.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jon JaquesInformation TechnologistAuthor Commented:
Well, 2 things; first, my applications never inserts more than 1 record at a time into bookings, so technically, that shouldn't be a problem. The second thing is my whole (new) problem in that I realized that "TripType" does not exist in the triggered table; It needs to be looked up from tblTrips by the TripNo field.
0
Jon JaquesInformation TechnologistAuthor Commented:
Wait, my trigger inserts a record into the same triggered table! Will this cause an infinite loop or something?
0
Scott PletcherSenior DBACommented:
It's possible, if the RECURSIVE_TRIGGERS option is set ON for that db.

To me, the easiest way around that is to set CONTEXT_INFO so that you can tell if/when a nested trigger is firing.  If you want more info on that option, just let me know.
0
Jon JaquesInformation TechnologistAuthor Commented:
Okay, I just read up a bit on CONTEXT_INFO... it seems like a session variable, so I might use something like this:

IF (SELECT CONTEXT_INFO()) <> ""
     RETURN;

SET CONTEXT_INFO 'True';

Is good?
0
Scott PletcherSenior DBACommented:
Hmm, I wouldn't be that bludgeoning.  You're assuming nothing else will ever use CONTEXT_INFO, and I wouldn't want to make that assumption.

Instead, just use, say, the first byte.  Create some documentation somewhere that identifies how each byte / block of bytes in CONTEXT_INFO is going to be used.


IF SUBSTRING(CONTEXT_INFO(), 1, 1) = 0xEF
    RETURN;

DECLARE @context_info varbinary(128)
SET @context_info = 0xEF + SUBSTRING(CONTEXT_INFO(), 2, 127) --set first byte but preserve the rest
SET CONTEXT_INFO @context_info
0
Jon JaquesInformation TechnologistAuthor Commented:
Wow, good stuff! I've integrated that now into the trigger. I still need to deal with this comment, though:

SQL triggers only fire once per statement -- even if, say, 10M rows were affected -- not once per row.  Therefore, there could be a mix of types in the same trigger, so 'ST' and some not.

The only use-case that I have that would do a multi-record insert is when I'm moving data back and forth to my development database for testing, and as I understand it, this could cause the trigger to fire.

Could I maybe fire a RETURN anytime there's more than 1 record in the transaction?
0
Jon JaquesInformation TechnologistAuthor Commented:
Maybe something like this?

IF (SELECT Count(*) FROM inserted) > 1
      RETURN;
0
Scott PletcherSenior DBACommented:
For absolute max efficiency, I'd do it this way:

--if more than one row affected, exit trigger.
IF (SELECT COUNT(*) FROM (SELECT TOP (2) * FROM inserted) AS derived) > 1
    RETURN;

But you could handle mixed types easily enough if you wanted to:

INSERT INTO dbo.destination_table
SELECT ...
FROM inserted
WHERE
    TripType = 'Student (ST)'

If there were no rows that matched, no inserts would occur.  If all 100 matched, 100 would be inserted.  If 50 matched and 50 didn't, 50 would be inserted.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jon JaquesInformation TechnologistAuthor Commented:
Wow Scott, you rule! I'm still thinking that a multi-insert transaction wouldn't be the use-case I'm after, UNLESS I'm thinking something that's wrong... I'm thinking that every user who generates a booking also generates 1 transaction. What I'm wondering is, if 2 or more users book at or nearly at the same time, will the database engine stack them up in the 'inserted' table for processing in one transaction? If the latter is the case, then I may yet need to check the existing count of chaperones, and then process each record in inserted as you suggest, but only to create as many chaps as needed to fulfill the 1:10 ratio dictated by the business need.
0
Scott PletcherSenior DBACommented:
No, different users running at exactly the same time won't be put into the same transaction by SQL.  The INSERT itself would have to be of multiple rows for the trigger to have multiple rows in the inserted table rather than just one.
0
Jon JaquesInformation TechnologistAuthor Commented:
Your help and expertise is much appreciated Scott! Thanks again!
0
Scott PletcherSenior DBACommented:
You're welcome!  Good luck with the trigger!
0
Jon JaquesInformation TechnologistAuthor Commented:
Hey Scott, I can't get this bit of code to work... any suggestions?

IF SUBSTRING(CONTEXT_INFO(), 1, 1) = 0xEF
    RETURN;

DECLARE @context_info varbinary(128)
SET @context_info = 0xEF + SUBSTRING(CONTEXT_INFO(), 2, 127) --set first byte but preserve the rest
SET CONTEXT_INFO @context_info
0
Scott PletcherSenior DBACommented:
Sorry, didn't think CONTEXT_INFO() would return NULL, but it does.

DECLARE @context_info varbinary(128)
SET @context_info = 0xEF + ISNULL(SUBSTRING(CONTEXT_INFO(), 2, 127), 0x00) --set first byte but preserve the rest
...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.