Home » SQL & PL/SQL » SQL & PL/SQL » Database Trigger
Database Trigger [message #196084] Wed, 04 October 2006 01:38 Go to next message
skkazmi
Messages: 44
Registered: April 2006
Member
I would like to stop insertion into a particular table by using database trigger.
Plz provide me the complete information regarding my issues.

Khurram
Re: Database Trigger [message #196102 is a reply to message #196084] Wed, 04 October 2006 02:57 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It cannot be done "silently". You may raise an exception (using raise_application_error() ) within a BEFORE INSERT trigger though. The exception will be passed back to the SQL INSERT statement which will then fail with your specified ORA-20??? error message.

If you want it to just quietly not insert the row based on some condition and return no error - it can't be done.

If you can't bear to hear the word "No", it is technically possible to record a log of all of the rows that should not have been inserted, and then DELETE them in an AFTER INSERT statement level trigger. This is such a stupid idea, I'm not going to describe it further.

On a similarly ridiculous note, you can rename the table and replace it with a view (select * from renamed_table). You can then place an INSTEAD OF INSERT trigger on the view that will conditionally discard the rows that you want to suppress, and explicitly insert the rest into the table. I mention this for completeness, not because it is a good idea. It would create a performance nightmare for high-volume inserts.


Ross Leishman
Re: Database Trigger [message #196103 is a reply to message #196084] Wed, 04 October 2006 02:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
CREATE OR REPLACE TRIGGER no_insert
BEFORE INSERT ON <table>
BEGIN
  raise_application_error(-20001,'No inserts allowed');
END;
Re: Database Trigger [message #196104 is a reply to message #196103] Wed, 04 October 2006 02:59 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Gotcha! Twisted Evil He hates it when I do that.... Wink
Re: Database Trigger [message #196111 is a reply to message #196104] Wed, 04 October 2006 03:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Git!
Re: Database Trigger [message #196204 is a reply to message #196111] Wed, 04 October 2006 08:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
revoke insert privilege from the table.
If it's the owner you are concerned about, he can also disable the trigger..
Re: Database Trigger [message #196296 is a reply to message #196103] Wed, 04 October 2006 23:04 Go to previous messageGo to next message
skkazmi
Messages: 44
Registered: April 2006
Member
Thanks for reply and help,

Also i would like to now how to stop updation and deletion into the table by using the database trigger,

Thanks again
Khurram
Re: Database Trigger [message #196300 is a reply to message #196296] Wed, 04 October 2006 23:07 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Re-read the replies above and use your imagination.

Ross Leishman
Previous Topic: Re-use a variable..
Next Topic: converting a number to hex
Goto Forum:
  


Current Time: Sun Dec 04 10:38:01 CST 2016

Total time taken to generate the page: 0.10528 seconds