DELIMITER $$
DROP PROCEDURE IF EXISTS dContacto;
CREATE PROCEDURE dContacto
(clave$ VARCHAR(20), contacto$ VARCHAR(200), estado$ INT)
BEGIN
	DECLARE clave_$ INT;
	IF estado$ = 1 THEN
		SET clave_$ :=  (SELECT id FROM predios WHERE descifrar(predio) = clave$);
	ELSE
		SET clave_$ := (SELECT id FROM residentes WHERE clave = clave$);
		DELETE FROM contactos WHERE clave = clave_$ AND descifrar(contacto) = contacto$;
	END IF;
END
$$

-- >>> registra los contactos de los trabajadores, residentes y predios
DELIMITER $$
DROP PROCEDURE IF EXISTS iContacto;
CREATE PROCEDURE iContacto
(clave$ VARCHAR(10), contacto$ VARCHAR(100), estado$ INT)
BEGIN
	-- variable para id de trabajador, residente o predio
	DECLARE id_$ INT;
	IF estado$ = 1 THEN
		SET id_$ := (SELECT id FROM predios WHERE descifrar(predio) = clave$ AND estado_registro = 11);
	ELSE
		SET id_$ := (SELECT id FROM residentes WHERE clave = clave$);
	END IF;
	INSERT INTO contactos(clave, contacto, estado)
	VALUES(id_$, cifrar(contacto$), estado$);
	IF (ROW_COUNT()) = 1 THEN
		SELECT 800 AS estado, 'Se registró el contacto' AS mssg;
	ELSE
		SELECT 500 AS estado, 'Intenta de nuevo' AS mssg;
	END IF;
END
$$
