My trigger is not working in MySQL

0
votes

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
                 ^^^^^^^
2
mysqltriggers
Seems like we need a more verbose description to help… - kirelagin
Not working is not a problem. Can you please show what is your actual problem? - Himanshu Jansari
i am new to mysql store procedure, i have table trigger and user - Yanks
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
when i run this code in mysql the following error occurs - Yanks

2 Answers

0
votes

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.

0
votes

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';