CODEWITHSUNDEEP
mysqlphpmyadmin
Rob
Rob

Reputation: 57

What is the error in making trigger in this?

I made a trigger like this:

create trigger mytrigger 
before 
insert on
person4
for each row
begin
if new.salary < 5000 then
    set new.salary = 6000;
end if;
end;

Here salary is an attribute of the table person4 and I am making a trigger for this. However, it shows the error: #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 '' at line 8

Why is this happeneing? The syntax is complete correct. Why then is it happeneing?

Upvotes: 0

Views: 25

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

The trigger looks good the only thing missing was the delimiter.

I just created a triger with sample table and here it is

delimiter //
create trigger mytrigger before insert on person4
for each row
begin
 if new.salary < 5000 then
   set new.salary = 6000;
 end if;
end;//

delimiter;

Here is the mysql test

mysql> create table person4(id int, name varchar(20),salary int);
Query OK, 0 rows affected (0.16 sec)

mysql> insert into person4 values (1,'aa',6000);
Query OK, 1 row affected (0.06 sec)
mysql> select * from person4;
+------+------+--------+
| id   | name | salary |
+------+------+--------+
|    1 | aa   |   6000 |
+------+------+--------+
1 row in set (0.01 sec)

mysql> insert into person4 values (1,'bb','3000');
Query OK, 1 row affected (0.04 sec)

mysql> select * from person4;
+------+------+--------+
| id   | name | salary |
+------+------+--------+
|    1 | aa   |   6000 |
|    1 | bb   |   6000 |
+------+------+--------+
2 rows in set (0.00 sec)

Upvotes: 1

Related Questions