CODEWITHSUNDEEP
oracle-databasetriggersinsert
Gábor Varga
Gábor Varga

Reputation: 840

Oracle before insert all trigger

I want to create a trigger before when insert into more rows. Before inserting the new data, i want to delete the earlier version of those by an id.

e.g.:

CREATE OR REPLACE TRIGGER mytableTrigger
BEFORE INSERT ALL ON mytable
BEGIN
    DELETE FROM mytable WHERE column2 = fooId; --I want to get fooId in here.
END;

INSERT ALL
  INTO mytable (column1, column2, column3) VALUES (Seq.nextval(), fooId, 'val1.3')
  INTO mytable (column1, column2, column3) VALUES (Seq.nextval(), fooId, 'val2.3')
  INTO mytable (column1, column2, column3) VALUES (Seq.nextval(), fooId, 'val3.3')
SELECT * FROM dual;

If it would be a simple row-level trigger then I can get fooId with :new.fooId. But it isn't. So, can i get or give an id to INSERT ALL Trigger?

THX.

Upvotes: 2

Views: 1858

Answers (2)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

INSERT ALL is a special case of INSERT, it will fire standard BEFORE/AFTER INSERT triggers.

You would need ON EACH ROW triggers to access the values of the row you are inserting. (un)Fortunately, you can't use row triggers to query the table you are trying to modify: you will run into ORA-04091. See this recent question for why you should not use triggers if you want to do this kind of logic.

Use procedures. Easier to code, easier to maintain and debug. Triggers are the GOTO of database logic.

Here's a similar advice from Tom Kyte:

anyway, you can do too much work in triggers, this may well be that time -- there is nothing wrong with doing things in a more straightforward fashion (eg: using a stored procedure to implement your transaction) [...]
The logic is a whole lot more understandable that way (and maintainable and testable and everything)

With hindsight your case is even more complicated than what I thought at the beginning. Now my understanding of your requirement is: you want your statement to first remove all rows that will have the fooID identifier, then insert another set of rows. However, the number of rows could be different so a standard row-per-row approach won't work.

I'm now not even sure you could do this with triggers, even if you persisted with this approach !

Upvotes: 5

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

I don't know of any reliable relational database that will allow a table to be deleted or updated at the same time as it's being inserted into from the same transcriptional statement.
Yet this is what you want to do. It's called a mutating table.
I know that Oracle won't let this.
In your example when Inserting you want to insert some data, so there will be a recursive operation.
The solution wont be using a trigger pal.

Upvotes: 1

Related Questions