My trigger is not working in MySQL

Question

I wanto to create a trigger in MySQL but it displays an error message when I run the creation code:

CREATE TRIGGER before_employee_update 
    BEFORE UPDATE ON trigger
    FOR EACH ROW BEGIN

    update users
    SET username= 'krishna',
        password= 'abc';
END

Error is:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'trigger FOR EACH ROW BEGIN INSERT INTO users SET username= 'kr' at line 2

This is the code the error points to:

BEFORE UPDATE ON trigger
                 ^^^^^^^

Not working is not a problem. Can you please show what is your actual problem? — Himanshu
– Himanshu, Commented May 31, 2013 at 7:30
i am new to mysql store procedure, i have table trigger and user — Yanks
– Yanks, Commented May 31, 2013 at 7:30
error is some thing like this: -- "1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'trigger FOR EACH ROW BEGIN INSERT INTO users SET username= 'kr' at line 2" — Yanks
– Yanks, Commented May 31, 2013 at 7:32

Devart · Accepted Answer · 2013-05-31 08:58:48Z

0

TRIGGER is reserved word in MySQL, you should quote this name -

CREATE TRIGGER before_employee_update
  BEFORE UPDATE
  ON `trigger`
  FOR EACH ROW
BEGIN
  UPDATE users SET username = 'krishna', password = 'abc';
END

As a workaround - rename table trigger.

edited May 31, 2013 at 8:58

answered May 31, 2013 at 8:29

Devart

120k23 gold badges172 silver badges189 bronze badges

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

1 Comment

Devart Over a year ago

Use DROP TRIGGER trigger_name; comamnd. Or also you can do it using free GUI tool - dbForge Studio for MySQL (express edition).

Gordon Linoff · Accepted Answer · 2013-05-31 07:31:02Z

0

The proper syntax for insert is:

insert into users(username, password)
    select 'krishna', 'abc';

Your syntax is close to an update:

update users
SET username= 'krishna',
    password= 'abc';

answered May 31, 2013 at 7:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Gordon Linoff Over a year ago

@Nirdosh . . . The operation is on a different table, so you can use either update or delete.

Yanks Over a year ago

can u write me a simple trigger so that i can follow it's syntax, because i can't execute a single trigger in mysql.

Collectives™ on Stack Overflow

My trigger is not working in MySQL

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related