Strange issue with trigger execution in oracle

Question

This is a pretty straightforward trigger which is executing but is not printing the simple message:

CREATE OR REPLACE TRIGGER t_emp
AFTER INSERT OR DELETE OR UPDATE ON employee
FOR EACH ROW
ENABLE
DECLARE
    v_user VARCHAR2(20);
BEGIN
    SELECT user INTO v_user FROM DUAL;
    IF INSERTING THEN
        DBMS_OUTPUT.PUT_LINE('One row inserted by ' || v_user);
    ELSIF DELETING THEN
        DBMS_OUTPUT.PUT_LINE('One row deleted by ' || v_user);
    ELSIF UPDATING THEN 
        DBMS_OUTPUT.PUT_LINE('One row updated by ' || v_user);
    END IF;
END;
/

Table: 
CREATE TABLE employee
    (
        s_name varchar2(30)
    );

Simple test case:

INSERT INTO employee
VALUES('Kafka');

However, yields no message although it gets executed. Now here is where it gets interesting. If I run a testing anonymous block like

BEGIN
    DBMS_OUTPUT.PUT_LINE('One row inserted by ' );
end;
/

I get the first printout of the trigger(which was supposed to be printed when I executed the DML statement) followed by the current message ("One row inserted by"). I have tested other pl/sql blocks and they execute fine. This seems to be a problem only with triggers (I also tried a couple of others). What could be the issue?
Thanks

Works for me. Is this an issue with the trigger or with dbms_output? I see a lot of posts along the lines of Problem with [some really specific scenario] which boil down to dbms_output settings. By the way you don't need to select from dual - PL/SQL has a handy assignment operator, so you could just declare k_user constant user_users.username%type := user; — William Robertson
– William Robertson, Commented May 22, 2018 at 18:50

Lukasz Szozda · Accepted Answer · 2018-05-22 17:10:13Z

1

Printing from trigger is poor idea. Anyway you could use:

SET SERVEROUTPUT ON;
INSERT INTO employee(s_name) VALUES('Kafka');
COMMIT;

edited May 22, 2018 at 17:10

answered May 22, 2018 at 17:02

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bendemann Over a year ago

Thanks, but why do I need to use commit in this case? I am not using it otherwise -I mean of course commit is important but for my cheap student projects and stuffs not so much-.

APC Over a year ago

@Bendemann - even your "cheap student projects" must serve some kind of purpose and so ought to be worth persisting. Perhaps you are relying on client functionality to implement AUTOCOMMIT for you. If so that is a very bad habit which you should shake now, because it will bite you if you ever execute DML In Real Life.

William Robertson Over a year ago

That said, a commit isn't needed to test this trigger.

Collectives™ on Stack Overflow

Strange issue with trigger execution in oracle

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related