INSERT_NOTIFY¶

BEGIN  
    SELECT UserName INTO @NewUserName FROM userprofile WHERE UserId = NEW.UserId;  
        SELECT RoleId INTO @RoleId_User FROM webpages_roles WHERE RoleName = '用户' LIMIT 1;  
        SELECT RoleId INTO @RoleId_StationManager FROM webpages_roles WHERE RoleName = '站长' LIMIT 1; 

    IF NEW.RoleId = @RoleId_User THEN
              SELECT count(*) INTO @IsExist FROM 表_用户 WHERE 用户_用户名 = @NewUserName; 
                IF @IsExist = 0 THEN
                    INSERT INTO 表_用户 (用户_用户名, 用户_用户ID, 用户ID, 注册时间) VALUES (@NewUserName, NEW.UserId, MD5(@NewUserName), NOW());  
                END IF;  
    END IF;  

        IF NEW.RoleId = @RoleId_StationManager THEN  
            SELECT count(*) INTO @IsExist FROM 表_站长 WHERE 站长_用户名 = @NewUserName;
                IF @IsExist = 0 THEN
            INSERT INTO 表_站长 (站长_用户名, 站长_用户ID, 注册时间) VALUES (@NewUserName, NEW.UserId, NOW());  
                END IF;  
    END IF;  
END

delete_notify¶

BEGIN  
        SELECT RoleId INTO @RoleId_User FROM webpages_roles WHERE RoleName = '用户' LIMIT 1;  
        SELECT RoleId INTO @RoleId_StationManager FROM webpages_roles WHERE RoleName = '站长' LIMIT 1; 

    IF OLD.RoleId = @RoleId_User THEN
              DELETE FROM 表_用户 WHERE 用户_用户ID = OLD.UserId; 
    END IF;  

    IF OLD.RoleId = @RoleId_StationManager THEN
              DELETE FROM 表_站长 WHERE 站长_用户ID = OLD.UserId; 
    END IF;  
END

update_notify¶

BEGIN  
    SELECT UserName INTO @NewUserName FROM userprofile WHERE UserId = NEW.UserId;  
        SELECT RoleId INTO @RoleId_User FROM webpages_roles WHERE RoleName = '用户' LIMIT 1;  
        SELECT RoleId INTO @RoleId_StationManager FROM webpages_roles WHERE RoleName = '站长' LIMIT 1; 

        IF OLD.RoleId = @RoleId_User THEN
              DELETE FROM 表_用户 WHERE 用户_用户ID = OLD.UserId; 
    END IF;  

        IF OLD.RoleId = @RoleId_StationManager THEN
              DELETE FROM 表_站长 WHERE 站长_用户ID = OLD.UserId; 
    END IF;  

    IF NEW.RoleId = @RoleId_User THEN
              SELECT count(*) INTO @IsExist FROM 表_用户 WHERE 用户_用户名 = @NewUserName; 
                IF @IsExist = 0 THEN
                    INSERT INTO 表_用户 (用户_用户名, 用户_用户ID, 用户ID, 注册时间) VALUES (@NewUserName, NEW.UserId, MD5(@NewUserName), NOW());  
                END IF;  
    END IF;  

        IF NEW.RoleId = @RoleId_StationManager THEN  
            SELECT count(*) INTO @IsExist FROM 表_站长 WHERE 站长_用户名 = @NewUserName;
                IF @IsExist = 0 THEN
            INSERT INTO 表_站长 (站长_用户名, 站长_用户ID, 注册时间) VALUES (@NewUserName, NEW.UserId, NOW());  
                END IF;  
    END IF;  
END