Before Delete Trigger

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc

Poston Tue, 03 Jan 2006 2:02 pm

Good afternoon folks.

I would like to clarify a doubt.

Would I create a before delete trigger, for example, and put a clause and, if it is true, do not perform the delete?
For example: before delete Trigger, but if a = 1, it does not raise?
This would have q come from application?

Grateful, cheezburger network:-
Location: Araraquara - SP

Poston Mon, 09 Jan 2006 7:56 pm

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

For example the trigger you could:
Code: Select all
ops$marcio@LNX10GR2> create table t ( x int );

Table created.

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> 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'
Location: São Paulo

Poston Tue, 10 Jan 2006 2:08 pm

Another way, which perhaps does not apply to the case of the cheezburger network, is to create a VIEW and apply a trigger INSTEAD OF her.

Then, within that trigger, you test the condition and applies whether or not the DELETE on the original table.

(of course, in this case it is not guaranteed that the user apply a delete directly in the table) ... IE doesn't do a lot ...
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?

Poston Mon, 19 Jun 2006 2:07 pm

How am I supposed to do with comparing fields between two tables?
Location: sp

Poston Mon, 19 Jun 2006 2:28 pm

Talking to compare a field to another (from another table) within a trigger before delete?

All you have to do the select desired within the trigger.
Is this? If not, explain better what you want to do.
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?

  • See also
    Last Post

Return to PL/SQL

Who is online

Users browsing this forum: No registered users and 12 guests