一个关于MySQL触发器的问题,要求是。在一个表1执行一条update语句后,统计与该语句的id相同信息的条数,然后计算出条数的一半数目。
然后再判断该表1中id相同条目的中的state字段,是否全部相同。全部相同则更改同数据库下的另一个表2中的与其有相同id的信息(唯一)的state字段的值。由于本人对MySQL的触发器的操作不熟,所以求大神们指点。
这个是在sql2005中的写法,(应该是没有错误的,不知道写错。就是要在MySQL中实现这个触发器的功能)CREATE TRIGGER changestate
on applystate
after update
for each row
as declare @applyid int, @count int, @count2 int
select @applyid=applyid from INSERTED
SELECT @count=count(*) from y.applystate where applystate.applyid = @applyid
select @count2=count(*) from y.applystate where applystate.applyid = @applyid and state=1
if @count2>=@count1
print 'ok'
然后再判断该表1中id相同条目的中的state字段,是否全部相同。全部相同则更改同数据库下的另一个表2中的与其有相同id的信息(唯一)的state字段的值。由于本人对MySQL的触发器的操作不熟,所以求大神们指点。
这个是在sql2005中的写法,(应该是没有错误的,不知道写错。就是要在MySQL中实现这个触发器的功能)CREATE TRIGGER changestate
on applystate
after update
for each row
as declare @applyid int, @count int, @count2 int
select @applyid=applyid from INSERTED
SELECT @count=count(*) from y.applystate where applystate.applyid = @applyid
select @count2=count(*) from y.applystate where applystate.applyid = @applyid and state=1
if @count2>=@count1
print 'ok'
FOR EACH ROW
BEGINdeclare applyid int;
declare count1 int;
declare count2 int;
set applyid=(select applyid from applystate);
set count1=(select count(*) from applystate where applyid = applyid);
set count2=(select count(*) from applystate where applyid = applyid and applystate.state=1);if count2>=count1 THEN
update app_menu set name='12222' where id=1;
END IF ;END
FOR EACH ROW BEGIN
declare v_applyid int;
declare v_count int;
declare v_ount2 int;
SELECT count(*) into v_count from y.applystate where applystate.applyid = new.applyid;
select count(*) into v_ount2 from y.applystate where applystate.applyid = new.applyid and state=1;
END;
|delimiter ;
delimiter $$
CREATE TRIGGER changestate after update ON applystate
FOR EACH ROW BEGIN
declare v_applyid int;
declare v_count int;
declare v_ount2 int;
SELECT count(*) into v_count from y.applystate where applystate.applyid = new.applyid;
select count(*) into v_ount2 from y.applystate where applystate.applyid = new.applyid and state=1;
if v_count=v_count2 then
你的操作;
end if;
END$$
delimiter ;