CODEWITHSUNDEEP
oracle-databaseplsqltriggersoracle-sqldeveloper
Zohaib Chaudhry
Zohaib Chaudhry

Reputation: 1

PL/SQL Unable to get return value of a function when called through trigger - Oracle

I am calling a function in oracle in an after update trigger. The Function is returning a value that is equated to perform a select and an insert operation.

The issue is when I am calling this function in the trigger it is getting terminated, that is it is not performing the corresponding insert operation. But the function is working fine when I execute it by itself. Also, if the trigger is run by removing the condition which is returned by the function, it is getting executed as expected.

Function:

CREATE OR REPLACE FUNCTION VERIFY_FINAL 
    (case_id IN number)    
    RETURN varchar2
IS

  is_marked_final varchar2(4);    
  loop_count number(2);

  cursor c1 is
      SELECT sub_case_status from
      cdm_master_sub_case 
      where master_id = (case_id);

BEGIN

    is_marked_final := 'Y';
    loop_count := 0;

    FOR rec in c1
    LOOP
      IF (rec.sub_case_status = '1') THEN
        is_marked_final := 'Y';
      ELSIF (rec.sub_case_status = '2') THEN
        is_marked_final := 'Y';
      ELSE
        loop_count := loop_count + 1;
      END if;
    END LOOP;

    IF (loop_count > 0) THEN
      is_marked_final := 'N';
    END if;

  RETURN is_marked_final;

END;

Trigger:

CREATE OR REPLACE TRIGGER CDM_MASTER_SUB_CASE_TRIGGER    
AFTER UPDATE
on CDM_MASTER_SUB_CASE
FOR EACH ROW

DECLARE    
  check_var varchar2(4);
  unique_id varchar2(100);
  transaction_id number(10);
BEGIN

  transaction_id := :new.MASTER_ID;
  check_var := VERIFY_FINAL(transaction_id);

  IF (check_var = 'Y') THEN

    select UNIQUE_CUST_ID 
    INTO unique_id 
    from ASM355.cdm_matches 
    where MASTER_ID = :new.MASTER_ID 
    and rownum = 1;

    INSERT INTO tracking_final_cases (MASTER_ID,unique_cust) 
    values (:new.master_id,unique_id);

  END if;

END;

I would appreciate it if anyone can point me in the right direction.

Upvotes: 0

Views: 536

Answers (3)

Zohaib Chaudhry
Zohaib Chaudhry

Reputation: 1

Thanks guys for the time, it got resolved. I was querying a select statement in the function body over a table on which the corresponding trigger was created.

Upvotes: 0

Michael Broughton
Michael Broughton

Reputation: 4055

1.) As tmrozek points out a return of 'N' will not do the associated insert. I might suggest having an ELSE to that IF that does something to indicate if that is what is happening.

2.) I would also point out that your SELECT INTO, if it does not find a corresponding value, would cause issues. You might want to do something to ensure that this trigger is failsafe, or have you considered what you want the code to do if that situation occurs? (Error out? Insert a null unique_id?)

3.) If you are looking at the results from a different session, bear in mind that the inserted tracking_final_cases will not be visible until you commit your changes in the session that called the trigger.

Upvotes: 1

tmrozek
tmrozek

Reputation: 79

I don't know your table data but it is possible to your function to return 'N' so it wouldn't meet your trigger condition (check_var = 'Y').

If you run command like that:

update CDM_MASTER_SUB_CASE
set sub_case_status = 3;

you will probably get your problem.

Upvotes: 0

Related Questions