CODEWITHSUNDEEP
sqloracle-databasetriggers
Belitho Xavier
Belitho Xavier

Reputation: 139

Does anyone know why this trigger is not working?

create or replace NONEDITIONABLE TRIGGER SetNapomena
BEFORE INSERT
ON stavkafakture
FOR EACH ROW
    DECLARE
        V_napomena VARCHAR2(20);
BEGIN
    EXECUTE IMMEDIATE 'ALTER TRIGGER zabranjeno DISABLE';

    SELECT napomena INTO V_napomena
    FROM faktura
    WHERE brojfakture =:NEW.brojfakture;

    :NEW.napomena := V_napomena;

    EXECUTE IMMEDIATE 'ALTER TRIGGER zabranjeno ENABLE';
END;

When I insert into a table (in this case stavkafakture) I get this error:

Cannot commit in a trigger

I did some research and added Pragma Autonomous Transaction and commit, but after adding them I try to insert a row in the table it loads forever and never works. It stays in load forever.

Does anyone know why?

Upvotes: 0

Views: 209

Answers (1)

SuperPoney
SuperPoney

Reputation: 660

In Oracle DDL statement like CREATE, ALTER, DROP generate an explicit commit.

IE if you issue this kind of statement this does not requires a COMMIT nor a ROLLBACK :

CREATE TABLE MyTable(MyField NUMBER);

This will create the MyTable table and cannot be ROLLBACK. This is a limitation of Oracle that other RDBMS like PostreSQL doesn't have.

Upvotes: 1

Related Questions