xbox360dp
asked on
Delete all rows of data BEFORE insert on table
Gurus,
What is the best method to delete existing rows from a table before insert?
This will certainly cause mutating errors! What is the next best option?
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;This will certainly cause mutating errors! What is the next best option?
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I assume this is related to our previous question:
https://www.experts-exchange.com/questions/28659368/Use-of-Cursor-in-Trigger-causing-too-many-row-inserts.html
In that question you aren't trying to delete from the same table that is firing the trigger.
I'm a little confused?
https://www.experts-exchange.com/questions/28659368/Use-of-Cursor-in-Trigger-causing-too-many-row-inserts.html
In that question you aren't trying to delete from the same table that is firing the trigger.
I'm a little confused?
Yes, trigger is one option to do delete before insert.
You can also use "truncate table" to delete all records.
You can also use "truncate table" to delete all records.
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
you don't need a table for that
you can use a constant .. or better a variable ... or multiple variables
ASKER
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?
The question is what is the best approach if I want to truncate the table before every insert?
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';
>>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';
ASKER
execute immediate 'truncate table table_name'; doesn't work in the trigger. I receive the following error ...
ORA-04092: cannot COMMIT in a trigger
ORA-04092: cannot COMMIT in a trigger
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I use global temp tables the data is deleted before being used in update statement I need.
>>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"?
When you created it did you use "on commit preserve rows"?
ASKER
When I use global temp tables the data is deleted before being used in update statement I need.
>>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.
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.
ASKER
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?
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?
>>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.
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.
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.