title: Trigger on delete to avoid dangling references
body: <p>I have a problem with the implementation of some triggers in Oracle.</p>
  
  <p>I have two types "t_movie" and "t_video" defined as </p>
  
  <pre><code>CREATE TYPE t_movie AS OBJECT( 
  name VARCHAR(20),
  year INTEGER);
  </code></pre>
  
  <p>And</p>
  
  <pre><code>CREATE TYPE t_video AS OBJECT( 
  type CHAR,
  movie REF t_movie);
  </code></pre>
  
  <p>And I have also the associated tables</p>
  
  <pre><code>CREATE TABLE movies OF t_movie
  
  
  CREATE TABLE videos OF t_video
  </code></pre>
  
  <p>If I delete a tuple from the table movies I will have some tuples in the other table with references to object that doesn't exist anymore. How can I avoid this? I thought that a trigger is necessary but I have no idea of how to implement it. Can anyone help me?</p>
  
  <p>Thanks.</p>
  
  <p>Edit:</p>
  
  <p>I tried a trigger like this:</p>
  
  <pre><code>CREATE or REPLACE TRIGGER delete_movie_cascade 
    before delete on movies
    for each row
  DECLARE
    movie_ref (REF t_movie);
  BEGIN
    movie_ref = ref :old;
    dbms_output.put_line(deref(movie_ref).name);
    DELETE FROM videos WHERE movie = movie_ref;
  END;
  </code></pre>
  
  <p>But, as expected, I get the error </p>
  
  <blockquote>
  <pre><code>Error(6,13): PLS-00103: Encountered the symbol "(" when expecting one of the following:     
  constant exception &lt;an identifier&gt;    &lt;a double-quoted delimited-identifier&gt; table long 
  double ref    char time timestamp interval date binary national character    nchar
  </code></pre>
  </blockquote>