Re: Truncate table and Triggers

If you are running 8.1.6, you could create a 'before truncate' trigger;

create or replace trigger jpl_b_trunc
before truncate
on jpl.schema
begin

    raise_application_error(-20001,'Not allowed to truncate'); end;
/

You could also enhance this trigger to test the name of the object being truncated and be a little more selective.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Nicolas Bronke wrote in message <93enuo$hbk$04$1_at_news.t-online.com>...


>> >I placed a delete trigger on a table to unpermit delete operations under

 a


>> >special condition.

>> >

>> >Now I fpund out, that truncate table ommit this rules. Everything is

 erased


>> >nevertheless from the rules.

>> >

>> >Perhaps it is logically, but how can I avoid this situation?

>> >

>> The Truncate command is what is known as a DDL (Data Definition

>> Language)  not a DML (Data Maniplulation Language) command.  If I am

>> not mistaken, DDL does not fire ANY triggers where DML will.  Like the

>> other gentleman said, it is a privlege issue.  The owner of the table

>> can do what they want with it, but others have to be given permisions.

>> If you don't give others the permission to trucate, drop or delete

>> from a table, they cannot.

>

>Thank you. That makes more clear.

>

>Regards

>Nicolas

>

>