Validate trigger entries

Question

Post reply

Validate trigger entries

masoudk1990

SSCommitted

Points: 1651
More actions
December 21, 2013 at 11:52 pm

#302577

Hello, I validate my trigger entries like this:
IF @Field = ''
BEGIN
ROLLBACK TRANSACTION GOTO E:
END
E:
E: is last line in my trigger and I have no code after that, I expected I can validate entries with this approach but instead after I go into E: it show me error message:
The transaction ended in the trigger. The batch has been aborted.
Can I ignore above message? Already tried TRY CATCH and it doesn't worked either.
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.

Viewing 6 posts - 1 through 5 (of 5 total)

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

masoudk1990 SSCommitted Points: 1651 More actions · Answer 1

I found this message:

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

Is because "ROLLBACK TRANSACTION".

If we don't want above message we have to remove "ROLLBACK TRANSACTION" and validate our entries outside of trigger.

___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 2

Couple of things.

Can't you just use constraints?

Your posted code seems to assume that there will be just one row coming to trigger, which might not be the case.

masoudk1990 SSCommitted Points: 1651 More actions · Answer 3

Ville-Pekka Vahteala (12/22/2013)
Couple of things.
Can't you just use constraints?
Your posted code seems to assume that there will be just one row coming to trigger, which might not be the case.

Well, if you insert 1000 rows all 1000 rows dont go to trigger at once

In fact your trigger execute 1000 times for each inserted rows 🙂

___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.

masoudk1990 SSCommitted Points: 1651 More actions · Answer 4

Ville-Pekka Vahteala (12/22/2013)
Couple of things.
Can't you just use constraints?
Your posted code seems to assume that there will be just one row coming to trigger, which might not be the case.

Thank you, The insert trigger is called once for multiple row inserts 🙁

Im into big trouble

___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

masoudk1990 (12/22/2013)
Ville-Pekka Vahteala (12/22/2013)
Couple of things.
Can't you just use constraints?
Your posted code seems to assume that there will be just one row coming to trigger, which might not be the case.
Thank you, The insert trigger is called once for multiple row inserts 🙁
Im into big trouble

No you're not. You can use an INSTEAD OF trigger to evaluate each row before you insert it into the table. A better way, of course, is to prevalidate things before you even try an INSERT. Look it up in "Books Online" (get there by pressing the {f1} key when you're in SSMS).

Of course, I can't see your code. If you were to post it, perhaps we'd be better able to assist.

--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)