CODEWITHSUNDEEP
oracle-databaseplsqltriggers
Izumi
Izumi

Reputation: 571

Oracle PL/SQL: a scheduled procedure, leading to firing of a trigger?

Okay, I'm new to Oracle PL/SQL and I've stumbled across a problem that I cannot figure out. I have a procedure that leads to transferring data from one table to another and a trigger that activates on the insertion in the second table. I scheduled that procedure to run every minute (for testing - would be daily once I've figured it out), using the DBMS_JOB.SUBMIT - the scheduled part works perfectly, however after the completion of the procedure the trigger is not fired. I tried with before and after insert clauses, but it is still not working. If I call the procedure directly it works and it does fire the trigger just fine. So... I'm already wondering whether the scheduled procedure can fire the trigger at all?!

This is the schedule's code:

DECLARE
  VJOBN BINARY_INTEGER;
BEGIN
  DBMS_JOB.SUBMIT(
  JOB => VJOBN,
  INTERVAL => 'SYSDATE + 1/2880', 
  WHAT => 'BEGIN my_procedure(); END;'
  );
END;


create or replace TRIGGER TO_PRJ
AFTER INSERT ON PROJECTS
FOR EACH ROW
BEGIN
  IF INSERTING
  THEN DBMS_OUTPUT.PUT_LINE('INSERTED PROJECT WITH ID:  '||:NEW.PROJECT_ID||')
  END IF;
END;​

Table PROJECTS has ID number, name varchar2, and some other that are not important. The procedure transfers the ID and the name from orders to projects.

P.S. I'm using http://apex.oracle.com and when I get the timestamp from it the time is actually 6 hours behind me - not sure if it can be of any significance...

Upvotes: 2

Views: 2304

Answers (2)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

DBMS_OUTPUT and DBMS_JOB do not work the way you are trying to use them. The scheduled job is probably running, the trigger is firing - but since DBMS_OUTPUTneeds to be activated in the session that executes the DBMS_OUTPUT commands (i.e. the internal session used by DBMS_JOB) you will never see any output.

DBMS_OUTPUT's output is not visible across session, so the session that issues the DBMS_JOB.submit command will NOT receive the output, even if DBMS_OUTPUT is activated for that session.

Upvotes: 3

Maep
Maep

Reputation: 843

Try using scheduler, it's much better then jobs. And bring there code of trigger and tables, it may help

Upvotes: 0

Related Questions