SQL queries in trigger statement

Question

I'm writing the code for an hotel management DB for school assignement. i need to insert a query inside a trigger and my sql server gives me this sintaxerror:

#1064 in the query SQL near 'SELECT codiceFiscale FROM IMPIEGATO WHERE ambiente=new.ambiente SET NEW.nome=U' line 5

CODE:

CREATE TRIGGER IF NOT EXISTS Gestione.ControlloExtra
BEFORE INSERT ON SPESEEXTRA
FOR EACH ROW
    BEGIN
    IF EXISTS SELECT codiceFiscale FROM IMPIEGATO WHERE ambiente=new.ambiente
        SET NEW.nome=UPPER(NEW.nome);

Are you using MySQL or MS SQL Server? (That code attempt is product specific, and won't run on both those products.) — jarlh
– jarlh, Commented Feb 5, 2018 at 11:24
I'm using MySQL. Yes the END is missing but that is not the problem, the problem is the query. — Alessandro Soffiati
– Alessandro Soffiati, Commented Feb 5, 2018 at 11:27

Gordon Linoff · Accepted Answer · 2018-02-05 11:29:13Z

2

Subqueries need parentheses:

CREATE TRIGGER IF NOT EXISTS Gestione.ControlloExtra
BEFORE INSERT ON SPESEEXTRA
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT i.codiceFiscale FROM IMPIEGATO i WHERE i.ambiente = new.ambiente)
        SET NEW.nome = UPPER(NEW.nome);
    END IF;
    . . .
END;

answered Feb 5, 2018 at 11:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

@AlessandroSoffiati . . . It cannot be exactly the same error because of the parentheses.

Collectives™ on Stack Overflow

SQL queries in trigger statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related