DBMS_OUTPUT.PUT_LINE not showing message to user

Question

I am trying to create a trigger that would check for a null value in column namesdreservation_status from my table passenger and set it to a default value if it's null and show the user a message of it being set to default.

The trigger is created and works fine for setting the default but no message is shown to the user.

create or replace trigger mytrigger
    before insert or update on passenger
    for each row
    when (new.reservation_status IS NULL)
begin
    IF :new.reservation_status IS NULL THEN
        :new.reservation_status := 'not reserved';
        dbms_output.put_line('reservation status invalid, set to default');
    end IF;
end mytrigger
/

The value gets changed to 'not reserved' if its null but the message 'reservation status invalid, set to default' isn't displayed. Help.

are you running from sqlplus? You need to execute "set serveroutput on" — OldProgrammer
– OldProgrammer, Commented Nov 7, 2019 at 13:22
yes, i am using sqlplus. I did set serveroutput to on outside the trigger it did not do anything. — Gulsicka
– Gulsicka, Commented Nov 7, 2019 at 13:26

thatjeffsmith · Accepted Answer · 2019-11-07 14:31:40Z

2

Trigger's don't do anything until they're actually triggered - in this case, on an INSERT.

clear screen
set serveroutput on

drop table passenger;
create table passenger (id integer, reservation_status varchar2(25));

create or replace trigger mytrigger
    before insert or update on passenger
    for each row
    when (new.reservation_status IS NULL)
begin
    IF :new.reservation_status IS NULL THEN
        :new.reservation_status := 'not reserved';
        dbms_output.put_line('reservation status invalid, set to default');
    end IF;
end mytrigger;
/

insert into passenger values (1, null);
commit;

If I run this in SQLPlus, SQLcl, or SQL Developer -

And if we check the table, you can see our trigger, 'worked.'

answered Nov 7, 2019 at 14:31

thatjeffsmith

22.7k6 gold badges44 silver badges139 bronze badges

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

Collectives™ on Stack Overflow

DBMS_OUTPUT.PUT_LINE not showing message to user

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