triggers.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


delimiter //

-- tr - abbr trigger
-- i - insert
-- u - update
-- d - delete


drop trigger if exists tr_i_submodule//
create trigger tr_i_submodule
    before insert on submodules for each row
begin
    declare vDisciplineID int default 0;
    declare vAccumType, vChangeMax boolean default false;
    declare vModuleType enum('regular','exam', 'bonus', 'extra') default 'extra';

    -- todo: check repeated bonus
    select modules.Type into vModuleType
        from modules
        where modules.ID = new.ModuleID
        limit 1;

    -- todo: in operator
    if (vModuleType <=> 'regular' or vModuleType <=> 'exam') then begin
        set vAccumType = (not vModuleType <=> 'exam');

        -- todo: extract foo
        set vDisciplineID = (
            select disciplines.ID
            from modules
                inner join disciplines on disciplines.ID = modules.DisciplineID
            where modules.ID = new.ModuleID
            limit 1
        );

        set vChangeMax = vAccumType || new.OrderNum <=> 1;

        update disciplines
        set     disciplines.CurRate = disciplines.CurRate + if(new.isUsed, new.MaxRate, 0),
                disciplines.MaxRate = disciplines.MaxRate + if(vChangeMax, new.MaxRate, 0)
        where disciplines.ID = vDisciplineID
        limit 1;
    end; end if;
end//

drop trigger if exists tr_d_submodule//
create trigger tr_d_submodule
    before delete on submodules for each row
begin
    declare vDisciplineID int default 0;
    declare vAccumType, vChangeMax boolean default false;
    declare vModuleType enum('regular','exam', 'bonus', 'extra') default 'extra';

    -- todo: check repeated bonus
    select modules.Type into vModuleType
        from modules
        where modules.ID = old.ModuleID
        limit 1;


    if (vModuleType <=> 'regular' or vModuleType <=> 'exam') then begin
        set vAccumType = (not vModuleType <=> 'exam');

        -- todo: extract foo
        set vDisciplineID = (
            select disciplines.ID
            from modules
                inner join disciplines on disciplines.ID = modules.DisciplineID
            where modules.ID = old.ModuleID
            limit 1
        );

        set vChangeMax = vAccumType || old.OrderNum <=> 1;

        update disciplines
        set     disciplines.CurRate = disciplines.CurRate - if(old.isUsed, old.MaxRate, 0),
                disciplines.MaxRate = disciplines.MaxRate - if(vChangeMax, old.MaxRate, 0)
        where disciplines.ID = vDisciplineID
        limit 1;
    end; end if;

end//

drop trigger if exists tr_u_submodule//
create trigger tr_u_submodule
    before update on submodules for each row
begin
    declare vDisciplineID int default 0;
    declare vAccumType, vChangeMaxOld, vChangeMaxNew boolean default false;
    declare vModuleType enum('regular','exam', 'bonus', 'extra') default 'extra';

    if old.ModuleID != new.ModuleID then
        signal sqlstate '45000';
    end if;

    -- todo: check repeated bonus
    select modules.Type into vModuleType
        from modules
        where modules.ID = new.ModuleID
        limit 1;


    if (vModuleType <=> 'regular' or vModuleType <=> 'exam') then begin
        set vAccumType = (not vModuleType <=> 'exam');

        -- todo: extract foo
        set vDisciplineID = (
            select disciplines.ID
            from modules
                inner join disciplines on disciplines.ID = modules.DisciplineID
            where modules.ID = old.ModuleID
            limit 1
        );

        set vChangeMaxOld = vAccumType || old.OrderNum <=> 1;
        set vChangeMaxNew = vAccumType || new.OrderNum <=> 1;

        update disciplines
        set     disciplines.CurRate = disciplines.CurRate
                    - if(old.isUsed, old.MaxRate, 0)
                    + if(new.isUsed, new.MaxRate, 0),
                disciplines.MaxRate = disciplines.MaxRate
                    - if(vChangeMaxOld, old.MaxRate, 0)
                    + if(vChangeMaxNew, new.MaxRate, 0)
        where disciplines.ID = vDisciplineID
        limit 1;
    end; end if;

end//


delimiter ;