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, Nov 07 '19 at 13:22
yes, i am using sqlplus. I did set serveroutput to on outside the trigger it did not do anything. — Gulsicka, Nov 07 '19 at 13:26

score 2 · Accepted Answer · answered Nov 07 '19 at 14:31

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.'

DBMS_OUTPUT.PUT_LINE not showing message to user

1 Answers1