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

In article <cs7tth$28h$1_at_news.BelWue.DE>, Holger Baer says...
>
>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?

http://otn.oracle.com/pls/db92/db92.drilldown?remark=&word=ora_sql_txt&book=&preference=

ora_sql_TXT

        ^^^

it is documented in the application developers guide, alongside all of the event attribute functions (ora_* functions available in triggers)

>
>Thanks
>
>Holger

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation