ADS_5_Trigger_cursor

MYSQL

CREATE TABLE employee (
 emp_id INTEGER PRIMARY KEY,
 emp_name VARCHAR(20),
 salary DECIMAL
);
INSERT INTO employee VALUES (1, 'John', 40000);
INSERT INTO employee VALUES (2, 'Doe', 50000);
CREATE TABLE salary_history (
 emp_id INTEGER,
 old_salary DECIMAL
);
DELIMITER //
CREATE TRIGGER before_salary_update
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
  INSERT INTO salary_history (emp_id, old_salary) VALUES (OLD.emp_id, OLD.salary);
END//
DELIMITER ;
drop trigger before_salary_update;

UPDATE employee SET salary = salary + 10000 WHERE emp_id = 1;
UPDATE employee SET salary = salary + 5000 WHERE emp_id = 2;
SELECT * FROM employee;
SELECT * FROM salary_history;

--CURSOR

CREATE TABLE employee_backup (
 emp_id INT,
 emp_name VARCHAR(20),
 salary DECIMAL
);

DELIMITER //
CREATE PROCEDURE backup_employee()
BEGIN
   DECLARE done INT DEFAULT FALSE;
   DECLARE v_emp_id INT;
   DECLARE v_emp_name VARCHAR(20);
   DECLARE v_salary DECIMAL;
   -- Declare cursor
   DECLARE emp_cursor CURSOR FOR
   SELECT emp_id, emp_name, salary FROM employee;
   -- Declare exit handler
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   -- Open cursor
   OPEN emp_cursor;
   -- Fetch and insert rows
   read_loop: LOOP
      FETCH emp_cursor INTO v_emp_id, v_emp_name, v_salary;
      IF done = 1 THEN
         LEAVE read_loop;
      END IF;

      -- Insert the fetched data into the backup table
      INSERT INTO employee_backup (emp_id, emp_name, salary)
      VALUES (v_emp_id, v_emp_name, v_salary);
   END LOOP;
   -- Close cursor
   CLOSE emp_cursor;
END //
DELIMITER ;

drop procedure backup_employee;

-- Call the stored procedure
CALL backup_employee();
SELECT * FROM employee_backup;
DROP TABLE employee_backup;