Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2009
    Posts
    15

    Unanswered: Trigger with null fields

    Hi, i'm having a problem with a trigger that copies the information from a staging table to a final table.

    My trigger should delete if exists any record for that date, and insert into the final table.

    My problem is: when i have any null column, the trigger fails.
    the error is: ORA-00936 Missing expression.

    Both tables have nullable permitions to that fields...


    can anyone help on this?

    Code:
    CREATE OR REPLACE
    TRIGGER TRG_FARMACOVIGILANCIA 
    AFTER INSERT ON BSC_L_FARMACOVIGILANCIA 
    FOR EACH ROW 
    Declare 
     v_sql varchar2(5000);
     v_count number;
    
    BEGIN
     v_sql := 'select count(1) from bsc_f_FARMACOVIGILANCIA where data = '|| :NEW.DATA;
     execute immediate v_sql into v_count;
    
     if v_count <> 0 then
     v_sql := 'delete from bsc_f_FARMACOVIGILANCIA where data = '|| :NEW.DATA;
     execute immediate v_sql;
     end if;
    
     v_sql := 'insert into bsc_f_FARMACOVIGILANCIA values('||:NEW.ID_FARMACOVIGILANCIA||','||:new.OE||','||:new.oo||','||:new.direccao||','||:new.data||','||:new.N_NOTIF_GV_NGV_PS||','||:new.N_NOTIF_GV_NGV_IF||','||:new.N_MATER_EDUC_APROVADOS||','||:new.N_PROTOC_AN_RISCO||','||:new.N_REL_AN_RISCO||','||:new.N_NOTIF_GV_PS_ENCAM_RSP_PR||','||:new.N_NOT_ESP_RAM_GV_ENCAM_EMEA_PR||','||:new.N_TT_NOT_GV||','||:new.N_TT_ALT_TIPO_II_SEG||','||:new.N_TT_MED_SEG_URG||','||:new.N_ALT_TIPO_II_SEG_CONC_PR||','||:new.N_MED_SEG_URG_CONC_PR||','||:new.N_CIRC_INFORMATIVAS||','||:new.N_PUBLICACOES||','||:new.N_BOLETINS_FARMACOVIGIL||','||:new.DATA_CTRL||')';
     execute immediate v_sql;
    END;
    thanx,

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What is NULL? Is it ":NEW.DATA"? If so, perhaps you should first check whether it is NULL and create WHERE clause according to that fact. Because, you can't have
    Code:
    WHERE data = <null>
    , but
    Code:
    WHERE data IS NULL
    . Something like this:
    Code:
    if :new.data is null then
       v_sql := 'select ... where data is null';
    else
       v_sql := 'select ... where data = ' || :new.data
    end if;
    
    execute immediate v_sql into v_count;
    etc.
    If I'm wrong, I guess you'll say so.

  3. #3
    Join Date
    Dec 2009
    Posts
    15
    Littlefoot, thanx for your fast reply.
    But my problem isn't with the field data (date in portuguese), the data field can't be null, it has a value every time that i insert some data to the table, i'm having problem in all other fileds (that can be null).

    There is some restriction to the trigger or calling :new.field where field can't be null?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It is because you are (unnecessarily) constructing dynamic SQL that, if values are null, ends up looking like this:
    Code:
    delete from bsc_f_FARMACOVIGILANCIA where data =
    Code:
    insert into bsc_f_FARMACOVIGILANCIA values(,,,,,,)
    Why are you using dynamic SQL at all? You could just write:

    Code:
    CREATE OR REPLACE
    TRIGGER TRG_FARMACOVIGILANCIA 
    AFTER INSERT ON BSC_L_FARMACOVIGILANCIA 
    FOR EACH ROW 
    Declare 
     v_count number;
    
    BEGIN
     select count(1) into v_count from bsc_f_FARMACOVIGILANCIA where data = :NEW.DATA;
    
     if v_count <> 0 then
        delete from bsc_f_FARMACOVIGILANCIA where data = :NEW.DATA;
     end if;
    
     insert into bsc_f_FARMACOVIGILANCIA values(NEW.ID_FARMACOVIGILANCIA,:new.OE,:new.oo,:new.direccao,:new.data,:new.N_NOTIF_GV_NGV_PS,:new.N_NOTIF_GV_NGV_IF,:new.N_MATER_EDUC_APROVADOS,:new.N_PROTOC_AN_RISCO,:new.N_REL_AN_RISCO,:new.N_NOTIF_GV_PS_ENCAM_RSP_PR,:new.N_NOT_ESP_RAM_GV_ENCAM_EMEA_PR,:new.N_TT_NOT_GV,:new.N_TT_ALT_TIPO_II_SEG,:new.N_TT_MED_SEG_URG,:new.N_ALT_TIPO_II_SEG_CONC_PR,:new.N_MED_SEG_URG_CONC_PR,:new.N_CIRC_INFORMATIVAS,:new.N_PUBLICACOES,:new.N_BOLETINS_FARMACOVIGIL,:new.DATA_CTRL);
    END;
    Or even better/simpler:
    Code:
    CREATE OR REPLACE
    TRIGGER TRG_FARMACOVIGILANCIA 
    AFTER INSERT ON BSC_L_FARMACOVIGILANCIA 
    FOR EACH ROW 
    BEGIN
     delete from bsc_f_FARMACOVIGILANCIA where data = :NEW.DATA;
    
     insert into bsc_f_FARMACOVIGILANCIA values(NEW.ID_FARMACOVIGILANCIA,:new.OE,:new.oo,:new.direccao,:new.data,:new.N_NOTIF_GV_NGV_PS,:new.N_NOTIF_GV_NGV_IF,:new.N_MATER_EDUC_APROVADOS,:new.N_PROTOC_AN_RISCO,:new.N_REL_AN_RISCO,:new.N_NOTIF_GV_PS_ENCAM_RSP_PR,:new.N_NOT_ESP_RAM_GV_ENCAM_EMEA_PR,:new.N_TT_NOT_GV,:new.N_TT_ALT_TIPO_II_SEG,:new.N_TT_MED_SEG_URG,:new.N_ALT_TIPO_II_SEG_CONC_PR,:new.N_MED_SEG_URG_CONC_PR,:new.N_CIRC_INFORMATIVAS,:new.N_PUBLICACOES,:new.N_BOLETINS_FARMACOVIGIL,:new.DATA_CTRL);
    END;

  5. #5
    Join Date
    Dec 2009
    Posts
    15

    Thumbs up

    Quote Originally Posted by andrewst View Post
    It is because you are (unnecessarily) constructing dynamic SQL that, if values are null, ends up looking like this:
    Code:
    delete from bsc_f_FARMACOVIGILANCIA where data =
    Code:
    insert into bsc_f_FARMACOVIGILANCIA values(,,,,,,)
    Why are you using dynamic SQL at all? You could just write:

    Code:
    CREATE OR REPLACE
    TRIGGER TRG_FARMACOVIGILANCIA 
    AFTER INSERT ON BSC_L_FARMACOVIGILANCIA 
    FOR EACH ROW 
    Declare 
     v_count number;
    
    BEGIN
     select count(1) into v_count from bsc_f_FARMACOVIGILANCIA where data = :NEW.DATA;
    
     if v_count <> 0 then
        delete from bsc_f_FARMACOVIGILANCIA where data = :NEW.DATA;
     end if;
    
     insert into bsc_f_FARMACOVIGILANCIA values(NEW.ID_FARMACOVIGILANCIA,:new.OE,:new.oo,:new.direccao,:new.data,:new.N_NOTIF_GV_NGV_PS,:new.N_NOTIF_GV_NGV_IF,:new.N_MATER_EDUC_APROVADOS,:new.N_PROTOC_AN_RISCO,:new.N_REL_AN_RISCO,:new.N_NOTIF_GV_PS_ENCAM_RSP_PR,:new.N_NOT_ESP_RAM_GV_ENCAM_EMEA_PR,:new.N_TT_NOT_GV,:new.N_TT_ALT_TIPO_II_SEG,:new.N_TT_MED_SEG_URG,:new.N_ALT_TIPO_II_SEG_CONC_PR,:new.N_MED_SEG_URG_CONC_PR,:new.N_CIRC_INFORMATIVAS,:new.N_PUBLICACOES,:new.N_BOLETINS_FARMACOVIGIL,:new.DATA_CTRL);
    END;
    Or even better/simpler:
    Code:
    CREATE OR REPLACE
    TRIGGER TRG_FARMACOVIGILANCIA 
    AFTER INSERT ON BSC_L_FARMACOVIGILANCIA 
    FOR EACH ROW 
    BEGIN
     delete from bsc_f_FARMACOVIGILANCIA where data = :NEW.DATA;
    
     insert into bsc_f_FARMACOVIGILANCIA values(NEW.ID_FARMACOVIGILANCIA,:new.OE,:new.oo,:new.direccao,:new.data,:new.N_NOTIF_GV_NGV_PS,:new.N_NOTIF_GV_NGV_IF,:new.N_MATER_EDUC_APROVADOS,:new.N_PROTOC_AN_RISCO,:new.N_REL_AN_RISCO,:new.N_NOTIF_GV_PS_ENCAM_RSP_PR,:new.N_NOT_ESP_RAM_GV_ENCAM_EMEA_PR,:new.N_TT_NOT_GV,:new.N_TT_ALT_TIPO_II_SEG,:new.N_TT_MED_SEG_URG,:new.N_ALT_TIPO_II_SEG_CONC_PR,:new.N_MED_SEG_URG_CONC_PR,:new.N_CIRC_INFORMATIVAS,:new.N_PUBLICACOES,:new.N_BOLETINS_FARMACOVIGIL,:new.DATA_CTRL);
    END;
    Andrewst, Thank you so much!

    It worked perfectly.

    Thats the problem of coding too much PL/SQL dinamic... i was thinking to do a decode on the sqlldr to -1 and on the trigger do another decote to null, but your solution was perfect!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •