Create a trigger with a SELECT inside 1

[pascalxricher]

Hi !

I need to create a trigger on DELETE on a table and raise an exception if a field of the deleted row is in an other table...

I tried this but without success :

CREATE OR REPLACE TRIGGER BD_Table1
BEFORE DELETE ON Table1
FOR EACH ROW
BEGIN
IF OLD.Field IN (SELECT Field FROM Table2)) THEN
RAISE_APPLICATION_ERROR(-20000, 'Access denied !');
END IF;
END;
/

This code create an error when SQL*PLUS is trying to create the trigger. What can I do to solve this problem ???

Thanks !

 

[jee]

The problem is that in your If statement you have what is essentially a subquery - and that won't work. Here is something that does compile and does the same thing (I think):

CREATE OR REPLACE TRIGGER BD_Table1
  BEFORE DELETE ON Table1
  FOR EACH ROW  
  DECLARE
  v_count    NUMBER;
  BEGIN
    SELECT COUNT (*) INTO v_count
    FROM Table2
    WHERE Field = :OLD.Field     
      
    IF (v_count > 0) THEN
      RAISE_APPLICATION_ERROR(-20000, 'Access denied !');   
    END IF;
  END;