Error "1241 - Operand should contain 1 column" trigger that checks for duplicates

Question

Error "1241 - Operand should contain 1 column" trigger that checks for duplicates

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I'm doing this trigger to check if dados_id and referente_obs exists in the bank table

But it returns this error:

1241 - Operand should contain 1 column (s);

BEGIN
    IF (NEW.id_dados and NEW.referente_obs) not in (
        select A.id_dados,A.referente_obs
        From banco as A  
        where (NEW.id_dados = A.id_dados and NEW.referente_obs = A.referente_obs)
    ) THEN
       CALL 'Insert Not Allowed.';

    END IF;
END

mysql sql trigger

asked by anonymous 14.09.2017 / 19:25

2 answers

Connection only works with manually set values How to activate certain CSS from a certain scroll?

score 0 · Answer 1

BEFORE INSERT ON banco FOR EACH ROW BEGIN IF (NEW.id_dados, NEW.referente_obs) not in ( ( select A.id_dados From banco as A where NEW.id_dados = A.id_dados ), ( select A.referente_obs From banco as A where NEW.referente_obs = A.referente_obs )) THEN CALL Insert Not Allowed.; END IF; END

score 0 · Answer 2

You can not do a SELECT nested with two columns. You must separate it into two SELECTs: one for A.id_dates and one for A.re. Here's how:

IF (NEW.id_dados and NEW.referente_obs) not in (
            select A.id_dados, A.referente_obs
            From banco as A  
            where (NEW.id_dados = A.id_dados and NEW.referente_obs = A.referente_obs)
)

Try this:

IF (NEW.id_dados NOT IN 
(
  SELECT A.id_dados
  FROM banco AS A  
  WHERE NEW.id_dados = A.id_dados
)) AND ((NEW.referente_obs) NOT IN
(
  SELECT A.referente_obs
  FROM banco AS A  
  WHERE NEW.referente_obs = A.referente_obs
))