Home » SQL & PL/SQL » SQL & PL/SQL » Before Insert trigger - Skip Insert
Before Insert trigger - Skip Insert [message #189443] Thu, 24 August 2006 10:57 Go to next message
MyronWintonyk
Messages: 4
Registered: August 2006
Location: Calgary, AB, Canada
Junior Member

I have a requirement to skip inserts into a table on certain conditions. basically, I want to do the following in a PL/SQL trigger:

if condition then
skip insert
end if;

Simple, but I cannot find a way to skip the insert. Does anyone have an idea?

I could use a global temporary table as my main table and insert into the real table if the conditions do not match, but that seems a bit goofy to me.
Re: Before Insert trigger - Skip Insert [message #189449 is a reply to message #189443] Thu, 24 August 2006 11:41 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
How about creating an instead-of trigger on a view? Insert into the view instead of the table and handle the conditional logic in the trigger.
Re: Before Insert trigger - Skip Insert [message #189452 is a reply to message #189449] Thu, 24 August 2006 12:26 Go to previous messageGo to next message
MyronWintonyk
Messages: 4
Registered: August 2006
Location: Calgary, AB, Canada
Junior Member

I have considered the idea of the instead of trigger, and I may proceed in that way if a "better" one cannot be found. However, that requires a fair number of changes within the application (to insert into the view instead of the table). I'd like to avoid that if possible.

But, thanks for the idea!
Re: Before Insert trigger - Skip Insert [message #189812 is a reply to message #189452] Sun, 27 August 2006 23:58 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You can't do it. At best (other than the view idea) you can record the rowid of the row inserted in a package level variable and then delete it in an AFTER INSERT statement level trigger. This is really bad programming - don't do it.

Or you can raise an error and handle it in the application.

Ross Leishman
Re: Before Insert trigger - Skip Insert [message #189929 is a reply to message #189443] Mon, 28 August 2006 11:19 Go to previous messageGo to next message
MyronWintonyk
Messages: 4
Registered: August 2006
Location: Calgary, AB, Canada
Junior Member

After some consideration, I've decided the "best" way forward for me is the instead of trigger. I had toyed with several other approaches, but in the end, this is the cleanest.

I did consider the idea of a after insert trigger to delete the row, but of course, you run into the mutating table problem. I even tried an autonomous transaction, but then you end up with a dead-lock.

Thanks for the ideas!
Re: Before Insert trigger - Skip Insert [message #189975 is a reply to message #189929] Mon, 28 August 2006 15:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The method concerning the after insert trigger Ross mentioned would involve an after insert STATEMENT trigger. Whence the storage of rowids in a package. That would circumvent the mutating table error.

I think the cleanest way would be to raise an error and let the application layer handle it. After all, the application layer should be made aware that the insert did not succeed?!
Re: Before Insert trigger - Skip Insert [message #189985 is a reply to message #189975] Mon, 28 August 2006 21:50 Go to previous message
MyronWintonyk
Messages: 4
Registered: August 2006
Location: Calgary, AB, Canada
Junior Member

Thanks for clearing that one up Frank. I never thought of that one ... Doh!
Previous Topic: "Logon as a batch job" privilege - trying to import
Next Topic: YTD Accumulation of Months
Goto Forum:
  


Current Time: Sat Dec 10 20:21:48 CST 2016

Total time taken to generate the page: 0.06764 seconds