1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Trigger Code

Discussion in 'SQL PL/SQL' started by venu57, Mar 20, 2015.

  1. venu57

    venu57 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Bangalore
    we are loading data into ebc_xmldoc table through some procedure.
    while loading if any error occurs we are updating errmsg
    trigger should check based on err msg and poppulate the values.

    So here if traxtype is 'FS' then do we need to take the record count and insert that into another table

    yes, if you are trying to load the data with same xmldocid then counts will increase on the same record
    .
    wililf show you one example.

    so here there are 24 records.
    for all records trax type is FS.
    Ok
    and if you see there errmsg is null. that means
    total proc count = 24
    errcnt = 0
    fscnt = 24
    Suppose if the errmsg is not null then we have to substract the record count
    yes in that case if you got errmsg for 5 records
    total proc count = 24
    errcnt = 5
    fscnt = 19

    Please give me suggesion
     
  2. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    create or replace trigger ebc_xmldoc_count
    after insert on ebc_xmldoc
    --referencing REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
    --for each row
    as
    v_tot_cnt number;
    v_errcnt number;
    v_fscnt number;

    begin


    select count(1) into v_tot_cnt from ebc_xmldoc where traxtype ='FS';

    select count(1) into v_errcnt from ebc_xmldoc where traxtype ='FS' and errmsg is not null;

    select count(1) into v_fscnt from ebc_xmldoc where traxtype ='FS' and errmsg is null;



    insert into ebc_xmldoc_audit(tot_cnt,errcnt,fscnt)
    values (v_tot_cnt,v_errcnt,v_fscnt);

    end ebc_xmldoc_count;
     
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    To group using xmldocid, use the procedure below:

    create or replace trigger ebc_xmldoc_count
    after insert on ebc_xmldoc
    --referencing REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
    --for each row
    as
    v_tot_cnt number;
    v_errcnt number;
    v_fscnt number;

    begin


    select sum(count(1)) into v_tot_cnt from ebc_xmldoc where traxtype ='FS' group by xmldocid;

    select SUM(COUNT(1)) into V_ERRCNT from EBC_XMLDOC where TRAXTYPE ='FS' and ERRMSG is not null
    group by xmldocid;

    select SUM(COUNT(1)) into V_FSCNT from EBC_XMLDOC where TRAXTYPE ='FS' and ERRMSG is null
    group by xmldocid;


    insert into ebc_xmldoc_audit(tot_cnt,errcnt,fscnt)
    values (v_tot_cnt,v_errcnt,v_fscnt);

    end ebc_xmldoc_count;
     
  4. venu57

    venu57 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Bangalore
    Hi, I want before insert or update trigger.

    trigger on table ebc_xmldoc (summary table will be ebc_xmldocsumm)

    ebc_xmldoc strucure:

    Name Null Type
    ---------- -------- --------------
    XMLDOCID NOT NULL VARCHAR2(50)
    XMLRECNO NOT NULL NUMBER
    INSBY NOT NULL VARCHAR2(30)
    INSDT NOT NULL DATE
    UPDBY NOT NULL VARCHAR2(30)
    UPDDT NOT NULL DATE
    PROVCD NOT NULL CHAR(15)
    XMLDOCTYP NOT NULL VARCHAR2(15)
    XMLCONTENT NOT NULL CLOB
    PROCBY VARCHAR2(30)
    PROCDT DATE
    ERRMSG VARCHAR2(4000)
    TRXTYP VARCHAR2(3)
    REMARK VARCHAR2(2000)
    VAR1 VARCHAR2(100)
    VAR2 VARCHAR2(100)
    VAR3 VARCHAR2(100)

    ebc_xmldocsumm

    Name Null Type
    ---------- -------- --------------
    PROVCD NOT NULL CHAR(15)
    XMLDOCID NOT NULL VARCHAR2(50)
    XMLDOCTYP NOT NULL VARCHAR2(15)
    INSBY NOT NULL VARCHAR2(30)
    INSDT NOT NULL DATE
    UPDBY NOT NULL VARCHAR2(30)
    UPDDT NOT NULL DATE
    STATUS CHAR(1)
    FSCNT NUMBER(8)
    AMCNT NUMBER(8)
    CACNT NUMBER(8)
    OTHCNT NUMBER(8)
    TOTCNT NUMBER(8)
    PROCCNT NUMBER(8)
    ERRCNT NUMBER(8)
    LASTPROCBY VARCHAR2(60)
    LASTPROCDT DATE
    REM VARCHAR2(4000)
    CLOSEBY VARCHAR2(30)
    CLOSEDT DATE

    BEFORE INSERT OR UPDATE OR DELETE ON ebc_xmldoc
    IF inserting or updating
    IF trxtyp = FS add or minus ebc_xmldoc.FSCNT
    IF trxtyp = AM add or minus ebc_xmldoc.AMCNT
    IF trxtyp = CA add or minus ebc_xmldoc.CACNT
    ELSE add or minus ebc_xmldoc.OTHCNT

    EBC_XMLDOCSUMM. TOTCNT

    Ex : If you are inserting 8 records into table ebc_xmldoc with same xmldocid, in that
    2 record are with trxtype = FS (errmsg is not null for 1 record)
    3 record are with trxtype = AM (errmsg is not null for 2 record)
    1 record are with trxtype = CA (errmsg is not null for 1 record)
    2 record are with trxtype NOT IN (FS,AM,CA); (errmsg is not null for 1 record)

    So totcnt=8, FSCNT=2, AMCNT=3, CACNT =1, OTHCNT=2; PROCCNT = 3; ERRCNT = 5;
    if any case ebc_xmldoc.errmsg is not null then that record count will insert into errcnt.
     
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    create or replace trigger ebc_xmldoc_count
    BEFORE INSERT OR UPDATE ON ebc_xmldoc
    referencing REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
    for each row
    as

    begin

    update ebc_xmldocsumm set TOTCNT = TOTCNT+1
    where XMLDOCID=:new.XMLDOCID;

    IF :new.traxtype ='FS' then

    update ebc_xmldocsumm set FSCNT = FSCNT+1
    where XMLDOCID=:new.XMLDOCID;


    if :new.errmsg is notnull then

    update ebc_xmldocsumm set ERRCNT = ERRCNT+1
    where XMLDOCID=:new.XMLDOCID;


    end if;

    ELSIF :new.traxtype ='AM' then

    update ebc_xmldocsumm set AMCNT = AMCNT+1
    where XMLDOCID=:new.XMLDOCID;


    if :new.errmsg is notnull then

    update ebc_xmldocsumm set ERRCNT = ERRCNT+1
    where XMLDOCID=:new.XMLDOCID;


    end if;

    ELSIF :new.traxtype ='CA' then

    update ebc_xmldocsumm set CACNT = CACNT+1
    where XMLDOCID=:new.XMLDOCID;


    if :new.errmsg is notnull then

    update ebc_xmldocsumm set ERRCNT = ERRCNT+1
    where XMLDOCID=:new.XMLDOCID;

    end if;

    ELSE

    update ebc_xmldocsumm set OTHCNT = OTHCNT+1
    where XMLDOCID=:new.XMLDOCID;

    end if;

    end ebc_xmldoc_count;
     
  6. venu57

    venu57 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Bangalore
    Hi Vicky, Previously we don't have any data to update. WE have to insert the entries into summary table by taking the some column values from the ebc_xmldoc table.
     
  7. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    If we insert every new record into ebc_xmldocsumm table means., which record will you update for Count/?
     
  8. venu57

    venu57 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Bangalore
    Not every record needs to be inserted. Only based on xmldocid, we have to insert. suppose for docid we have 10 records then only one enrty wshould be insrted. see the following exampl.

    provcd xmldocid xmldocno insby insdt upby updt status FSCNT AMCNT CACNT OTHCNT TOTCNT PROCCNT ERRCNT
    ZZZ TESTING_DOC PMIREQ SCHEDULER 3/3/2015 6:21 SYSTEM 3/3/2015 9:47 3 2 2 1 8 3 5

    i want output like that.
     
  9. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Consider the corrections made while inserting.,


    create or replace trigger EBC_XMLDOC_COUNT
    before insert or update or delete on ebc_xmldoc
    REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
    for each row
    as
    v_XML_count number;
    begin

    case
    when INSERTING or UPDATING then

    select COUNT(1) into v_XML_count from EBC_XMLDOC
    where XMLDOCID=:new.XMLDOCID;

    if v_XML_count > 0 then

    update ebc_xmldocsumm set TOTCNT = TOTCNT+1
    where XMLDOCID=:new.XMLDOCID;

    IF :new.traxtype ='FS' then

    update ebc_xmldocsumm set FSCNT = FSCNT+1
    where XMLDOCID=:new.XMLDOCID;


    if :new.errmsg is notnull then

    update ebc_xmldocsumm set ERRCNT = ERRCNT+1
    where XMLDOCID=:new.XMLDOCID;


    end if;

    ELSIF :new.traxtype ='AM' then

    update ebc_xmldocsumm set AMCNT = AMCNT+1
    where XMLDOCID=:new.XMLDOCID;


    if :new.errmsg is notnull then

    update ebc_xmldocsumm set ERRCNT = ERRCNT+1
    where XMLDOCID=:new.XMLDOCID;


    end if;

    ELSIF :new.traxtype ='CA' then

    update ebc_xmldocsumm set CACNT = CACNT+1
    where XMLDOCID=:new.XMLDOCID;


    if :new.errmsg is notnull then

    update ebc_xmldocsumm set ERRCNT = ERRCNT+1
    where XMLDOCID=:new.XMLDOCID;

    end if;

    ELSE

    update ebc_xmldocsumm set OTHCNT = OTHCNT+1
    where XMLDOCID=:new.XMLDOCID;

    end if;

    else

    if :new.TRAXTYPE in ('FS','AM','CA') then
    insert into EBC_XMLDOCSUMM(UR_COLS,.. ) values:)new.VAL1,:new.VAL2,....);

    update EBC_XMLDOCSUMM set
    FSCNT =DECODE:)new.TRAXTYPE,'FS',1,0),
    AMCNT =DECODE:)new.TRAXTYPE,'AM',1,0),
    CACNT =DECODE:)new.TRAXTYPE,'CA',1,0)
    where XMLDOCID=:new.XMLDOCID;

    else

    update EBC_XMLDOCSUMM set OTHCNT = 1
    where XMLDOCID=:new.XMLDOCID;

    end if;

    end if;

    end case;

    end ebc_xmldoc_count;
     
  10. venu57

    venu57 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Bangalore
    Its working correctly. Thank You.