Home » SQL & PL/SQL » SQL & PL/SQL » how to prevent Insert in Before Insert trigger???
how to prevent Insert in Before Insert trigger??? [message #251034] Thu, 12 July 2007 04:22 Go to next message
oralover
Messages: 97
Registered: January 2005
Member
hi all

a very newbie question

i created a table and now want to create a trigger (Before Insert on Each Row) and prevent Insert/Update in this table on some conditions which not belongs this table means in another table i am checking a Flag if its TRUE then insert/update from ANY APPLICATION including SQL environments/tools. i can block Update but how i can achieve this for Insert. please help.

thanks in advance.
Re: how to prevent Insert in Before Insert trigger??? [message #251037 is a reply to message #251034] Thu, 12 July 2007 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes use a trigger and call raise_application_error when the condition is met.

Regards
Michel
Re: how to prevent Insert in Before Insert trigger??? [message #251048 is a reply to message #251034] Thu, 12 July 2007 05:10 Go to previous messageGo to next message
oralover
Messages: 97
Registered: January 2005
Member
many thanks for a very quick response and valuable time to help.
but i want a silent action, means the executer of Insert / Update should not aware about what happened, it succeed or failed.
Re: how to prevent Insert in Before Insert trigger??? [message #251051 is a reply to message #251048] Thu, 12 July 2007 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't have a silent fail.
A fail means an exception. If there is no exception then it does not fail.
So it is not silent unless you include your statement in a PL/SQL block with an exception clause to ignore your exception.

Regards
Michel
Re: how to prevent Insert in Before Insert trigger??? [message #251052 is a reply to message #251034] Thu, 12 July 2007 05:22 Go to previous messageGo to next message
oralover
Messages: 97
Registered: January 2005
Member
If i ignore the Exception, will it insert / update or not??
can u please give an example with many thanks.
Re: how to prevent Insert in Before Insert trigger??? [message #251054 is a reply to message #251052] Thu, 12 July 2007 05:24 Go to previous messageGo to next message
Maaher
Messages: 7054
Registered: December 2001
Senior Member
This might be interesting. Personally, I don't like the idea of hiding exceptions.

MHE
Re: how to prevent Insert in Before Insert trigger??? [message #251055 is a reply to message #251034] Thu, 12 July 2007 05:43 Go to previous messageGo to next message
oralover
Messages: 97
Registered: January 2005
Member
thanks Maaher you are respectable person here, used When Others but i think i have no idea to hide Error Message.
this is only a Security trick to make Fool if any one want to make something wrong.

[Updated on: Thu, 12 July 2007 05:45]

Report message to a moderator

Re: how to prevent Insert in Before Insert trigger??? [message #251064 is a reply to message #251052] Thu, 12 July 2007 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If i ignore the Exception, will it insert / update or not??

Not:
SQL> create table t (id integer);

Table created.

SQL> create or replace trigger my_trig before insert on t
  2  begin
  3    raise_application_error(-20000,'Dont do that');
  4  end;
  5  /

Trigger created.

SQL> declare
  2    myexception exception;
  3    pragma exception_init(myexception, -20000);
  4  begin 
  5    insert into t values (0);
  6  exception
  7    when myexception then null;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select * from t;

no rows selected

Regards
Michel
Re: how to prevent Insert in Before Insert trigger??? [message #251067 is a reply to message #251034] Thu, 12 July 2007 06:18 Go to previous messageGo to next message
oralover
Messages: 97
Registered: January 2005
Member
yeah, very nice,
huge thanks, appreciate your struggle to educate me.
but please inform, if a person using SQL*Plus environment or TOAD like utility to insert manualy, how it will prevent insert and not display any error message??? because here you is the solution to prevent these from FORM's like interface.

[Updated on: Thu, 12 July 2007 06:25]

Report message to a moderator

Re: how to prevent Insert in Before Insert trigger??? [message #251073 is a reply to message #251067] Thu, 12 July 2007 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I am using SQL*Plus and only SQL*Plus.

Regards
Michel
Re: how to prevent Insert in Before Insert trigger??? [message #251079 is a reply to message #251034] Thu, 12 July 2007 06:32 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
A trigger is on the table, it doesn't matter what front end tries to insert the record, the trigger will fire.
Re: how to prevent Insert in Before Insert trigger??? [message #251085 is a reply to message #251034] Thu, 12 July 2007 06:40 Go to previous messageGo to next message
oralover
Messages: 97
Registered: January 2005
Member
very sorry, i failed to convey...
i mean that if a someone is using as below:
Insert Into t
Values (1);

then will it block this insert??

for some Security i want to prevent a wrong guy to insert / update in that table (from any interface) but do not mention him/her that the operation was blocked, and do not block the data from a right person.

like this???
CREATE OR REPLACE TRIGGER EMP_TRIG
  BEFORE INSERT OR UPDATE ON TSDETL 
    REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
      WHEN ((NEW.empno > 99))
DECLARE
  incorrect EXCEPTION;
  aa boolean;
BEGIN
  If :NEW.empno > 99 Then
     :NEW.sal := :NEW.sal + (:NEW.sal*5)/100;
  Else
     raise incorrect;
  End If;
Exception
  When incorrect Then
       raise_application_error(-20101,'Prevent it...');
END;


this is not correct as its inserting rows
once again, i appologize Michel.

[Updated on: Thu, 12 July 2007 06:55]

Report message to a moderator

Re: how to prevent Insert in Before Insert trigger??? [message #251101 is a reply to message #251085] Thu, 12 July 2007 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
WHEN ((NEW.empno > 99))

You don't execute the trigger is empno <= 99, so you never raise the exception.

Btw, why 2 levels of parenthesis and not only one?

Regards
Michel

Re: how to prevent Insert in Before Insert trigger??? [message #251117 is a reply to message #251034] Thu, 12 July 2007 07:28 Go to previous messageGo to next message
oralover
Messages: 97
Registered: January 2005
Member
very well noticed, and sorry its my mistake but it compiled without any error... anyway now i am going to implement another solution which is, if condition is FALSE it will insert into another dummy table so it will not display any error message, and it will track the wrong guy/user also.

i much much thankful for the great efforts from you guys, i learned alot from very helpful and really professional people here.
Re: how to prevent Insert in Before Insert trigger??? [message #251122 is a reply to message #251117] Thu, 12 July 2007 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if condition is FALSE it will insert into another dummy table

This will not prevent you from inserting into the original table.

Regards
Michel
Re: how to prevent Insert in Before Insert trigger??? [message #251133 is a reply to message #251034] Thu, 12 July 2007 07:50 Go to previous message
oralover
Messages: 97
Registered: January 2005
Member
you are right Maaher, i checked it. thanks.
Previous Topic: max extents
Next Topic: delete without logging
Goto Forum:
  


Current Time: Thu Oct 23 03:10:53 CDT 2014

Total time taken to generate the page: 0.09862 seconds