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, Nov 26 '17 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, Nov 26 '17 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, Nov 26 '17 at 18:11

Dave Costa · Accepted Answer · 2017-11-26T21:50:03.970

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 '17 at 21:50

answered Nov 26 '17 at 17:05

Dave Costa

47,262
8
56
72

there's no problem with enabling SERVEROUTPUT prior to creating the trigger. – Barbaros Özhan Nov 26 '17 at 18:08
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. – Dave Costa Nov 26 '17 at 21:49
@Dave Costa Where sould I put the forward slash? After the `Enable` keyword? – Fogarasi Norbert Nov 26 '17 at 21:53
1

@FogarasiNorbert -- on the line following `END;` – Dave Costa Nov 27 '17 at 03:42
1

See also https://stackoverflow.com/questions/8406835/oracle-sql-plus-how-to-end-command-in-sql-file/8406998#8406998 – Dave Costa Nov 27 '17 at 03:43

score 1 · Answer 2 · answered Nov 26 '17 at 15:57

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 '17 at 15:57

Stilgar

441
2
6

Ah, you would also need to adjust your insert statement to reference column "name". – Stilgar Nov 26 '17 at 16:00
1

Turns out that you CAN retrieve the dbms_output but you need to be using a PL/SQL block to do that. Refer to: https://docs.oracle.com/cd/B10501_01/appdev.920/a96612/d_output.htm – Stilgar Nov 26 '17 at 16:06

PL/SQL trigger not working

2 Answers2