Oracle Sql trigger not working

Question

I have a very simple trigger, that prints out the username and date when a new row is inserted into the users table. But after it successfully compiled, the trigger didn't get triggered (there was no output in the dbms window). So, I simplified my code until I got this:

CREATE OR REPLACE TRIGGER logger
AFTER INSERT ON USERS
BEGIN
   DBMS_OUTPUT.PUT_LINE('User added with name:');
END;

If I run the code in the SQL worksheet (from BEGIN to END), I can see the output, but not when I try to use the trigger. Where is the problem?

From my point of view there is no point to use a trigger to print something when it’s triggered. To do something more feasible, why dont you try to insert something in some table and you see if it works? — James
– James, Commented May 5, 2018 at 11:13
Your problem is that DBMS_OUTPUT.PUT_LINE is aimed to write to console. So, you need to have the trigger making some change that could be checked without using the console. You can, for instance, send an e-mail to yourself (just to test that the trigger is fired, not as the normal functionality of the trigger). — FDavidov
– FDavidov, Commented May 5, 2018 at 11:34
I completely agree with @Jaime Drq and @FDavidov. Besides, if you really want to see on the console, then you need to issue set serveroutput on command without need of begin .. end unless you call from an external application. — Barbaros Özhan
– Barbaros Özhan, Commented May 5, 2018 at 11:52
I know, that there is no point of the code, this is an assignment for my class. The task is to create any trigger, and I choose to print the name and date of the registration. — johnscapw
– johnscapw, Commented May 5, 2018 at 12:45

eaolson · Accepted Answer · 2018-05-05 15:44:38Z

1

There are two options, one is that the trigger is not firing, the other is that it is, but you're not seeing the output.

Assuming you're working in a console program like sqlplus, make sure to do SET SERVEROUTPUT ON before inserting your row.
If that doesn't fix it, then make sure the trigger is firing. Try creating a simple table:

CREATE TABLE logtable ( msg VARCHAR2(30 CHAR));

Next, add this to your trigger,

INSERT INTO logtable( msg ) VALUES ( 'trigger fired' );

Then, try your insert again.

answered May 5, 2018 at 15:44

eaolson

15.2k7 gold badges46 silver badges62 bronze badges

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

Collectives™ on Stack Overflow

Oracle Sql trigger not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related