| How to prevent trigger from firing on failed insert [message #653038] |
Fri, 24 June 2016 10:56  |
laddg
Messages: 9
Registered: May 2005
|
Junior Member |
|
|
I am inexperienced, but learning fast.
I have a trigger that fires on 'after insert'. It works well.
When a record failed to insert (for integrity constraints), I assumed that it would not fire since it was an 'after', but it does.
How can I prevent this trigger from firing on a failed insert? Can I test inside the trigger for success of the insert? Maybe as part of the 'when' clause?
Thanks in advance.
DJG
|
|
|
|
| Re: How to prevent trigger from firing on failed insert [message #653042 is a reply to message #653038] |
Fri, 24 June 2016 11:02  |
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
The way constraints work is that the row (please don't say "record" when you mean "row") is inserted, then the constraint is checked, and then if necessary the insert is rolled back. Whatever your trigger is doing should be rolled back too. The exception is if your trigger is doing something really stupid, such as an autonomous transaction or sending an email.
Almost certainly you are using trigges in an inappropriate way if this is a problem. Most applictions never need a trigger, and using them to implement business logic is usually a very bad idea.
|
|
|
|
|
|
| Re: How to prevent trigger from firing on failed insert [message #653044 is a reply to message #653043] |
Fri, 24 June 2016 11:08 |
laddg
Messages: 9
Registered: May 2005
|
Junior Member |
|
|
Maybe I did not give enough information. The process is:
A row is inserted into a table by any of a large number of different processes
Once the row is inserted, the trigger creates a web event which then transmits the row to a remote service
If the row is not inserted properly, I do not want to call the web service
Essentially, if the insert fails, I do not want to fire the trigger
Does that make sense?
[Updated on: Fri, 24 June 2016 11:08] Report message to a moderator |
|
|
|
|
|
| Re: How to prevent trigger from firing on failed insert [message #653046 is a reply to message #653044] |
Fri, 24 June 2016 11:14 |
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Yes, it makes sense, and is a bad design. As you have discovered! Really, triggers are not meant for this sort of thing. Some sites have rules banning such use completely.
A better design would be be to go via a queue. Your trigger can enqueue a message (which will be rolled back if the insert fails) and then after commit a job process can dequeue the message and use it to call the web service, asynchronously with regard to the transaction. That way you decouple the external event from the internal event, and have full control of it.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to prevent trigger from firing on failed insert [message #653059 is a reply to message #653053] |
Sat, 25 June 2016 07:02 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
laddg wrote on Fri, 24 June 2016 13:23Completely out of my control. I know the company is upgrading databases regularly (we have hundreds), just haven't gotten to this one yet.
I appreciate that you may not have any control, but saying they are "upgrading databases regularly" seems a bit of dodge. We must have different ideas about what is meant by "regularly". I haven't even SEEN a 9i database in 10 years. There are now no less than THREE major releases superseding 9i. Even if "regularly" means "regularly, once every decade" they would at least be to 10g by now.
[Updated on: Sat, 25 June 2016 07:03] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
