Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Valid way to bail out of After Insert Trigger?

Posted on 2014-08-14
17
Medium Priority
?
176 Views
Last Modified: 2014-08-21
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
0
Comment
Question by:CoastalData
  • 9
  • 8
17 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40260945
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
 
LVL 3

Author Comment

by:CoastalData
ID: 40261000
My bad! Meant to say "After Trigger"... I've sent a request for attention to rename the question!
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40261034
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:CoastalData
ID: 40261202
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
 
LVL 3

Author Comment

by:CoastalData
ID: 40261207
Wait, my trigger inserts a record into the same triggered table! Will this cause an infinite loop or something?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40261302
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
 
LVL 3

Author Comment

by:CoastalData
ID: 40261500
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40261641
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
 
LVL 3

Author Comment

by:CoastalData
ID: 40261762
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
 
LVL 3

Author Comment

by:CoastalData
ID: 40261781
Maybe something like this?

IF (SELECT Count(*) FROM inserted) > 1
      RETURN;
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40261805
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
 
LVL 3

Author Comment

by:CoastalData
ID: 40261900
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40261989
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
 
LVL 3

Author Closing Comment

by:CoastalData
ID: 40263115
Your help and expertise is much appreciated Scott! Thanks again!
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40263264
You're welcome!  Good luck with the trigger!
0
 
LVL 3

Author Comment

by:CoastalData
ID: 40274481
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40276959
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question