Declaring variables in PLSQL triggers in mysql

Question

I am writing PLSQL trigger for MySQL database. I am trying to declare variables . So i am writing a declare block in the trigger. Here is my following code

CREATE TRIGGER leave_approve_trigger 
AFTER UPDATE ON leave_status
FOR EACH ROW
DECLARE    //Syntax error
current_id integer;
BEGIN
if NEW.status == 'APPROVED'
THEN
select id into current_id from leave_request_table;
insert into update_table values(current_id);
ENDIF;
END;

The error I get is syntax error for mysql version 5.5.0. Can we actually declare variables. Thanks in advance

PL/SQL is the scripting language for the Oracle database. It will not work with MySQL. Here is a reference to the MySQL create trigger syntax: dev.mysql.com/doc/refman/5.7/en/create-trigger.html. — Gordon Linoff
– Gordon Linoff, Commented Sep 28, 2014 at 13:24
There is no PL/SQL in MySQL. Plus your code is invalid for PL/SQL as well, because the comparison operator is = not == — user330315
– user330315, Commented Sep 28, 2014 at 13:47

Ivan Cachicatari · Accepted Answer · 2014-09-28 13:25:48Z

1

The procedure syntax in a little different in MySQL, you must change some lines:

Declare block must be after begin of code.
the == operator but chanted to =
ENDIF; must change to END IF;
All block must begin with changing the delimiter for statements, and at the end restore it.
If a field name is a reserved word it can be enclosed by ` to avoid syntax error.

Here is the code:

DELIMITER $$

CREATE TRIGGER leave_approve_trigger 
AFTER UPDATE ON leave_status
FOR EACH ROW
BEGIN
    DECLARE current_id int;

    if NEW.`status` = 'APPROVED' THEN
        select id into current_id from leave_request_table;
        insert into update_table values(current_id);
    END IF;

END$$

DELIMITER ;

answered Sep 28, 2014 at 13:25

Ivan Cachicatari

4,3143 gold badges24 silver badges41 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2014-09-28 13:27:54Z

1

I think the MySQL version would be more like this:

delimiter $$

CREATE TRIGGER leave_approve_trigger AFTER UPDATE ON leave_status
FOR EACH ROW
BEGIN
    if NEW.status = 'APPROVED' THEN
        insert into update_table 
             select id
             from leave_request_table;
    END IF;
END;

delimiter ;

By the way, you don't need the variable in either Oracle or MySQL. Just use insert . . . select.

I would think that you would also want to match to a specific row in the leave_request_table, but your trigger doesn't do that.

answered Sep 28, 2014 at 13:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Declaring variables in PLSQL triggers in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related