OraFAQ Forum: SQL & PL/SQL » how to run delete trigger when truncating a table

Home » SQL & PL/SQL » SQL & PL/SQL » how to run delete trigger when truncating a table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to run delete trigger when truncating a table [message #28495]

Thu, 25 December 2003 20:45

Puneet sachar
Messages: 62
Registered: October 2003

Member

i have a delete trigger, i want to run it...
but i'm not deleting the table, i'm truncating it

How to run delete trigger when truncate a table

Report message to a moderator

Re: how to run delete trigger when truncating a table [message #28497 is a reply to message #28495]

Thu, 25 December 2003 21:56

Srinivas
Messages: 138
Registered: June 2000

Senior Member

CREATE TABLE x (a NUMBER)

CREATE TABLE y (b NUMBER)

Trigger on x if delete from x then truncate table y

CREATE OR REPLACE TRIGGER Example
BEFORE DELETE ON x
FOR EACH ROW
DECLARE
xyz VARCHAR2(100);
PRAGMA autonomous_transaction;
BEGIN
xyz:= 'truncate table y';
EXECUTE IMMEDIATE xyz;
END Example;
/

NOTE:- Truncate table will do a auto commit . you need to take care.

Delete from x;

Select * from y;

HTH
Srini

Report message to a moderator

Re: how to run delete trigger when truncating a table [message #28499 is a reply to message #28495]

Thu, 25 December 2003 22:54

Puneet sachar
Messages: 62
Registered: October 2003

Member

no Srinivas

what i'm loking is, suppose if i have delete trigger on table X and when i truncate table X i need to run that trigger.

Puneet

Report message to a moderator

Re: how to run delete trigger when truncating a table [message #28501 is a reply to message #28495]

Fri, 26 December 2003 01:01

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

mag@mutation_mutation > truncate table dept;
truncate table dept
               *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20111: truncate is not allowed .... table is protect by a trigger
ORA-06512: at line 7

mag@mutation_mutation > get trunc_trigger
  1  create or replace trigger trunc_trigger
  2  before ddl on schema
  3  declare
  4  v_user VARCHAR2(1000) := sys_context('USERENV', 'OS_USER');
  5  NO_PRIVS EXCEPTION;
  6  PRAGMA EXCEPTION_INIT(NO_PRIVS, -20111);
  7  BEGIN
  8  IF ora_sysevent IN('TRUNCATE') and LOWER(V_USER) IN( 'mag.s', 'mag', 'oracle') THEN
  9  RAISE_APPLICATION_ERROR(-20111, 'truncate is not allowed .... table is protect by a trigger');
 10  END IF;
 11* END trunc_trigger;
mag@mutation_mutation > /

Trigger created.

----------------------------------------------------------------------

this trigger fires only for this user.
if  user does the truncte on this table..it wont fire.
include or exclude the usernames ( in and not in clause in the trigger definition ) so u can cntrol the users.

----------------------------------------------------------------------

mag@mutation_mutation > connect dbadmin/xxxxxxx;
Connected.
mag@mutation_mutation > truncate table mag.dept;

Table truncated.

Report message to a moderator

Re: Re: how to run delete trigger when truncating a table [message #28539 is a reply to message #28501]

Tue, 30 December 2003 00:38

janno
Messages: 1
Registered: December 2003

Junior Member

Well, actually Oracle also provides
(cant say exactly from which version since)
it's specific truncate-triggers :

create or replace TRIGGER prevent_trunc
before truncate on schema
WHEN (ora_dict_obj_name='YOUR_TABLE_NAME' AND ora_dict_obj_TYPE='TABLE')

Begin

RAISE_APPLICATION_ERROR(-20111, ' Truncate is not allowed .... table is protected !');

END;

and or course, add your own (in aforementioned WHEN-section) restrictions when necessary

Report message to a moderator

Previous Topic:	subquery
Next Topic:	Primary keys versus Unique indexes

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 03:35:37 CST 2024