Examples and Notes by Week

↑ Up to Parent directory
View Create_GetCustomerLevel_proc.sql code.	Open/Save Create_GetCustomerLevel_proc.sql.
View Create_and_test_beforeDelete_trig.sql code.	Open/Save Create_and_test_beforeDelete_trig.sql.
View Create_before_employee_update_trig.sql code.	Open/Save Create_before_employee_update_trig.sql.
View Create_createEmailList_proc.sql code.	Open/Save Create_createEmailList_proc.sql.
View Create_employees_audit_table.sql code.	Open/Save Create_employees_audit_table.sql.
View Create_trigger1.sql code.	Open/Save Create_trigger1.sql.
View Create_trigger2.sql code.	Open/Save Create_trigger2.sql.
View Create_trigger3.sql code.	Open/Save Create_trigger3.sql.
View CustomerLevel_Func.sql code.	Open/Save CustomerLevel_Func.sql.
View Test_before_employee_update.sql code.	Open/Save Test_before_employee_update.sql.
View notes.txt code.	Open/Save notes.txt.
View outputEmailList.sql code.	Open/Save outputEmailList.sql.

The code for Create_and_test_beforeDelete_trig.sql is:
DROP TABLE IF EXISTS salaries; CREATE TABLE salaries ( employeeNumber INT PRIMARY KEY, validFrom DATE NOT NULL, amount DEC(12 , 2 ) NOT NULL DEFAULT 0 ); INSERT INTO salaries(employeeNumber,validFrom,amount) VALUES (1002,'2000-01-01',50000), (1056,'2000-01-01',60000), (1076,'2000-01-01',70000); DROP TABLE IF EXISTS SalaryArchives; CREATE TABLE SalaryArchives ( id INT PRIMARY KEY AUTO_INCREMENT, employeeNumber INT, validFrom DATE NOT NULL, amount DEC(12 , 2 ) NOT NULL DEFAULT 0, deletedAt TIMESTAMP DEFAULT NOW() ); DROP TRIGGER IF EXISTS before_salaries_delete; DELIMITER $$ CREATE TRIGGER before_salaries_delete BEFORE DELETE ON salaries FOR EACH ROW BEGIN INSERT INTO SalaryArchives(employeeNumber,validFrom,amount) VALUES(OLD.employeeNumber,OLD.validFrom,OLD.amount); END$$ DELIMITER ; DELETE FROM salaries WHERE employeeNumber = 1002;