Compilation Error in MYSQL trigger?

Question

Table structure is:

mysql> DESC groups;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| PKey         | varchar(64)  | NO   | PRI | NULL    |       |
| group_name   | varchar(64)  | YES  |     | NULL    |       |
| Region       | varchar(128) | NO   |     | NULL    |       |
| Role         | varchar(128) | NO   |     | NULL    |       |
| parent_group | varchar(64)  | NO   | MUL | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

When i am executing this Trigger , i'm having a compilation error

DELIMITER $$
CREATE
    TRIGGER `group_before_delete` BEFORE DELETE
    ON `groups`
    FOR EACH ROW BEGIN
    IF old.parent_group=old.PKey THEN
        UPDATE `Error: deletion RootGroup is prohibited!`;
    ELSE              
        UPDATE groups
        SET parent_group=old.parent_group
        WHERE parent_group=old.Pkey; 
    END IF;
    END$$
DELIMITER ;

ERROR 1064 (42000): 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 ';
ELSE
t_group=old.parent_group
t_group=old.PKey;
END IF;' at line 6
mysql> DELIMITER ;

Can you tell me what i'm missing here ??

Jocelyn · Answer 1 · 2012-11-20T18:12:20.310

2

You get an error in your code because the syntax of your UPDATE statement is not valid.

The links you give are 4 and 5 years old! Since the SIGNAL statement is available in MySQL since version 5.5.0 (released 3 years ago), this is really not a good idea to use the hacks described in these 2 webpages. Instead, use the SIGNAL statement.

Note: From the comments we learn that the OP is not using MySQL 5.5, so SIGNAL is not available.

edited Nov 20 '12 at 18:12

answered Nov 20 '12 at 17:23

Jocelyn

11,209
10
43
60

I am working with MySQL versions prior to 5.5. So signal handling not possible..thanks To answer! – Grijesh Chauhan Nov 20 '12 at 17:36
OK. Next time remember (when relevant) to include the version of your MySQL Server in your question. – Jocelyn Nov 20 '12 at 17:40

score 1 · Accepted Answer · edited Nov 20 '12 at 17:24

1

In your IF statement, the following is not a valid SQL statement:

UPDATE `Error: deletion RootGroup is prohibited!`;

This should be this:

IF old.parent_group=old.PKey THEN
    UPDATE `Error: deletion RootGroup is prohibited!` set x=1;
ELSE              
    UPDATE groups
    SET parent_group=old.parent_group
    WHERE parent_group=old.PKey; 
END IF;

I have never done things this way. It is a bit of an ugly way of doing it. But if it works, what the heck.

edited Nov 20 '12 at 17:24

Grijesh Chauhan

57,103
20
141
208

answered Nov 20 '12 at 17:08

Tom

6,593
3
21
42

I learn it from here: http://www.devshed.com/c/a/MySQL/Using-the-SIGNAL-Statement-for-Error-Handling/1/ – Grijesh Chauhan Nov 20 '12 at 17:09
Same is here: http://stackoverflow.com/questions/24/throw-an-error-in-a-mysql-trigger in Mr. el.atomo's answer – Grijesh Chauhan Nov 20 '12 at 17:10
You didn't copy and paste too carefully. I updated my answer. – Tom Nov 20 '12 at 17:14
Thanks! ..But Why `SET x=1` ? We have not defined `x` ? – Grijesh Chauhan Nov 20 '12 at 17:20
1

It's just to make a valid SQL statement. Basically it is just designed to throw an error. The following table does not exist: `Error: deletion RootGroup is prohibited!`, 'x' would be a field within that table, but obviously it doesn't exist either. You just need that element to make a valid SQL statement. Such as: UPDATE SET = 1; – Tom Nov 20 '12 at 17:27
Rely now I can understood How can I throw Exception in SQL... Excellent TOM..Thanks! – Grijesh Chauhan Nov 20 '12 at 17:31
1

@user1705796 This is really not the right way to do it. Read my answer. – Jocelyn Nov 20 '12 at 17:35
@Jocelyn: But I am working MySQL versions prior to 5.5. So signal handling not possible... – Grijesh Chauhan Nov 20 '12 at 17:37
This is a hack, so if possible use Jocelyn's solution. Although I guess it wouldn't work with a MySQL version < 5.5.0. – Tom Nov 20 '12 at 17:43

Compilation Error in MYSQL trigger?

2 Answers2