Trigger help needed!

[sheed]

Hi,
I am new to PL/SQL and trying to solve the following problem. I have a trigger on a table that is fired on INSERT/UPDATE on STARTINGTBL (i.e. TABLE), I am checking one of the inserted value against TABLE_CHECK2 (i.e. TABLE) and if I find a value, I am trying to
to grab all values matching the same value from CHECK_DATA_VIEW (i.e VIEW) and then inserting the values grabbed from a CHECK_DATA_VIEW in my FINAL_TBL (i.e. TABLE). I have a following cursor at the top and don't know why it's not working and complains on CD.CHECK1%TYPE i.e.

    CURSOR C(CHECKINGDATA CD.CHECK1%TYPE) IS 
      SELECT CD.CHECK1, 
             CD.CHECK_2,
	     CD.CHECK_3
      FROM CHECK_DATA_VIEW CD
      WHERE CD.SOURCE = CHECKINGDATA;

Following is my TRIGGER code.

CREATE OR REPLACE TRIGGER TRGTST 
AFTER INSERT OR UPDATE ON STARTINGTBL
DECLARE  
    VAR_CHECK1 CHECK_DATA_VIEW.CHECK1%TYPE;
    VAR_CHECK_2 CHECK_DATA_VIEW.CHECK_2%TYPE;
    VAR_CHECK_3 CHECK_DATA_VIEW.CHECK_3%TYPE;
    VAR_COUNT NUMBER;
    VAR_TEMP VARCHAR2(20);
	    
    CURSOR C(CHECKINGDATA CD.CHECK1%TYPE) IS 
      SELECT CD.CHECK1, 
             CD.CHECK_2,
	     CD.CHECK_3
      FROM CHECK_DATA_VIEW CD
      WHERE CD.SOURCE = CHECKINGDATA;   
BEGIN
    VAR_COUNT := 0;
    IF :NEW.INSERTEDDB1 IS NOT NULL THEN
       VAR_TEMP := TRIM(UPPER(:NEW.INSERTEDDB1));
       SELECT COUNT(*) INTO VAR_COUNT
       FROM TABLE_CHECK2
       WHERE UPPER(INSERTEDDB2) = VAR_TEMP; 
    END IF;
    
    IF VAR_COUNT > 0 THEN
        OPEN C(:NEW.INSERTEDDB1);
        LOOP
           FETCH C INTO VAR_CHECK1, VAR_CHECK_2, VAR_CHECK_3;
           EXIT WHEN C%NOTFOUND;
           INSERT INTO FINAL_TBL(UNIQUE_ID,VALUE1,VALUE2,VALUE3) VALUES(TST_SEQ.NEXTVAL,VAR_CHECK_3,VAR_CHECK1,VAR_CHECK_2);
        END LOOP;
    END IF;
EXCEPTION
  WHEN OTHERS
  THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

Can someone please point me what am I doing wrong. I also want to have EXCEPTION HANDLING code too for each DML statement. Thanks

 

[SantaMufasa]

Sheed,

1) Please post a "DESCRIBE check_data_view". Since the other two variables from "check_data_view" appear to be in a different format from "CHECK1", it may be that the correct reference is "CHECK_1".

2) If you wish to refer to pre- or post-change values of a column (as ":new.<column>" or "ld.<column>") from your triggering table, then you must add the following to your trigger definition:

CREATE OR REPLACE TRIGGER TRGTST 
AFTER INSERT OR UPDATE ON STARTINGTBL
[b]FOR EACH ROW[/b]...

3) I'm not quite sure how you plan for "CHECKINGDATA" to become populated (...I'm just not understanding the code with which you are trying to populate CHECKINGDATA. It doesn't mean your code is wrong...it just means that I don't understand it.) Therefore, until I understand it better, (in the sample code, below) I "stubbed" a variable by that name with a value in table_check2.INSERTEDDB2.

4) Although we could probably re-work your code to get it to work using syntax corrections, if you are looking for the simplest technique to achieve your trigger's objective, I believe that you can accomplish the objective without a CURSOR:

create or replace trigger trgtst
    after insert or update on startingtbl
    for each row
declare
    checkingdata varchar2(20) := 'Whatever';
begin
    insert into final_tbl(UNIQUE_ID,VALUE1,VALUE2,VALUE3)
      (select tst_seq.nextval,CHECK1, CHECK_2, CHECK_3
         FROM CHECK_DATA_VIEW
        WHERE SOURCE = CHECKINGDATA
          and exists (select 'x'
                        from table_check2
                       where UPPER(INSERTEDDB2) = TRIM(UPPER(:NEW.INSERTEDDB1))));
exception
      WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;   
/

Trigger created.

If, however, the purpose of your exercise is to understand better how to use CURSORS, then I suggest that you investigate "implicit CURSORS" in a "Cursor FOR LOOP". Below is a simple sample of an implicit cursor. As you can see, the code for an implicit cursor is far simpler than an explicit cursor...implicit cursors free you from having to

1) DECLARE the cursor explicitly,
2) OPEN the cursor in your BEGIN section,
3) FETCH rows explicitly.
4) Check for "<cursor>%FOUND" or "<cursor>%NOTFOUND".
5) CLOSE the cursor when you are done.

So, in other words, implicit CURSORs do just about everything for you:

set serveroutput on format wrap
begin
    for r in (select last_name, salary from s_emp
               where salary > 1500
               order by salary desc) loop
        dbms_output.put_line(to_char(r.salary,'$99,999')
               ||' '||r.last_name);
    end loop;
end;
/
  $3,328 Velasquez
  $1,550 Ropeburn
  $1,525 Nguyen
  $1,515 Sedeghi

PL/SQL procedure successfully completed.
**********************************************************************************

Let us know if any of this is helpful in resolving your needs.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sheed]

How do I Rollback the whole transaction if some of the records in the cursor don't pass the condition for inserting. How do I make all the records to be committed only if all the records in the CUR_V1 CURSOR meets the condition IF VAR_1 IS NOT NULL AND VAR_2 IS NOT NULL AND VAR_3 IS NOT NULL THEN . If any record fails the condition want to rollback all the transaction and don't want any record to be inserted. Basically only commits when all the records in the cursor are inserted successfully. Following is Trigger code. Any extra tips are appreciated to make it more cleaner and have better exception handling in it:

CREATE OR REPLACE TRIGGER DBSYSUSR.TSTTRG 
AFTER INSERT OR UPDATE ON STARTTBL
FOR EACH ROW
DECLARE 
    VAR_1 V_VIEW.V_V1%TYPE; 
    VAR_2 V_VIEW.V_V2%TYPE; 
    VAR_3 V_VIEW.V_V3%TYPE;
    V_RECCOUNT NUMBER;
    V_CNT NUMBER;
    V_E_NUMB NUMBER;
    V_E_MESSAGE VARCHAR2(100);
    V_TEMP VARCHAR2(20);
    V_FLAG BOOLEAN := FALSE;
    E_EXCEPTION1 EXCEPTION;
    E_EXCEPTION2 EXCEPTION;
    E_EXCEPTION_V3 EXCEPTION;
    E_EXCEPTION_V4 EXCEPTION;
    TYPE ICurTyp IS REF CURSOR; -- define weak REF CURSOR type
    CUR_V1 ICurTyp;  -- declare cursor variable
  
BEGIN
    BEGIN
     V_CNT := 0;
     IF :NEW.VAR_10 IS NOT NULL THEN
        V_TEMP := TRIM(UPPER(:NEW.VAR_10));
        SELECT COUNT(*) INTO V_CNT
        FROM CHECK_LIST
        WHERE UPPER(VAR_10_VALUE) = V_TEMP; 
     ELSE
  RAISE E_EXCEPTION1;
     END IF;
  
     IF V_CNT = 0 THEN
        RAISE E_EXCEPTION2;
     END IF;
  
 EXCEPTION
     WHEN E_EXCEPTION1
        THEN
     RAISE E_EXCEPTION1;
   
 WHEN E_EXCEPTION2
        THEN
     RAISE E_EXCEPTION2;
  
 WHEN OTHERS
 THEN      
     V_E_NUMB := SQLCODE;
            V_E_MESSAGE := SUBSTR(SQLERRM, 1, 100);
     RAISE_APPLICATION_ERROR(-20000, 'ERROR IN TSTTRG - ' || V_E_NUMB || '-' || V_E_MESSAGE);
    END; 
 BEGIN     
     V_RECCOUNT := 0;
     OPEN CUR_V1 FOR  -- open cursor variable
  SELECT CD.V_V1, 
                       CD.V_V2,
                CD.V_V3
                FROM V_VIEW CD
                WHERE CD.V_V1 = :NEW.VAR_10;
     LOOP
        FETCH CUR_V1 INTO VAR_1, VAR_2, VAR_3;
        EXIT WHEN CUR_V1%NOTFOUND;
     V_RECCOUNT := V_RECCOUNT + 1;
     IF VAR_1 IS NOT NULL AND VAR_2 IS NOT NULL AND VAR_3 IS NOT NULL THEN
               INSERT INTO IN_OUT_STAGE(ID,VAR_15,VAR_10,PS,LN) VALUES(SEQUENCE_TEST.NEXTVAL,:NEW.VAR_15,VAR_1,VAR_2,VAR_3);
     ELSE
     V_FLAG := TRUE;
     ROLLBACK;
     EXIT;
     END IF;
     END LOOP;
  CLOSE CUR_V1;
  
  IF V_RECCOUNT = 0 THEN
     RAISE E_EXCEPTION_V4;
  END IF;
  IF V_FLAG = TRUE THEN
     RAISE E_EXCEPTION_V3;
  END IF;
 EXCEPTION
     WHEN E_EXCEPTION_V3
  THEN
      RAISE E_EXCEPTION_V3;
     WHEN E_EXCEPTION_V4
  THEN
      RAISE E_EXCEPTION_V4;
   
  WHEN OTHERS
  THEN
      V_E_NUMB := SQLCODE;
                    V_E_MESSAGE := SUBSTR(SQLERRM, 1, 100);
      RAISE_APPLICATION_ERROR(-20000, 'ERROR IN TSTTRG - ' || V_E_NUMB || '-' || V_E_MESSAGE);
 END;
EXCEPTION
  WHEN E_EXCEPTION1
  THEN
  INSERT INTO CCA_LOG(LOG) VALUES('THERE IS NO VAR_10 ' || :NEW.VAR_15 || ' - ' || :NEW.VARIABLE_LN);
  
  WHEN E_EXCEPTION2
  THEN
  INSERT INTO CCA_LOG(LOG) VALUES('THERE IS NO COUNT ' || :NEW.VAR_15 || ' - ' || :NEW.VARIABLE_LN);
      
  WHEN E_EXCEPTION_V3
  THEN
  INSERT INTO CCA_LOG(LOG) VALUES('THERE IS NO VIEW DATA ' || :NEW.VAR_15 || ' - ' || :NEW.VARIABLE_LN);
   
  WHEN OTHERS
  THEN
      V_E_NUMB := SQLCODE;
      V_E_MESSAGE := SUBSTR(SQLERRM, 1, 100);
      DBMS_OUTPUT.PUT_LINE('ERROR IN TRG -' || V_E_NUMB || '-' || V_E_MESSAGE);
END TSTTRG;

 

[Beilstwh]

You can't have rollback or commit in a normal trigger. If the base update that fired the trigger completes and a commit is issued then all the work done by the trigger is committed. If the trigger fails or a rollback is issued, then ALL the transactions done by the trigger are automatically rolled back.

Bill
Oracle DBA/Developer
New York State, USA