Trigger problems

[marcio.68]

I have to trigger in the table the one that brings up to date two other tables (B and C), when I give error of constraint in one of brought up to date tables (B or C), the table and the one that error gave suffers ROLLBACK, however to another one not. How to proceed?

[balajiyes]

All I can understand is... ROLLBACK.

try increasing the space of ur ROLLBACK.

[DaPi]

Is this what you mean?

1) Update to A triggers updates to B and C.
2) Update to C fails due to constraint error.
3) Updates to A and C are rolled back, but update to B is not rolled back.

If so, it sounds like you have a commit in the trigger - something you should normally NOT do.

[slimdave]

Originally posted by DaPi
Is this what you mean?

1) Update to A triggers updates to B and C.
2) Update to C fails due to constraint error.
3) Updates to A and C are rolled back, but update to B is not rolled back.

If so, it sounds like you have a commit in the trigger - something you should normally NOT do.

I don't think that commit in trigger is allowed.

create table my_table (col1 number);

create or replace trigger my_trigger before insert on my_table
for each row
begin null;
commit;
end;
/

insert into my_table values (1)
            *
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "KEYSTONE_STAGE.MY_TRIGGER", line 2
ORA-04088: error during execution of trigger 'KEYSTONE_STAGE.MY_TRIGGER'

[DaPi]

Originally posted by slimdave
I don't think that commit in trigger is allowed.

Ooops - I only tried compiling it
Of course, it can only be trapped at execution 'coz the commit could be in a proc called from the trigger. (I assume we're not talking about autonomous transactions . . . .)

[marcio.68]

In trigger three functions are executed, however in none of them it has commit.
Tables B and C are in another bank, this make some difference?

[balajiyes]

You CANNOT give commit or rollback within a FUNCTION()

[marcio.68]

Do NOT have commit or rollback in this functions.

[balajiyes]

Mr. marcio.68