Sql Trigger Help

[nenu_devudni]

trigger epudu raledu ..ede first .. kasta help cheyandi  .. 

aa climax lo una if condition sariga work avatledu ... enni sarlu try chesina ..flag asalu set katledu ... ekada thapu chesano kasta cheppi punyam katukondi ...  

:#<

 

 CREATE OR REPLACE TRIGGER UIMSMGR.U$_UCRCRHS_AFTER_DB

   AFTER INSERT OR UPDATE

      ON UIMSMGR.UCRCRHS FOR EACH ROW

DECLARE

 

  creditpoints  NUMBER;

  credit_points NUMBER;

  newpoints     NUMBER;

  check_ind     VARCHAR2(1);  

  credit_ind    VARCHAR2(1);

  nsf_count     NUMBER :=0;     

  credit_count  NUMBER;

  ccat_value    VARCHAR2(10); 

  utrccat_value VARCHAR2(10);   

  l_points         NUMBER;

  max_hits       NUMBER := 0;

  actual_hits    NUMBER := 0;

BEGIN 

  SELECT nvl(ucbcust_pay_by_check_ind,'Y'),

         NVL(ucbcust_pay_by_cc_db_ind,'Y'),

         ucbcust_creditpoints

    INTO check_ind,

         credit_ind,

         credit_points

    FROM ucbcust

   WHERE ucbcust_cust_code = :new.ucrcrhs_cust_code;

 

  BEGIN  

    SELECT nvl(utrccat_points_per_occur,0)

      INTO newpoints

      FROM utrccat

          ,utvvals

     WHERE utrccat_code = :new.ucrcrhs_ccat_code

       AND utvvals_mstr_code = 'CREDIT'

       AND utrccat_code = utvvals_value;

      

  EXCEPTION

    WHEN no_data_found THEN

      newpoints := 0;

  END;

 

  creditpoints := creditpoints + newpoints;

 

  UPDATE ucbcust

     SET ucbcust_creditpoints = creditpoints

   WHERE ucbcust_cust_code = :new.ucrcrhs_cust_code;

 

 BEGIN   

    SELECT utrccat_code

      INTO ccat_value

      FROM utrccat,

           utvvals

     WHERE utrccat_code = :new.ucrcrhs_ccat_code

       AND utvvals_mstr_code = 'CREDIT'

       AND utrccat_code = utvvals_value;

 EXCEPTION

    WHEN no_data_found THEN

    ccat_value := 'NONE'; 

 END;

   

   max_hits := 0;

   actual_hits := 0;

      

    BEGIN

        SELECT utrccat_max_credit_hits,

           NVL                           

             ( SUM                         

               ( TO_NUMBER                 

                 ( decode                  

                   ( ucrcrhs_reversal_ind,       

                     'Y','-1',             

                     '1'                   

                   )                       

                 )                         

               )                           

              ,0                           

             )     

      INTO max_hits,

           actual_hits

      FROM ucrcrhs, utvvals, utrccat                  

     WHERE utrccat_code = :NEW.ucrcrhs_ccat_code

       AND utvvals_mstr_code = 'CREDIT'

       AND utrccat_code = utvvals_value

       and ucrcrhs_cust_code = :new.ucrcrhs_cust_code 

       AND ucrcrhs_occurance_date BETWEEN TRUNC(ADD_MONTHS(SYSDATE,0 - utrccat_valid_for_months)) AND TRUNC(SYSDATE) + .99999   

      group by utrccat_max_credit_hits; 

    

    EXCEPTION

    WHEN no_data_found THEN

     dbms_output.put_line(SQLERRM);

       WHEN OTHERS THEN

       dbms_output.put_line(sqlerrm);

    END; 

   

    --  reversal

      IF :NEW.ucrcrhs_ccat_code = ccat_value --'NSF'   

      THEN

        IF :NEW.ucrcrhs_reversal_ind = 'Y'

        THEN

           nsf_count := 0;

           nsf_count := nvl(credit_points,0) - newpoints;       

 

          UPDATE ucbcust

            SET ucbcust_creditpoints = nsf_count

          WHERE ucbcust_cust_code = :new.ucrcrhs_cust_code;

          

         ELSE 

            nsf_count := 0;

            nsf_count := nvl(credit_points,0) + newpoints;

 

            UPDATE ucbcust

               SET ucbcust_creditpoints = nsf_count

             WHERE ucbcust_cust_code = :new.ucrcrhs_cust_code;         

        END IF;    

        

        IF actual_hits > max_hits

        THEN

          IF check_ind = 'Y'

          THEN

            UPDATE ucbcust a 

               SET a.ucbcust_pay_by_check_ind = 'N'

             WHERE a.ucbcust_cust_code = :new.ucrcrhs_cust_code;

          END IF;

         ELSE

          IF check_ind = 'N'

          THEN

              UPDATE ucbcust a

               SET a.ucbcust_pay_by_check_ind = 'Y'

             WHERE a.ucbcust_cust_code = :new.ucrcrhs_cust_code;

          END IF;

        END IF;

      END IF;

  

END u$_ucrcrhs_after_db;

/

 

[MeetFriendz]

ltt

[Mastanvali]

baboyee code raseyachu..kani dinni inspect cheyalante chiraku……i dont have work experience dintlo….

[kum5758]

ltt

[nenu_devudni]

[nenu_devudni]

baboyee code raseyachu..kani dinni inspect cheyalante chiraku……i dont have work experience dintlo….

 

logic lu enni sarlu alochinchina sakaga ardam ayyi savavu mayya .. enni sarlu try chesina ratledu .. :3D_Smiles_38: . 

[Mastanvali]

 love india(genelia shashireka parinayam pic )  uncle  sql teach chestadanta..i want him to solve this one

[sandy240887]

exception lo dbms msgs pettukunnatte, same to same aa last logic lo kuda pettukoni em values vasthunnayo check chesuko. First actual_hits inka max_hits print chesuko, then check_ind value check cheyyi..tharvatha update statement tharvatha inko dbms statement pettukoni SQL%ROWCOUNT use chesi, enni rows update ayinayo check chesuko...Itla every step ki dbms msgs pettukoni check chesuko nuvvu expect chesina value vasthunda leda ani...

 

[puli raaja]

CITI#H@

[Mastanvali]

love india …itu sudali

[nenu_devudni]

exception lo dbms msgs pettukunnatte, same to same aa last logic lo kuda pettukoni em values vasthunnayo check chesuko. First actual_hits inka max_hits print chesuko, then check_ind value check cheyyi..tharvatha update statement tharvatha inko dbms statement pettukoni SQL%ROWCOUNT use chesi, enni rows update ayinayo check chesuko...Itla every step ki dbms msgs pettukoni check chesuko nuvvu expect chesina value vasthunda leda ani...

but mayya .. ee trigger front end form nundi records insert avtay ... so apudu dbms statement lu ekada kanadapatay ??

[nenu_devudni]

plsql block or procedure aithe ala test cheyochu but oka trigger lo dbms statement lo una output ekada kanapadatay ??