title: PL / SQL Trigger to write data to a txt file
body: <p>I have this problem that writes a text line each time all copies of a movie are checked out. They want to contain the date, movie ID, and movie stock quantity on the text file. I have used a cursor to obtain data from the table, and the UTL_file.FILE_TYPE to write output to the text file. The trigger compiles however I am not getting any file written.</p>
  
  <pre><code>create or replace trigger zeromovies_write
  After update on mm_movie    
  
  DECLARE
  
  c_id mm_movie.movie_id%type;
  c_qty mm_movie.movie_qty%type;
  CURSOR c_movie is 
  SELECT movie_id, movie_qty FROM mm_movie;
  fh UTL_FILE.FILE_TYPE;
  v_date DATE;
  total VARCHAR2(50);
  
  BEGIN
  
  OPEN c_movie;
  LOOP
    FETCH c_movie into c_id, c_qty;
    EXIT WHEN c_movie%notfound;
  END LOOP;
  if c_qty = 0 THEN
  select sysdate into v_date from dual;
  total := (c_id || c_qty || v_date);
  fh := UTL_FILE.FOPEN('ORA_FILES', 'checkout.txt', 'w');
  UTL_FILE.PUT_LINE(fh, total);
  end if;
  UTL_FILE.FCLOSE(fh);
  close c_movie;
  
  END;
  </code></pre>
  
  <p>I then update the table using the following code (setting qty to 0) but the write does not happen.</p>
  
  <pre><code>UPDATE mm_movie
  SET movie_qty = 0
  WHERE movie_id = 7;
  
  UPDATE mm_movie 
  SET movie_qty = 0
  WHERE movie_id = 8;
  </code></pre>