CREATE OR REPLACE TRIGGER TR_B_IU_H_M
before
INSERT OR UPDATE ON ELENCO
FOR EACH ROW
DECLARE
nmujeres SMALLINT :=0;
nhombres SMALLINT :=0;
genero VARCHAR2(20);
mensaje VARCHAR2(20) :='';
BEGIN
    SELECT GENERO_ACTOR INTO genero FROM ACTOR WHERE ID_ACTOR = :NEW.id_actor;
    IF genero = 'MASCULINO'  THEN
     nhombres := nhombres+1;
    ELSIF genero = 'FEMENINO' THEN
     nmujeres := nmujeres+1;
    END IF;
    FOR i IN (SELECT * FROM ELENCO )
    LOOP
        IF i.id_pelicula = :NEW.id_pelicula THEN
            SELECT GENERO_ACTOR INTO genero FROM ACTOR WHERE ID_ACTOR = i.id_actor AND NOT ID_ACTOR=:NEW.id_actor;
            IF genero = 'MASCULINO'  THEN
             nhombres := nhombres+1;
            ELSIF genero = 'FEMENINO' THEN
             nmujeres := nmujeres+1;
            END IF;
        END IF;
    END LOOP;
    UPDATE PELICULA SET TOTAL_HOMBRES_PELICULA = nhombres, TOTAL_MUJERES_PELICULA=nmujeres, TOTAL_ACTORES = nhombres+nmujeres WHERE ID_PELICULA = :NEW.id_pelicula;
END;