Oracle Trigger on Nested Table

Question

I want to create a trigger to validate the date of birth, for example it's not > SYSDATE:

Employee -- STRUCTURED TYPE
{
     name VARCHAR2(10)
     lastname VARCHAR(10)
     birthdate DATE
}

Employee_List TABLE OF Employee -- NESTED TABLE

Museum -- TABLE
{
     id NUMBER
     EmployeeList Employee_List
}

I'm using Oracle 10g.

Anything you've tried and that did not work?

Rene
– Rene

2013-11-27 06:42:50 +00:00
Commented Nov 27, 2013 at 6:42 — Rene
– Rene, Commented Nov 27, 2013 at 6:42

Wernfried Domscheit · Accepted Answer · 2013-11-27 07:25:51Z

1

You cannot define a trigger like "BEFORE UPDATE ON Museum.EmployeeList.birthdate" But you can write a normal trigger BEFORE UPDATE ON Museum and in trigger body you can loop over all employees checking the date.

Try this one:

CREATE OR REPLACE TRIGGER BUIR_Museum 
    BEFORE INSERT OR UPDATE ON Museum
    FOR EACH ROW

BEGIN
    IF :NEW.EmployeeList IS NOT NULL THEN
        FOR i IN :NEW.EmployeeList.FIRST..:NEW.EmployeeList.LAST LOOP
            IF :NEW.EmployeeList(i).birthdate > SYSDATE THEN
                RAISE_APPLICATION_ERROR(-20029, 'Invalid birthday');
            END IF;
        END LOOP;
    END IF;
END;

answered Nov 27, 2013 at 7:25

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle Trigger on Nested Table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related