PL/SQL trigger not working

Question

I've created a table like this:

CREATE TABLE table_name (
name     VARCHAR2(20)
);

After that, I've created a trigger, which before every insert, has to write the users name:

SET SERVEROUTPUT ON;

CREATE OR REPLACE TRIGGER bi_trigger
BEFORE INSERT ON table_name
FOR EACH ROW
ENABLE
DECLARE
v_user      VARCHAR2(20);
BEGIN
SELECT user INTO v_user FROM dual;
DBMS_OUTPUT.PUT_LINE('You inserted a row, ' || v_user);
END;

But, after inserting something into my table, the requested line with the user's name doesn't appear. What am I missing?

INSERT INTO table_name VALUES('qwerty');

Not sure what you are expecting. Are you saying the new row does not appear even after COMMIT? You won't see the dbms_output as the trigger exists outside of your session. Also v_user := USER; would replace the select from dual. Please elaborate on your expectations. — Stilgar
– Stilgar, Commented Nov 26, 2017 at 15:44
The rows are inserted. The problem is that I don't see the dbms_output. I've edited my question. — Fogarasi Norbert
– Fogarasi Norbert, Commented Nov 26, 2017 at 15:47
@FogarasiNorbert there seems no problem, except you didn't put a slash (/) after your trigger creation statement, as @Dave Costa says. As long as you stay on this session no problem may occur to see the message. — Barbaros Özhan
– Barbaros Özhan, Commented Nov 26, 2017 at 18:11

Dave Costa · Accepted Answer · 2017-11-26 21:50:03Z

2

I see two potential problems.

One is that it's not clear if you're actually creating the trigger. In the sample code you've shown, you need a forward slash (/) following the trigger text to actually execute the CREATE statement. You should see "Trigger created" if it is created successfully.

The other is that you are enabling SERVEROUTPUT prior to creating the trigger, which is irrelevant. You need to enable it in the session where the INSERT statement is executed. (If you are executing the INSERT in the same session as the CREATE, this is not the problem.)

(The other answer implies you aren't seeing the output because the trigger is executed outside your session. This is incorrect.)

edited Nov 26, 2017 at 21:50

answered Nov 26, 2017 at 17:05

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

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

5 Comments

Barbaros Özhan Over a year ago

there's no problem with enabling SERVEROUTPUT prior to creating the trigger.

Dave Costa Over a year ago

I'm not saying that's a problem. I'm saying if the INSERT is executed in another session, SERVEROUTPUT needs to be enabled in that session in order to see the output. Edited to clarify, I hope.

Fogarasi Norbert Over a year ago

@Dave Costa Where sould I put the forward slash? After the Enable keyword?

Dave Costa Over a year ago

@FogarasiNorbert -- on the line following END;

Dave Costa Over a year ago

See also stackoverflow.com/questions/8406835/…

Stilgar · Accepted Answer · 2017-11-26 15:57:07Z

1

You won't see the dbms_output because the trigger exists outside your local session, not connected. If you just want confirmation your trigger is firing correctly, try adding a column to the table such as created_by VARCHAR2(0032).

Replace your trigger body with

    :NEW.created_by := USER;

That will tell you if the trigger is working.

answered Nov 26, 2017 at 15:57

Stilgar

4612 silver badges6 bronze badges

2 Comments

Stilgar Over a year ago

Ah, you would also need to adjust your insert statement to reference column "name".

Stilgar Over a year ago

Turns out that you CAN retrieve the dbms_output but you need to be using a PL/SQL block to do that. Refer to: docs.oracle.com/cd/B10501_01/appdev.920/a96612/d_output.htm

Collectives™ on Stack Overflow

PL/SQL trigger not working

2 Answers 2

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related