Trigger Before Delete

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP

Good afternoon staff.

I would like to take a doubt.

Would you like to create a before trigger delete, for example, and put a clause and if it is true, do not run the delete?
For example:

Before delete trigger, but if A = 1, does it not shoot?
Would that have to come from the application?

grateful,

Trevisolli
: -O
mportes
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 13
Joined: Tue, 01 Nov 2005 11:53 pm
Location: São Paulo
Contact:

You can create this type of rule on trigger yes (bad idea). In my opinion the right place for this is in the application, in logic.

For example of trigger you could:

Select all

 
ops$marcio@LNX10GR2> create table t ( x int ); 
 
Table created. 
 
ops$marcio@LNX10GR2> 
ops$marcio@LNX10GR2> create or replace trigger t_bi_fer 
  2  before insert on t for each row 
  3  begin 
  4          if ( :new.x = 1 ) then 
  5             raise_application_error(-20001, 'Aqui não!'); 
  6          end if; 
  7  end; 
  8  / 
 
Trigger created. 
 
ops$marcio@LNX10GR2> show error 
No errors. 
ops$marcio@LNX10GR2> 
ops$marcio@LNX10GR2> insert into t values ( 0 ); 
 
1 row created. 
 
ops$marcio@LNX10GR2> insert into t values ( 1 ); 
insert into t values ( 1 ) 
            * 
ERROR at line 1: 
ORA-20001: Aqui não! 
ORA-06512: at "OPS$MARCIO.T_BI_FER", line 3 
ORA-04088: error during execution of trigger 'OPS$MARCIO.T_BI_FER' 
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Another way, which may not apply to the Trevisolli case, is to create a view and apply a trigger instead of it.

Hence, within this trigger, you test the condition and apply or not the delete in the original table ...

(of course, in this case it is not Guaranteed the fact that the user apply a delete directly to the table) ... that is, it is not for much ...
goiaba3d
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 1
Joined: Mon, 19 Jun 2006 2:00 pm
Location: sp

How would I compare fields between two tables ??
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Are you talking about comparing a field with another (from another table) inside a trigger before delete?

Just do the desired SELECT within the trigger.
Is that it? If it is not, explain better what you want to do.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 19 guests