How to check null fields in sql?

Question

How to check null fields in sql?

Navigation

#1 by (0 votes)

0

The trigger I have to do:

A user can only assign a rating to an offer if he has purchased it (reserve with estado = pago ). On the other hand, one of the two rating and comment attributes has to be NOT NULL .

What I have done:

create trigger T10 on CLASSIFICACOES instead of insertas begin

--inserir apenas se tiver adquirido um produto/serviço (reserva de estado = pago)
insert into CLASSIFICACOES
    select 
      i.ID_UTILIZADOR, 
      i.ID_OFERTA, 
      i.ID_CLASSIFICACAO, 
      i.DATA_DA_CLASSIFICACAO, 
      i.CLASSIFICACAO, 
      i.COMENTARIO
    from inserted i
    where exists ( 
      select 
        [ESTADO_DA_RESERVA]
      from RESERVAS r, 
      where 
        r.[ESTADO_DA_RESERVA] = 'Pago'
    )

-- verificar se tem ou comentário ou classificação 'NULL'
-- (aqui agora quero verificar se algum dos campos é 'NULL' para não poder inserir caso o sejam)

Is the right way to think to create this trigger? If yes, how do I check if the fields are NULL ?

Tables that matter:

CLASSIFICACOES (
  id_utilizador (pk), 
  id_oferta (pk), 
  id_classificacao (pk), 
  data_da_classificacao, 
  classificacao, 
  comentario
)

RESERVAS (
  id_reserva (pk), 
  id_meio_pagamento (fk), 
  data_de_reserva, 
  data_de_pagamento, 
  estado_da_reserva, 
  total
)

UTILIZADORES(
  id_utilizador (pk), 
  id_empresa (fk), 
  login, 
  email, 
  primeironome, 
  ultimonome, 
  nif, 
  bloqueado
)

sql sql-server null

asked by anonymous 08.12.2017 / 02:00

1 answer

Flash animations converted to HTML5 do not fit in div Break apart, taking part of the text

score 0 · Answer 1

You must have two FKs in the reservas table:

One that points to utilizadores , otherwise you do not know who made the reservation
One that points to ofertas , otherwise you do not know which offer was reserved

After that, the where in your trigger can be easily changed to make the consistency:

where exists ( 
  select 
    [ESTADO_DA_RESERVA]
  from RESERVAS r, 
  where 
    r.[ESTADO_DA_RESERVA] = 'Pago'
   and r.id_utilizador = i.id_utilizador
   and r.id_oferta = i.id_oferta
)

The reservas table seems to represent an N-to-N relationship between usuarios and ofertas , so you need to have these two FKs.