TRIGGERS&PROCEDURES

CREATE TRIGGER MAKE_ALIEN_ID BEFORE INSERT ON ALIEN
FOR EACH ROW
BEGIN

SET NEW.ID = CONCAT(
    FLOOR(10000 + (RAND()* 99999)),
    FLOOR(10000 + (RAND()* 99999)),
    FLOOR(10000 + (RAND()* 99999)),
    FLOOR(10000 + (RAND()* 99999)),
    FLOOR(10000 + (RAND()* 99999))
);

END

CREATE TRIGGER KILLER BEFORE DELETE ON ALIEN
FOR EACH ROW
BEGIN

DELETE FROM ALIENSHIP WHERE ALIENSHIP.ALIENID = OLD.ID;
DELETE FROM ALIENGUNS WHERE ALIENGUNS.ALIENID = OLD.ID;

END

CREATE PROCEDURE GetRegisteredAliens
BEGIN
SELECT NAME, FEATURES, RACE, DANGER, REGID, PLANET
FROM ALIEN
WHERE REGISTERED=1;
END

CREATE PROCEDURE GetUnregisteredAliens
BEGIN
SELECT NAME, FEATURES, RACE, DANGER, REGID, PLANET
FROM ALIEN
WHERE REGISTERED=0;
END