Re: Question: Use Trigger to Prevent Deletes with no Where Clause?

Thomas Kyte wrote:
>
> ops$tkyte_at_ORA9IR2> create table t ( x int );
>
> Table created.
>
> ops$tkyte_at_ORA9IR2> create or replace trigger t_trigger
> 2 before delete on t
> 3 declare
> 4 l_sql_text ora_name_list_t;
> 5 l_n number;
> 6 l_found boolean := false;
> 7 begin
> 8 l_n := ora_sql_txt(l_sql_text) ;
> 9 for i in 1 .. l_n
> 10 loop
> 11 l_found := upper(l_sql_text(i)) like '%WHERE%';
> 12 exit when l_found;
> 13 end loop;
> 14 if ( not l_found )
> 15 then
> 16 raise_application_error( -20000, 'you totally lose' );
> 17 end if;
> 18 end;
> 19 /
>
> Trigger created.
>

Nice trick - especially since ora_sql_text could not be found in the Dokumentation (using otn's own search utility for 10g).

Is there any documentation out that I missed?

Thanks

Holger Received on Fri Jan 14 2005 - 01:55:28 CST