Usar Trigger? Procedure? Ambas?

Banco de Dados

02/06/2018

Pessoal, estou perdido tenho a seguinte tarefa abaixo, mas usando a trigger não posso dar o select ela nao aceita, e não sei se uso procedure, triger, ambas, sei que essa parte deveria ser feito pela aplicação, mas o professor quer essa apresentação somente em BD.

compare se a data e hora da viagem e o motorista e viatura estão disponíveis antes de incluir no banco de dados,

Entao digamos que a viatura Gol tem uma viagem com o motorista Carlos dia 22/03/2018 as 11:00 com termino no mesmo dia porem na hora 17:00 e suponhamos que alguem quera cadastra a viagem com o mesmo motorista a mesma viatura no mesmo dia porem no horario do 12:00, não poderia ser cadastrado já que a viatura e motorista estariam ocupados. Estão preci ser checado se a viatura está disponivel, se o motorista está disponivel em um intervalo de dia e hora, pois poderam acontecer viagens de mais de um dia.

Abaixo segue meu código sou bem iniciante e toda ajuda seria muito bem vinda.

Já criei um posto sobre isso, mas mudei a tabela e naquele post não consegui resolver

agredeço a ajuda, batendo o desespero já

CREATE TABLE DADOSPESSOAS(
	IDDADOSPESSOAS INT PRIMARY KEY AUTO_INCREMENT,
	NOME VARCHAR(45) NOT NULL,
	EMAIL VARCHAR(45) UNIQUE NOT NULL,
	NIVEL CHAR(1) NOT NULL,
	DATANASCIMENTO DATE NOT NULL,
	SEXO CHAR(1) NOT NULL,
	CPF CHAR(14) UNIQUE NOT NULL,
	CELULAR VARCHAR(14) UNIQUE NOT NULL,
	CEP CHAR (9) NOT NULL,
	RUA VARCHAR(30) NOT NULL,
	NUMERO VARCHAR(8) NOT NULL,
	BAIRRO VARCHAR(25) NOT NULL,
	CIDADE VARCHAR(40) NOT NULL,
	ESTADO CHAR(2) NOT NULL
);

CREATE TABLE MOTORISTA (
	IDMOTORISTA INT PRIMARY KEY AUTO_INCREMENT,
	NUMEROREGISTRO VARCHAR(10) UNIQUE NOT NULL,
	VALIDADE DATE NOT NULL,
	ID_DADOSPESSOAS INT
);


CREATE TABLE ADMIN(
	IDADMIN INT PRIMARY KEY AUTO_INCREMENT,
	SENHA VARCHAR(12) NOT NULL,
	ID_DADOSPESSOAS INT
);

CREATE TABLE VIATURA (
	IDVIATURA INT PRIMARY KEY AUTO_INCREMENT,
	TIPOVEICULO VARCHAR(45) NOT NULL,
	MARCA VARCHAR(25) NOT NULL,
	MODELO VARCHAR(25) NOT NULL,
	COR VARCHAR(25) NOT NULL,
	ANOFABRICACAO CHAR(4) NOT NULL,
	PLACA CHAR(8) UNIQUE NOT NULL,
	RENAVAN CHAR(11) UNIQUE NOT NULL,
	PASSAGEIROS CHAR(2) NOT NULL

);

CREATE TABLE VIAGENS (
	IDVIAGENS INT PRIMARY KEY AUTO_INCREMENT,
	CIDADE VARCHAR(25) NOT NULL,
	DATAHORAINICIO DATETIME NOT NULL,
	DATAHORAFINAL DATETIME NOT NULL,
	COR VARCHAR(15) NOT NULL,
	CEP CHAR(9) NOT NULL,
	RUA VARCHAR(30) NOT NULL,
	NUMERO VARCHAR(8) NOT NULL,
	BAIRRO VARCHAR(25) NOT NULL,
	ESTADO CHAR(2) NOT NULL,
	DESCRICAO VARCHAR(200),
	ID_DADOSPESSOAS INT,
	ID_MOTORISTA INT,
	ID_VIATURA INT
);

CREATE  TABLE  PASSAGEIRO (
	IDPASSAGEIRO INT  PRIMARY KEY AUTO_INCREMENT,
	ID_DADOSPESSOAS INT ,
	ID_VIAGEM INT
);


	ALTER TABLE VIAGENS
	ADD CONSTRAINT FK_DADOSPESSOAS_VIAGENS
	FOREIGN KEY (ID_DADOSPESSOAS)
	REFERENCES DADOSPESSOAS(IDDADOSPESSOAS) ON DELETE CASCADE ON UPDATE CASCADE;

	ALTER TABLE VIAGENS
	ADD CONSTRAINT FK_MOTORISTA_VIAGENS
	FOREIGN KEY (ID_MOTORISTA)
	REFERENCES MOTORISTA(IDMOTORISTA)ON DELETE CASCADE ON UPDATE CASCADE;

	ALTER TABLE VIAGENS
	ADD CONSTRAINT FK_VIATURA_VIAGENS
	FOREIGN KEY (ID_VIATURA)
	REFERENCES VIATURA(IDVIATURA)ON DELETE CASCADE ON UPDATE CASCADE;

	ALTER TABLE MOTORISTA
	ADD CONSTRAINT FK_DADOSPESSOAS_MOTORISTA
	FOREIGN KEY (ID_DADOSPESSOAS)
	REFERENCES DADOSPESSOAS(IDDADOSPESSOAS)ON DELETE CASCADE ON UPDATE CASCADE;

	ALTER TABLE ADMIN
	ADD CONSTRAINT FK_DADOSPESSOAS_ADMIN
	FOREIGN KEY (ID_DADOSPESSOAS)
	REFERENCES DADOSPESSOAS(IDDADOSPESSOAS)ON DELETE CASCADE ON UPDATE CASCADE;

	ALTER TABLE PASSAGEIRO
	ADD CONSTRAINT FK_DADOSPESSOAS_PASSAGEIRO
	FOREIGN KEY (ID_DADOSPESSOAS)
	REFERENCES DADOSPESSOAS (IDDADOSPESSOAS)ON DELETE CASCADE ON UPDATE CASCADE;

	ALTER TABLE PASSAGEIRO
	ADD CONSTRAINT FK_VIAGENS_PASSAGEIRO
	FOREIGN KEY (ID_VIAGEM)
	REFERENCES VIAGENS (IDVIAGENS)ON DELETE CASCADE ON UPDATE CASCADE;
Maurício Kalfelz

Maurício Kalfelz

Curtidas 0

Respostas

Venancio Alves

Venancio Alves

02/06/2018

Olá Maurício Kalfelz, sabe que trigger é apenas um gatilho, ou seja, ela dispara determinada ação quando alguma transação ocorre em uma tabela, por exemplo: insert, delete ou update. É possível fazer apenas com procedure, porém para você que está começando é bom ter dados em suas tabelas para fazer simulações.
GOSTEI 0
Venancio Alves

Venancio Alves

02/06/2018

Se for criar uma procedure no MySQL ficará parecida conforme o código abaixo:


CREATE DEFINER=`root`@`localhost` PROCEDURE `Disponibilidade_Viagem`(
  IN idViatura INT,
  IN agendarViagem DATETIME
)
BEGIN
SELECT	Motorista,
		Carro,
		Destino,
		inicioViagem,
		terminoViagem,
		(IF( 
				inicioViagem = agendarViagem,
				'Viagem indisponível',
				'Viagem disponível'
           )
		) AS Viagem,
        (IF(
				idViatura = idViatura,
                'Carro Indisponível',
                'Carro Disponpivel'
			)
        ) AS Carro
FROM Viatura
INNER JOIN Viagem
ON
Viagem.idViatura = Viatura.idViatura			
INNER JOIN	Motorista
ON
Motorista.idMotorista = Viagem.idMotorista
WHERE idViatura = idViatura
AND agendarViagem = CURRENT_TIMESTAMP();
END


Obs: Você pode adicionar mais colunas como retorno de dados e até outros parâmetros de entrada, vai depender da sua lógica, pois existe outras maneiras de chegar no mesmo resultado.
GOSTEI 0
Maurício Kalfelz

Maurício Kalfelz

02/06/2018

Se for criar uma procedure no MySQL ficará parecida conforme o código abaixo:


CREATE DEFINER=`root`@`localhost` PROCEDURE `Disponibilidade_Viagem`(
  IN idViatura INT,
  IN agendarViagem DATETIME
)
BEGIN
SELECT	Motorista,
		Carro,
		Destino,
		inicioViagem,
		terminoViagem,
		(IF( 
				inicioViagem = agendarViagem,
				'Viagem indisponível',
				'Viagem disponível'
           )
		) AS Viagem,
        (IF(
				idViatura = idViatura,
                'Carro Indisponível',
                'Carro Disponpivel'
			)
        ) AS Carro
FROM Viatura
INNER JOIN Viagem
ON
Viagem.idViatura = Viatura.idViatura			
INNER JOIN	Motorista
ON
Motorista.idMotorista = Viagem.idMotorista
WHERE idViatura = idViatura
AND agendarViagem = CURRENT_TIMESTAMP();
END


Obs: Você pode adicionar mais colunas como retorno de dados e até outros parâmetros de entrada, vai depender da sua lógica, pois existe outras maneiras de chegar no mesmo resultado.


Olá Gilis, excelente até aqui entendi perfeitamente a lógica meu problemas agora é como inserir os todos os demais dados na tabela viagem após essa validação acima.
GOSTEI 0
Maurício Kalfelz

Maurício Kalfelz

02/06/2018

Se for criar uma procedure no MySQL ficará parecida conforme o código abaixo:


CREATE DEFINER=`root`@`localhost` PROCEDURE `Disponibilidade_Viagem`(
  IN idViatura INT,
  IN agendarViagem DATETIME
)
BEGIN
SELECT	Motorista,
		Carro,
		Destino,
		inicioViagem,
		terminoViagem,
		(IF( 
				inicioViagem = agendarViagem,
				'Viagem indisponível',
				'Viagem disponível'
           )
		) AS Viagem,
        (IF(
				idViatura = idViatura,
                'Carro Indisponível',
                'Carro Disponpivel'
			)
        ) AS Carro
FROM Viatura
INNER JOIN Viagem
ON
Viagem.idViatura = Viatura.idViatura			
INNER JOIN	Motorista
ON
Motorista.idMotorista = Viagem.idMotorista
WHERE idViatura = idViatura
AND agendarViagem = CURRENT_TIMESTAMP();
END


Obs: Você pode adicionar mais colunas como retorno de dados e até outros parâmetros de entrada, vai depender da sua lógica, pois existe outras maneiras de chegar no mesmo resultado.


Olá Amigo com sua dica criei essa aqui, ele mostra quando está diponivel ou não, a data, motorista e a viatura, porém ele faz o INSERT de qualquer modo, queria saber como não deixar ele fazer o INSERT caso alguma das opções estivesse indisponivel

CREATE  PROCEDURE TESTE(
	  IN IDVIAGENS INT ,
	  IN CIDADE VARCHAR(25) ,
	  IN DATAHORAINICIO DATETIME ,
	  IN DATAHORAFINAL DATETIME ,
	  IN COR VARCHAR(15) ,
	  IN CEP CHAR(9) ,
	  IN RUA VARCHAR(30),
	  IN NUMERO VARCHAR(8) ,
	  IN BAIRRO VARCHAR(25) ,
	  IN ESTADO CHAR(2) ,
	  IN DESCRICAO VARCHAR(200),
	  IN ID_DADOSPESSOAS INT,
	  IN ID_MOTORISTA INT,
	  IN ID_VIATURA INT

	)
	BEGIN
	SELECT  VIAGENS.ID_MOTORISTA,
	        VIAGENS.ID_VIATURA,
	        VIAGENS.DATAHORAINICIO,
	        VIAGENS.DATAHORAFINAL,
	        (IF( 
	                DATAHORAINICIO > DATAHORAFINAL AND DATAHORAFINAL < DATAHORAINICIO,
	                'Viagem indisponível',
	                'Viagem disponível'
	           )
	        ) AS Viagem,
	        (IF(
	                id_Viatura = id_Viatura,
	                'Carro Indisponível',
	                'Carro Disponpivel'
	            )
	        ) AS Carro,
	        (IF(
	                ID_MOTORISTA = ID_MOTORISTA,
	                'Motorista Indisponível',
	                'Motorista Disponpivel'
	            )
	        ) AS MOTORISTA

	FROM VIATURA
	INNER JOIN VIAGENS
	ON
	VIAGENS.ID_VIATURA = VIATURA.IDVIATURA           
	INNER JOIN  MOTORISTA
	ON
	VIAGENS.ID_MOTORISTA = MOTORISTA.IDMOTORISTA
	WHERE ID_VIATURA = IDVIATURA
	AND NOT (DATAHORAINICIO > DATAHORAFINAL)
	AND NOT (DATAHORAFINAL < DATAHORAINICIO);


	INSERT INTO VIAGENS (IDVIAGENS, CIDADE, DATAHORAINICIO, DATAHORAFINAL, COR, CEP, RUA, NUMERO, BAIRRO,
		ESTADO, DESCRICAO, ID_DADOSPESSOAS, ID_MOTORISTA, ID_VIATURA) VALUES (NULL, CIDADE, DATAHORAINICIO, DATAHORAFINAL, COR, CEP, RUA, NUMERO, BAIRRO,
		ESTADO, DESCRICAO, ID_DADOSPESSOAS, ID_MOTORISTA, ID_VIATURA);

	END
	$
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

02/06/2018

avalie a condicão antes do INSERT.
se fosse no SQL Server, seria algo assim:

if EXISTS(
    SELECT  VIAGENS.ID_MOTORISTA,
            VIAGENS.ID_VIATURA,
            VIAGENS.DATAHORAINICIO,
            VIAGENS.DATAHORAFINAL,
            (IF( 
                    DATAHORAINICIO > DATAHORAFINAL AND DATAHORAFINAL < DATAHORAINICIO,
                    'Viagem indisponível',
                    'Viagem disponível'
               )
            ) AS Viagem,
            (IF(
                    id_Viatura = id_Viatura,
                    'Carro Indisponível',
                    'Carro Disponpivel'
                )
            ) AS Carro,
            (IF(
                    ID_MOTORISTA = ID_MOTORISTA,
                    'Motorista Indisponível',
                    'Motorista Disponpivel'
                )
            ) AS MOTORISTA
 
    FROM VIATURA
    INNER JOIN VIAGENS
    ON
    VIAGENS.ID_VIATURA = VIATURA.IDVIATURA           
    INNER JOIN  MOTORISTA
    ON
    VIAGENS.ID_MOTORISTA = MOTORISTA.IDMOTORISTA
    WHERE ID_VIATURA = IDVIATURA
    AND NOT (DATAHORAINICIO > DATAHORAFINAL)
    AND NOT (DATAHORAFINAL < DATAHORAINICIO) )
then
    INSERT INTO VIAGENS (IDVIAGENS, CIDADE, DATAHORAINICIO, DATAHORAFINAL, COR, CEP, RUA, NUMERO, BAIRRO,
        ESTADO, DESCRICAO, ID_DADOSPESSOAS, ID_MOTORISTA, ID_VIATURA) VALUES (NULL, CIDADE, DATAHORAINICIO, DATAHORAFINAL, COR, CEP, RUA, NUMERO, BAIRRO,
        ESTADO, DESCRICAO, ID_DADOSPESSOAS, ID_MOTORISTA, ID_VIATURA);


Se você quiser apresentar os dados (como já fazia) precisa ser feito de outra forma.

Nos passe o maior número de informações que você puder: banco de dados, versão, etc.
GOSTEI 0
Maurício Kalfelz

Maurício Kalfelz

02/06/2018

Estou usando o MySql, mas vou tentar e já aviso amigo, obrigado
GOSTEI 0
Maurício Kalfelz

Maurício Kalfelz

02/06/2018

avalie a condicão antes do INSERT.
se fosse no SQL Server, seria algo assim:

if EXISTS(
    SELECT  VIAGENS.ID_MOTORISTA,
            VIAGENS.ID_VIATURA,
            VIAGENS.DATAHORAINICIO,
            VIAGENS.DATAHORAFINAL,
            (IF( 
                    DATAHORAINICIO > DATAHORAFINAL AND DATAHORAFINAL < DATAHORAINICIO,
                    'Viagem indisponível',
                    'Viagem disponível'
               )
            ) AS Viagem,
            (IF(
                    id_Viatura = id_Viatura,
                    'Carro Indisponível',
                    'Carro Disponpivel'
                )
            ) AS Carro,
            (IF(
                    ID_MOTORISTA = ID_MOTORISTA,
                    'Motorista Indisponível',
                    'Motorista Disponpivel'
                )
            ) AS MOTORISTA
 
    FROM VIATURA
    INNER JOIN VIAGENS
    ON
    VIAGENS.ID_VIATURA = VIATURA.IDVIATURA           
    INNER JOIN  MOTORISTA
    ON
    VIAGENS.ID_MOTORISTA = MOTORISTA.IDMOTORISTA
    WHERE ID_VIATURA = IDVIATURA
    AND NOT (DATAHORAINICIO > DATAHORAFINAL)
    AND NOT (DATAHORAFINAL < DATAHORAINICIO) )
then
    INSERT INTO VIAGENS (IDVIAGENS, CIDADE, DATAHORAINICIO, DATAHORAFINAL, COR, CEP, RUA, NUMERO, BAIRRO,
        ESTADO, DESCRICAO, ID_DADOSPESSOAS, ID_MOTORISTA, ID_VIATURA) VALUES (NULL, CIDADE, DATAHORAINICIO, DATAHORAFINAL, COR, CEP, RUA, NUMERO, BAIRRO,
        ESTADO, DESCRICAO, ID_DADOSPESSOAS, ID_MOTORISTA, ID_VIATURA);


Se você quiser apresentar os dados (como já fazia) precisa ser feito de outra forma.

Nos passe o maior número de informações que você puder: banco de dados, versão, etc.



Estou usando o MySql, mas vou tentar e já aviso amigo, obrigado
GOSTEI 0
POSTAR