Delete all rows of data BEFORE insert on table

Gurus,

What is the best method to delete existing rows from a table before insert?

create or replace
trigger delete_all_rows_from_table before
insert on table1 for each row
begin
delete from table1;
end;

Open in new window


This will certainly cause mutating errors! What is the next best option?
xbox360dpAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the question would be the process flow on such a requirement.
maybe you want a TEMPORARY table instead?
0
 
slightwv (䄆 Netminder) Commented:
I agree with GLOBAL TEMPORARY TABLES.

I wanted to point out the flaw in your design:
The trigger is FOR EACH ROW, so for every row you insert, you want to delete the previous row?

There would never be a row except for the previous one using that design.
0
 
slightwv (䄆 Netminder) Commented:
I assume this is related to our previous question:
http://www.experts-exchange.com/Database/Oracle/Q_28659368.html

In that question you aren't trying to delete from the same table that is firing the trigger.

I'm a little confused?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
HuaMin ChenSystem AnalystCommented:
Yes, trigger is one option to do delete before insert.
You can also use "truncate table" to delete all records.
0
 
Geert GOracle dbaCommented:
that looks like you want to use a constant ... or better a variable ... or multiple variables
you don't need a table for that

you can use a constant .. or better a variable ... or multiple variables
0
 
xbox360dpAuthor Commented:
GLOBAL TEMPORARY TABLES won't work in my case. However ... truncating the table would.

The question is what is the best approach if I want to truncate the table before every insert?
0
 
slightwv (䄆 Netminder) Commented:
I'm basing these replies from your other related question.

>>GLOBAL TEMPORARY TABLES won't work in my case

Why not?

>>However ... truncating the table would.

I don't think this is what you need.  Do you really want to truncate a temp table on every row that fires the trigger?

As far as the approach:
execute immediate 'truncate table table_name';
0
 
xbox360dpAuthor Commented:
execute immediate 'truncate table table_name'; doesn't work in the trigger. I receive the following error ...

ORA-04092: cannot COMMIT in a trigger
0
 
slightwv (䄆 Netminder) Commented:
You didn't post why global temp tables won't work.

The try delete:
execute immediate 'delete from table_name';


Still not sure you want or need this but I'll post in the other question.
0
 
xbox360dpAuthor Commented:
When I use global temp tables the data is deleted before being used in update statement I need.
0
 
slightwv (䄆 Netminder) Commented:
>>When I use global temp tables the data is deleted before being used in update statement I need.

When you created it did you use "on commit preserve rows"?
0
 
xbox360dpAuthor Commented:
When I use global temp tables the data is deleted before being used in update statement I need.
0
 
slightwv (䄆 Netminder) Commented:
>>When I use global temp tables the data is deleted before being used in update statement I need.

You posted this twice.
How did you create the global temporary table?
Do you still have the delete inside the trigger?  If so, don't do this.
0
 
xbox360dpAuthor Commented:
Slightwv,

I've never used global temporary tables but how does "on commit preserve rows" help me if I need the data deleted before the next insert?
0
 
slightwv (䄆 Netminder) Commented:
>>help me if I need the data deleted before the next insert?

Is the next insert in the same session?  If so, then it may not work.

You might need an after insert table level trigger.

That said:
I'm working on this from your other question.  I would rather continue over there since I don't think this question will be relevant once we answer that question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.