Pastein: use db_4991_07_pr; set @tot=0; select @tot; DELIMITER // CREATE TRIGGER SUB_COUNTS BEFORE INSERT ON STUDENTS FOR EACH ROW BEGIN SET @tot = @tot+ 1; END; // DELIMITER ; INSERT IN

Загрузка данных
use db_4991_07_pr;

set @tot=0;
select @tot;

DELIMITER //
CREATE TRIGGER SUB_COUNTS 
BEFORE INSERT ON STUDENTS
FOR EACH ROW
BEGIN
    SET @tot = @tot+ 1;
END;
//
DELIMITER ;

INSERT INTO STUDENTS (name, lastName, team) VALUES ('Ivan', "Petrov", 2);

select @tot;

set @y="";

DELIMITER //
CREATE TRIGGER Work_yy 
BEFORE INSERT ON WORK
FOR EACH ROW
BEGIN
	DECLARE proj_cnt INT;
    DECLARE team_cnt INT;
    SELECT COUNT(*) INTO proj_cnt FROM PROJECT WHERE shifr = PROJECT.shifr;
    SELECT COUNT(*) INTO team_cnt FROM TEAM WHERE id = TEAM.id;
    IF proj_cnt = 0 or team_cnt = 0 THEN
		SET @y = "Ошибка";
	ELSE
		SET @y = "Успешно";
	END IF;
END;
//
DELIMITER ;

INSERT INTO WORK (teamId, projectId, takt, result) VALUES (111, 111, 111,"111");
select @y;
INSERT INTO WORK (teamId, projectId, takt, result) VALUES (1, 101, 1,"Завершено");
select @y;

DELIMITER //
CREATE TRIGGER rabota
BEFORE INSERT ON WORK
FOR EACH ROW
BEGIN
	DECLARE proj_cnt INT;
    DECLARE team_cnt INT;
    SELECT COUNT(*) INTO proj_cnt FROM PROJECT WHERE shifr = PROJECT.shifr;
    SELECT COUNT(*) INTO team_cnt FROM TEAM WHERE id = TEAM.id;
    IF proj_cnt = 0 or team_cnt = 0 THEN
		SIGNAL sqlstate '45000'
        set message_text = 'ERRROR 1337 No cvyaz';
	END IF;
END;
//
DELIMITER ;

INSERT INTO WORK (teamId, projectId, takt, result) VALUES (1, 101, 1,"Завершено");
INSERT INTO WORK (teamId, projectId, takt, result) VALUES (10, 103, 1,"Завершено");

SET FOREIGN_KEY_CHECKS = 0;

DELIMITER //
CREATE TRIGGER rabota_for_student
BEFORE INSERT ON WORK
FOR EACH ROW
BEGIN
    DECLARE team_cnt INT;
    SELECT COUNT(*) INTO team_cnt FROM TEAM WHERE id = TEAM.id;
    IF team_cnt = 0 THEN
		SIGNAL sqlstate '45000'
        set message_text = 'ERRROR 1337 No cvyaz';
	END IF;
END;
//
DELIMITER ;

INSERT INTO STUDENTS (name, lastName, team) VALUES ('Alex', "Popobv", 2);

INSERT INTO STUDENTS (name, lastName, team) VALUES ('Nikita', "Popobv", 1234);

DELIMITER //
CREATE TRIGGER on_update_cascade_1
AFTER UPDATE ON PROJECT
FOR EACH ROW
BEGIN
	if OLD.shifr != NEW.shifr THEN
		UPDATE WORK
        set projectId = NEW.shifr
        WHERE projectId = OLD.shifr;
	END IF;
END;
//
DELIMITER ;

select shifr from PROJECT limit 1;

select id, projectId from WORK WHERE projectId = 101;

UPDATE PROJECT set shifr = 404 WHERE shifr = 101;

select id, projectId from WORK WHERE projectId = 101;

DELIMITER //
CREATE TRIGGER on_update_cascade_222
AFTER UPDATE ON STUDENTS
FOR EACH ROW
BEGIN
	if OLD.id != NEW.id THEN
		UPDATE STUDENTS
        set team = NEW.id
        WHERE team = OLD.id;
	END IF;
END;
//
DELIMITER ;

UPDATE TEAM set id = 22 where id = 11;
select id, name, lastName, team from STUDENTS where id = 22;

DELIMITER //
CREATE TRIGGER on_delete_cascade_111
AFTER DELETE ON WORK
FOR EACH ROW
BEGIN
    DELETE FROM TEAM WHERE id = OLD.teamId;
END;
//
DELIMITER ;

DELETE FROM WORK WHERE teamId = 1;

SELECT * FROM TEAM WHERE id = 1;

INSERT INTO TEAM (team) VALUES ('lost');

ALTER TABLE STUDENTS 
ALTER COLUMN team SET DEFAULT 100;

DELIMITER //
CREATE TRIGGER before_team_delete
BEFORE DELETE ON TEAM
FOR EACH ROW
BEGIN
    UPDATE STUDENTS 
    SET team = DEFAULT(team) 
    WHERE team = OLD.id;
END;
//
DELIMITER ;

ALTER TABLE TEAM ADD COLUMN Km VARCHAR(4);

DELIMITER //
CREATE TRIGGER triger3_61
BEFORE insert ON TEAM
FOR EACH ROW
BEGIN
	if new.team is not null and length(TRIM(new.team)) >= 1 then
		set NEW.Km = concat(
		SUbSTRING(NEW.team, 1, 1),
        SUbSTRING(new.team, -1)
		);
    else
		set new.Km = null;
	end if;
END;
//
DELIMITER ;
DROP TRIGGER triger3_6;
INSERT into TEAM (id, team , lider, mentor) values (11, "qwe", null, null);

select id , team, Km from TEAM where id = 11;
 
DELIMITER //
CREATE TRIGGER triger3_62
BEFORE insert ON userlist
FOR EACH ROW
BEGIN
	if new.pass is null or new.pass = '' then
    set New.pass = "Top_1_pass";
	end if;
END;
//
DELIMITER ;

insert into userlist (login) values ("qwe123");
insert into userlist (login) values ("get342");
select * from userlist;

CREATE TABLE log_project (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dat VARCHAR(20),                 
    time VARCHAR(20),             
    event VARCHAR(50),               
    row_numb INT                  
);

DELIMITER $$
CREATE TRIGGER trg_project_insert
AFTER INSERT ON PROJECT
FOR EACH ROW
BEGIN
    INSERT INTO log_project (dat, time, event, row_numb)
    VALUES (CURDATE(), CURTIME(), 'INSERT', id);
END$$
DELIMITER ;
DROP TRIGGER trg_project_insert;
DELIMITER $$
CREATE TRIGGER trg_project_delete
AFTER DELETE ON PROJECT
FOR EACH ROW
BEGIN
    INSERT INTO log_project (dat, time, event, row_numb)
    VALUES (CURDATE(), CURTIME(), 'DELETE', id);
END$$
DELIMITER ;


DELIMITER $$
CREATE TRIGGER trg_project_update123
AFTER update ON PROJECT
FOR EACH ROW
BEGIN
    INSERT INTO log_project (dat, time, event, row_numb, users)
    VALUES (CURDATE(), CURTIME(), 'update', id, current_user());
END$$
DELIMITER ;

ALTER TABLE log_project ADD COLUMN users VARCHAR(10);

select * from log_project;
ALTER TABLE PROJECT MODIFY topic VARCHAR(255);
INSERT INTO PROJECT (shifr, topic, project,cost) VALUES (505, "science", "top project", 1234);
update PROJECT set topic = "biology sieadasdasnce" where shifr = 505;
update PROJECT set topic = "тываываываыв sience" where shifr = 101;

SET FOREIGN_KEY_CHECKS = 0;



START TRANSACTION;

DELETE FROM WORK WHERE projectId = 102;

DELETE FROM PROJECT WHERE shifr = 102;

ROLLBACK;



START TRANSACTION;

DELETE FROM WORK WHERE projectId = 102;

DELETE FROM PROJECT WHERE shifr = 102;

COMMIT;



ALTER TABLE TEAM ADD COLUMN kol INT DEFAULT 0;



START TRANSACTION;

SET @A = (SELECT COUNT(*) FROM STUDENTS WHERE team = 1);

UPDATE TEAM SET kol = @A WHERE id = 1;

COMMIT;



DELIMITER //

CREATE PROCEDURE checkOK(

    IN Sh VARCHAR(20),

    IN Tm VARCHAR(100),

    IN Nz VARCHAR(50),

    IN dNla DATE,

    IN dOk DATE

)

BEGIN

    DECLARE rez VARCHAR(3) DEFAULT 'OK';

    DECLARE pr INT;

    START TRANSACTION;

    INSERT INTO PROJECT (shifr, topic, project, dateStart, dateFinish) VALUES (Sh, Tm, Nz, dNla, dOk);

    SELECT DATEDIFF(dateFinish, dateStart) INTO pr FROM PROJECT WHERE shifr = Sh;

    IF pr > 0 THEN

        COMMIT;

    ELSE

        ROLLBACK;

        SET rez = 'No';

    END IF;

    SELECT rez AS Result;

END //

DELIMITER ;



SET @sh = 123, @tm = 'Тестовый проект', @nz = 'Проект_А', @d1 = '2026-01-01', @d2 = '2026-12-31';

CALL checkOK(@sh, @tm, @nz, @d1, @d2);



SET @sh = 122, @tm = 'Ошибка дат', @nz = 'Проект_Б', @d1 = '2025-12-31', @d2 = '2025-01-01';

CALL checkOK(@sh, @tm, @nz, @d1, @d2);



SET GLOBAL event_scheduler = ON;



DELIMITER //

CREATE EVENT delete_old_work

ON SCHEDULE EVERY 1 DAY

DO

BEGIN

    DELETE w FROM WORK w INNER JOIN PROJECT p ON w.project = p.shifr WHERE p.dateFinish < DATE_SUB(CURRENT_DATE, INTERVAL 5 YEAR);

END //

DELIMITER ;



ALTER EVENT delete_old_work ON SCHEDULE EVERY 1 MINUTE;

SHOW EVENTS LIKE 'delete_old_work';



DELIMITER //

CREATE PROCEDURE reduce_cost_yearly()

BEGIN

    UPDATE PROJECT SET cost = cost * 0.99 WHERE dateStart <= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR);

END //



CREATE EVENT yearly_cost_update ON SCHEDULE EVERY 1 YEAR DO CALL reduce_cost_yearly() //

ALTER EVENT yearly_cost_update ON SCHEDULE EVERY 1 MINUTE //

ALTER EVENT yearly_cost_update ON SCHEDULE EVERY 1 YEAR //

DELIMITER ;



SHOW EVENTS LIKE 'yearly_cost_update';

SET SQL_SAFE_UPDATES = 0; -- Отключаем защиту

-- ALTER TABLE PROJECT ADD COLUMN atWork TINYINT(1) DEFAULT 1;
-- UPDATE PROJECT SET atWork = 1 WHERE shifr IS NOT NULL;

DELIMITER //


DROP PROCEDURE IF EXISTS delete_project_safe //
CREATE PROCEDURE delete_project_safe(IN p_shifr VARCHAR(20))
BEGIN
    DECLARE is_active TINYINT;
    START TRANSACTION;
    
    SELECT atWork INTO is_active FROM PROJECT WHERE shifr = p_shifr;
    
    IF is_active = 1 THEN
        ROLLBACK;
        SELECT 'ОШИБКА: Проект является текущим (atWork=1). Удаление отменено.' AS Message;
    ELSE
        DELETE FROM WORK WHERE project = p_shifr;
        DELETE FROM PROJECT WHERE shifr = p_shifr;
        COMMIT;
        SELECT 'Проект успешно удалён.' AS Message;
    END IF;
END //

CREATE TABLE IF NOT EXISTS USERS (
    login VARCHAR(50) PRIMARY KEY,
    pass VARCHAR(255),
    phone_home VARCHAR(20),
    phone_cell VARCHAR(20)
) //

DROP PROCEDURE IF EXISTS insert_user_safe //
CREATE PROCEDURE insert_user_safe(IN p_login VARCHAR(50), IN p_home VARCHAR(20), IN p_cell VARCHAR(20))
BEGIN
    IF p_home IS NULL AND p_cell IS NULL THEN
        SELECT 'ОШИБКА: Необходимо указать домашний или сотовый телефон.' AS Message;
    ELSE
        INSERT INTO USERS (login, phone_home, phone_cell) VALUES (p_login, p_home, p_cell);
        SELECT 'Пользователь успешно добавлен.' AS Message;
    END IF;
END //

DROP PROCEDURE IF EXISTS delete_student_safe //
CREATE PROCEDURE delete_student_safe(IN p_st_id INT)
BEGIN
    DECLARE leader_count INT;
    START TRANSACTION;
    
    SELECT COUNT(*) INTO leader_count FROM TEAM WHERE lider = p_st_id;
    
    IF leader_count > 0 THEN
        ROLLBACK;
        SELECT 'ОШИБКА: Студент является лидером команды. Удаление отменено.' AS Message;
    ELSE
        DELETE FROM STUDENT WHERE id = p_st_id;
        COMMIT;
        SELECT 'Студент успешно удалён.' AS Message;
    END IF;
END //

DELIMITER ;

SET SQL_SAFE_UPDATES = 1;
Больше возможностей при регистрации:
