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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

HuaMin ChenProblem resolverCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.