mysql 5.5命令_mysql 执行报错 如下命令执行报错 mysql版本 5.5.18

DROPTRIGGERIFEXISTS`elearning`.`user_register`;CREATEDEFINER='root'@'localhost'TRIGGER`elearning`.`user_register`AFTERINSERTON`elearning`.`r_register_user_wait`FOREACHROW...

DROP TRIGGER IF EXISTS `elearning`.`user_register`;

CREATE DEFINER='root'@'localhost' TRIGGER `elearning`.`user_register` AFTER INSERT ON `elearning`.`r_register_user_wait`

FOR EACH ROW begin

declare max_number int DEFAULT 0; -- 报名最大允许人数

declare user_count int DEFAULT 0; -- 报名已成功的人数

declare enable_wait char(1); -- 报名是否开启等待

declare enable_approval char(1); -- 报名是否开启审核

declare reg_status char(1) DEFAULT 'S'; -- 默认状态为报名成功

declare reg_count int DEFAULT 0; -- 当前人是否已报过名，0未报名，1已报名

-- text number;

-- PRAGMA AUTONOMOUS_TRANSACTION;

-- 查询报名信息

select rr.max_number, rr.enable_wait, rr.enable_approval

into max_number, enable_wait, enable_approval

from r_register rr

where rr.id = new.register_id;

-- 如果需要审批，则不用插入报名人员正式表，不需要审批时进行判断

if enable_approval != 'Y' then

-- 查询当前已报名人数

select count(1)

into user_count

from r_register_user rru

where rru.is_deleted = 'N'

and rru.status = 'S'

and rru.register_id = new.register_id;

-- 判断是否有剩余空位

if max_number = 0 or max_number > user_count then

-- 查询当前人是否已报过名

select count(1)

into reg_count

from r_register_user rru

where rru.is_deleted = 'N'

and rru.status = 'S'

and rru.user_id = new.user_id

and rru.register_id = new.register_id;

if reg_count = 0 then

-- 设置插入状态，是否需要审批

-- if enable_approval = 'Y' then

-- reg_status := 'W';

-- end if;

-- 插入报名人员正式表

insert into r_register_user

(id,

register_id,

user_id,

register_time,

type,

status,

create_by,

create_date,

last_update_by,

last_update_date)

values

(nextval('register_user_seq'),

new.register_id,

new.user_id,

new.register_time,

'O',

reg_status,

new.create_by,

new.create_date,

new.last_update_by,

new.last_update_date);

end if;

end if;

-- dbms_output.put_line('----------'||:new.id);

-- select count(1) into text from r_register_user_wait where id = :new.id;

-- dbms_output.put_line('++++++++++'||text);

-- 插入报名表成功后，删除等待表中该用户数据

-- delete from r_register_user_wait where id = :new.id;

-- delete_register_user_wait(:new.id);

-- commit;

-- else

-- 如果报名人数已满，如果报名没有开启等待，则删除等待表中该用户数据

-- if enable_wait = 'N' then

-- delete from r_register_user_wait where id = :new.id;

-- end if;

end if;

-- commit;

end;

错误

[SQL]

CREATE DEFINER='root'@'localhost' TRIGGER `elearning`.`user_register` AFTER INSERT ON `elearning`.`r_register_user_wait`

FOR EACH ROW begin

declare max_number int DEFAULT 0;

[Err] 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 3

展开