How to check null fields in sql?

Asked

Viewed 381 times

-1

The Trigger I have to do:

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

What I’ve been doing:

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 Rigger? If so, 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

  • You cannot enter this clause of NOT NULL in the where where you already check whether you are paid?

  • @Giovane not because they are different tables, your suggestion would be right in Where to try to create solution where to verify if there is null in this field?

  • Yeah, just run a search with JOIN. These tables relate, right?

  • Not the reserve table connects to a table of offers and that of offers is what links to one of ratings... Hence I don’t quite know how to make the querys to make this Trigger @Giovane

  • Put the structures there, just the parts that relate, I’ll help you.

  • @Giovane I edited in the reply, I hope it’s what you asked

  • Which database??

  • @Giovane thanks for the formatting of the question (still not quite sure how to put the questions well arranged

  • What do you mean? @Giovane

  • Which database is using? Mysql, Postgres, Oracle, SQL Server?

  • SQL Sever @Giovane

  • Right... how will you know if the user made the payment if the reservation does not have his ID?

  • Understanding "one of the two attributes classification and comment has to be NOT NULL" as the two can be simultaneously NOT NULL is only use OR (A IS NOT NULL OR B IS NOT NULL). If they cannot be simultaneously NOT NULL then you will need to use the XOR operator, which does not exist directly but can be rewritten with a combination of OR, AND and NOT.

Show 8 more comments

1 answer

-1

You owe two Fks on the table reservas:

  • One that points to utilizadores, otherwise you don’t know who made the reservation
  • One that points to ofertas, otherwise you don’t know which offer was reserved

After that, the where in your Rigger can be easily changed to make 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 table reservas seems to me to represent an N-to-N relationship between usuarios and ofertas, so you need to have these two Fks.

Browser other questions tagged sql sql-server null

You are not signed in. Login or sign up in order to post.