Would appreciate some assistance; I am using Oracle.

Current code:

```
CREATE OR REPLACE TRIGGER TRG_SAMPLETRG
BEFORE INSERT ON CATEGORY_Details
FOR EACH ROW
DECLARE W_YEARSALIVE NUMBER(3);
BEGIN

  SELECT ((SYSDATE - animal_origindate)/365) INTO W_YEARSALIVE
  FROM animal
  WHERE animal_no = :NEW.animal_no;

  IF W_YEARSALIVE < 180 THEN
    RAISE_APPLICATION_ERROR(-20005, 'The animal has existed for only ' || W_YEARSALIVE || ' years');
  ELSIF W_YEARSALIVE IS NULL THEN
    RAISE_APPLICATION_ERROR(-20005, 'The animal has to have a origin date entered before evaluation.');
  ELSE
    DBMS_OUTPUT.PUT_LINE ('The animal is over 180 years and is not yet extinct and has been added');
  END IF;
END;

```

When I am adding an animal into the category\_details table, if the animal has an origin date (in years), of less than 180, OR if it does not have an origin date, it will not be added and an exception error will be returned.

Now, **I don't require the trigger to be evaluated on every animal that I insert into the category\_details table, I only want to add/evaluate the trigger on the animals from only 4 of these different categories: Monera, Prokaryota, Plantae, Eukaryota**

i.e. **any other different category, i.e. Animalia, Vegetabilia, should not even be evaluated at all, aka, no restrictions on any insertion of an animal that are related to those (or 'other') categories.**

I've tried adding a WHEN statement with a subquery like below:
 WHEN (:NEW.kingdom\_no IN (SELECT kingdom\_no FROM kingdom WHERE kingdom\_name IN ('Monera', 'Prokaryota', 'Plantae', 'Eukaryota'))

but it looks like you can't add subqueries into TRIGGERS...

I found this thread: [Oracle: Using subquery in a trigger](https://stackoverflow.com/questions/909156/oracle-using-subquery-in-a-trigger)

However, it appears that only looks neat with 1 value/category, whereas I have 4, and would have to declare 4 variables?

I was also trying to put a subquery within an IF statement, however I don't think that is syntactically valid in SQL/Oracle.

Any assistance is appreciated thank you.

EDIT: Table structure is simply:
3 tables: animals, category\_details and kingdom
category\_details' table has the following attributes: animal\_no, kingdom\_no
animals' table has following attributes: animal\_no, animal\_origindate
kingdoms' table has the following attributes: kingdom\_no, kingdom\_name