how to use trigger in oracle

Question

I created a trigger but I don't know how to use it. can someone help me?

create or replace trigger user_id_trigger
              before insert on library_user
              for each row
              begin
                  if :new.user_id is null then
                      select USER_ID_O_SEQ.nextval into :new.user_id from library_user;
                 end if;
end;

create table LIBRARY_USER(      USER_ID number (20) primary key not null,
                                FIRST_NAME varchar (50) not null,
                                LAST_NAME varchar (50) not null,
                                USER_BDATE date not null,
                                USER_ADRESS varchar (200) not null,
                                USER_EMAIL varchar (50) not null,
                                USER_PHONE_NUMBER varchar (25),
                                USER_STATUS varchar (20) not null,
                                USERNAME varchar (50) not null,
                                PASSWORD varchar (50) not null);

Answer 1

Use dual in select rather than library:

CREATE OR REPLACE TRIGGER user_id_trigger
    BEFORE INSERT
    ON library_user
    FOR EACH ROW
BEGIN
    IF :new.user_id IS NULL
    THEN
        SELECT user_id_o_seq.NEXTVAL INTO :new.user_id FROM dual;
    END IF;
END;

I cant' remember which release allows you to skip the select (I think it was 11.2), but in more recent Oracle releases you can use:

CREATE OR REPLACE TRIGGER user_id_trigger
    BEFORE INSERT
    ON library_user
    FOR EACH ROW
BEGIN
    :new.user_id   := COALESCE( :new.user_id, user_id_o_seq.NEXTVAL );
END;