1

I created a trigger that does: When removing a table value, it performs an update on the same table as the event occurs. That means I need a temporary table to perform this trigger?

Error in SQL Developer (Oracle):

Cause: A trigger (or a user defined plsql function That Is referenced in this statement) attempted to look at (or modify) a table que was in the middle of being modified by the statement Which fired it.

Action: Rewrite the trigger (or function) so it does not read que table.

create or replace
TRIGGER "trigger_name"
AFTER DELETE ON table1 FOR EACH ROW

BEGIN
 MERGE INTO table1 t
 USING ( SELECT rowid rid, row_number() OVER (PARTITION BY column_pearson order by column_age) rn FROM table1) u
 ON ( t.rowid = u.rid )
 WHEN MATCHED THEN UPDATE SET t.column2 = u.rn;

 END;

-- with compound trigger -same error

create or replace
trigger "name_trigger"
for delete on table1

COMPOUND TRIGGER

AFTER STATEMENT IS
    BEGIN
    MERGE INTO table1
    USING ( SELECT rowid rid, row_number() OVER (PARTITION BY column_pearson order by column_age rn FROM table1) u
    ON ( t.rowid = u.rid )
    WHEN MATCHED THEN UPDATE SET t.column2 = u.rn;
    END AFTER STATEMENT
END name_trigger

http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm

Paulo Victor
  • 327
  • 1
  • 4
  • 12
  • Since you are not referencing `:old` or `:new` values, all you need to do is remove the `FOR EACH ROW` and the error will not be raised. – Tony Andrews Sep 11 '15 at 16:57
  • Thanks!, Now before your response displays the message: -identifier 't.column-pearson must' be declared -compilation unit analysis terminated I'am try "...PARTITION BY t.column_pearson..." but don't work – Paulo Victor Sep 11 '15 at 17:20
  • Unless I'm reading this wrong it appears the MERGE statement in this trigger will update every row in TABLE1 whenever you perform a DELETE. Are you sure you want to do this? – Bob Jarvis - Слава Україні Sep 11 '15 at 17:21
  • Regarding `column_pearson` - is this perhaps declared in the database as `column_person'? Just a guess... – Bob Jarvis - Слава Україні Sep 11 '15 at 17:22
  • The delete event that calls the trigger and the update to be performed after the trigger of the call is on the same table, did not want to use another table, even if termporária for this. It could be done without the need of a temporary table? – Paulo Victor Sep 11 '15 at 17:26
  • @BobJarvis No, it was typo, sorry – Paulo Victor Sep 11 '15 at 17:28
  • You could use a compound trigger to accomplish this. [See my answer to this question](http://stackoverflow.com/questions/29489951/oracle-trigger-after-insert-or-delete). IMO putting a sequence number column on this table which needs to be reset every time you do a delete is not a good idea. First, it adds unneeded complexity. Second, it's a performance problem. Third, there's no guarantee that the COLUMN2 values will be consistent each time the rows are renumbered. If you want this for sorting purposes get a non-repeating value from a SEQUENCE, assign it to COLUMN2, and don't ever change it. – Bob Jarvis - Слава Україні Sep 11 '15 at 17:32
  • @BobJarvis I see what you mean, but this value that I want to order is not a table sequence and is not a primary key. It is a business need a conditioning groups according to determined by the PARTITION BY. Yes, the future will have performance issues. But can you explain more clearly its suggestion for solution because I am newbie. – Paulo Victor Sep 11 '15 at 17:52
  • I want shares for each line, so from what I researched here, the most interesting thing to do would be to use the compound trigger. I'm checking the syntax of this. – Paulo Victor Sep 11 '15 at 18:11
  • I removed the lines for each row and work the way queeu wanted, thank you! @TonyAndrews – Paulo Victor Sep 11 '15 at 19:37
  • @BobJarvis My thanks to you too , learned a lot from their tips today – Paulo Victor Sep 11 '15 at 19:38
  • I removed the command "FOR EACH ROW" and the trigger is execute a table without the mutation error, but the updates are not persisted in the database. So I tried using "PRAGMA AUTONOMOUS_TRANSACTION;" a hint of the Oracle community, but also did not appear effect, the Trigger is called, but the update of the table is not persisted in the database. How persist a result the trigger? – Paulo Victor Sep 14 '15 at 14:02
  • ERROR Execute Delete for starting trigger: SQL Error: ORA - 00060 *Cause: Transactions deadlocked one another while waiting for resources. *Action: Look at the trace file to see the transactions and resources involved. Retry if necessary. – Paulo Victor Sep 14 '15 at 17:59
  • The trigger could call a procedure that would contain the commit instruction. The procedure runs smoothly, but not eprsiste the update data. When I specify the commit command before the tag 'end' gives error ded lock from a line. Now how can I know if it commits will reference the changes to the trigger? I could spend a kind of resultSet in PL / SQL, how could I do this? – Paulo Victor Sep 14 '15 at 19:23

0 Answers0