MYSQL触发器示例

delimiter $$
--DROP TRIGGER  /*!50032 IF EXISTS */ `sum_sale`$$
create trigger sum_sale after insert on cu_order for each row 
begin 
		DECLARE  sales decimal(10,2) DEFAULT (SELECT SUM( order_amount ) as a FROM `cu_order` WHERE stores=new.stores AND state >1);
		DECLARE  no_more_user INT DEFAULT 0;
		DECLARE  user_id INT(11);
		DECLARE  cur_user  CURSOR FOR  SELECT id FROM employ_user WHERE  user_state=1 AND ( dept_id=new.stores or dept_id in (SELECT id FROM `employ_dept` WHERE `company_id`=1 AND `type`=1 AND `state`=1) ) ;  /*First: Delcare a cursor,首先这里对游标进行定义*/ 
		DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET  no_more_user = 1; /*when "not found" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/
		IF sales > 1000000 THEN
			OPEN  cur_user; /*Second: Open the cursor 接着使用OPEN打开游标*/ 
			FETCH  cur_user INTO user_id; /*Third: now you can Fetch the row 把第一行数据写入变量中,游标也随之指向了