Syntax error in creating a trigger in MYSQL

Question

I wrote a trigger which get trigered after update statement of table1. If there is no row in table1 based on the conditions, then update table2.

CREATE TRIGGER trigger1 
    AFTER UPDATE ON my_database.table1
FOR EACH ROW
BEGIN
DECLARE cnt int;
IF new.column1 = 1 THEN
   SELECT COUNT(1) INTO @cnt FROM my_database.table1 
   WHERE userID = 1 AND isDeleted = 0;
IF cnt = 0 THEN
   UPDATE my_database.table2 
   SET isDeleted = 1 
   WHERE userSeqID = new.userID;
END IF

It gives me error i nline number 4, I couldn't understand the problem

Devart · Accepted Answer · 2013-09-11 14:06:56Z

1

Every IF statement has to ended with END IF clause. Try this statement -

DELIMITER $$

CREATE TRIGGER trigger1
  AFTER UPDATE
  ON my_database.table1
  FOR EACH ROW
BEGIN
  DECLARE cnt int;
  IF NEW.column1 = 1 THEN
    SELECT COUNT(1) INTO cnt FROM my_database.table1 WHERE userID = 1 AND isDeleted = 0;

    IF cnt = 0 THEN
      UPDATE my_database.table2 SET isDeleted = 1 WHERE userSeqID = NEW.userID;
    END IF;
  END IF;
END$$

DELIMITER ;

edited Sep 11, 2013 at 14:06

answered Sep 11, 2013 at 13:46

Devart

120k23 gold badges172 silver badges189 bronze badges

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

5 Comments

R Sumesh Manjunath Over a year ago

I want the second if statement inside the first if after the select statement.

Devart Over a year ago

I have changed my answer.

R Sumesh Manjunath Over a year ago

Still it gives me same error at declare statement. Kindly help me out.

Devart Over a year ago

Note that you set @cnt variable in first SELECT, but use cnt variable.

Devart Over a year ago

I have no problems with this query. Pay attention at using DELIMITERS also. But firstly fix problems inside the trigger.

Jeremy Smyth · Accepted Answer · 2013-09-11 13:50:45Z

1

The error on line 4 is because you're not using a delimiter.

Try this:

DELIMITER //
create trigger trigger1 after update on my_database.table1
for each row
begin
declare cnt int;
...
end if;
END;//

DELIMITER ;

You'll also need an END IF on the line after the select statement, and a terminal END; to match your begin, as I have in my example.

answered Sep 11, 2013 at 13:50

Jeremy Smyth

23.6k2 gold badges57 silver badges66 bronze badges

Collectives™ on Stack Overflow

Syntax error in creating a trigger in MYSQL

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related