We help IT Professionals succeed at work.
Get Started

Primary key check

1,755 Views
Last Modified: 2013-12-19
Hi in my form when we enter a record it check where the record exists or not ,
if exists it its gives an option to update. I have written a procedure for that and calling that procedure form when validate item trigger, its giving an error as Illegal restricted procedure GO_BLOCK in Trigger,
I have used, post-item and pre insert tirgger but it gives the same message.
The procedure code is,
PROCEDURE MEDIA_REC_CHECK IS
r_count number :=0;
alert_id  alert;
choice number;
where_clause varchar2(1000);
BEGIN
  SELECT COUNT(*) INTO r_count
  FROM MEDIA
  WHERE  OWNER_ID = :DB_MEDIA.OWNER_ID
  AND MEDIA_TYPE = :DB_MEDIA.MEDIA_TYPE;
IF r_count > 0 THEN
            alert_id := FIND_ALERT('ALERT_PRIMEKEY');
            choice := SHOW_ALERT(alert_id);
            if choice = ALERT_BUTTON1 then
                  GO_BLOCK('CB_MEDIA');
                  :CB_MEDIA.TXT_OWNER := :DB_MEDIA.OWNER_ID;
                  :CB_MEDIA.TXT_MCODE := :DB_MEDIA.MEDIA_TYPE;
                  GO_BLOCK('DB_MEDIA');
                  clear_block(no_validate);
                  IF(:CB_MEDIA.TXT_EDMSFT is null) then
                         where_clause := 'OWNER_ID LIKE''' || :CB_MEDIA.TXT_OWNER || '%'''  || ' AND MEDIA_TYPE LIKE''' || :CB_MEDIA.TXT_MCODE || '%''';
                  ELSE
                         where_clause := 'OWNER_ID LIKE''' || :CB_MEDIA.TXT_OWNER || '%'''  || ' AND MEDIA_TYPE LIKE''' || :CB_MEDIA.TXT_MCODE || '%''' || 'AND EDMS_FILE_TYPE LIKE''' || :CB_MEDIA.TXT_EDMSFT || '%''';
                  END IF;
                  GO_BLOCK('DB_MEDIA');
                  SET_BLOCK_PROPERTY('DB_MEDIA', DEFAULT_WHERE, where_clause);
                  EXECUTE_QUERY;
            else
                  GO_BLOCK('DB_MEDIA');
                  clear_block(no_validate);      
            end if;
END IF;
END;
Comment
Watch Question
Database Administrator, retired
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE