Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /* II.2 */
- CREATE TRIGGER [tr_2_Salary] ON [NhanVien]
- AFTER INSERT, DELETE, UPDATE
- AS
- IF
- (
- SELECT COUNT(*) --đếm số phòng ban có lương trưởng phòng < nhân viên
- FROM
- (
- SELECT *
- FROM
- ( --lấy ra lương của từng trưởng phòng
- SELECT
- [PhongBan].[MaPh],
- [NhanVien].[Luong] AS [Head_salary]
- FROM
- [PhongBan]
- INNER JOIN [NhanVien]
- ON [PhongBan].[TrPhg] = [NhanVien].[MaNV]
- ) AS [Dept_Head_salary]
- LEFT JOIN
- ( --lấy ra lương của nhân viên theo từng phòng, không tính trưởng phòng
- SELECT
- [NhanVien].[Phg],
- [NhanVien].[Luong]
- FROM
- [NhanVien]
- INNER JOIN [PhongBan]
- ON [PhongBan].[MaPh] = [NhanVien].[Phg]
- WHERE [NhanVien].[MaNV] != [PhongBan].[TrPhg]
- ) AS [Emp_Salary_except_head]
- ON [Dept_Head_salary].[MaPh] = [Emp_Salary_except_head].[Phg]
- WHERE [Dept_Head_salary].[Head_salary] < [Emp_Salary_except_head].[Luong]
- AND (
- [Dept_Head_salary].[MaPh] = (SELECT INSERTED.[Phg] FROM INSERTED)
- OR [Dept_Head_salary].[MaPh] = (SELECT DELETED.[Phg] FROM DELETED))
- ) AS [Table_should_empty] --lấy ra danh sách phòng ban có lương trưởng phòng < nhân viên
- ) > 0
- BEGIN
- PRINT 'The salary of the head of each department must be greater than or equal to salary of all employees in this department.'
- ROLLBACK TRANSACTION
- END;
- /*
- UPDATE [NhanVien] SET [Luong] = 25000 WHERE [MaNV] = 987987987
- */
- /* II.3 */
- CREATE TRIGGER [tr_3_Salary_diff] ON [NhanVien]
- AFTER INSERT, DELETE, UPDATE
- AS
- BEGIN
- DECLARE @diff float;
- DECLARE @avg_hn float;
- DECLARE @avg_hcm float;
- SET @avg_hn =
- (
- SELECT
- AVG([NhanVien].[Luong])
- FROM
- [NhanVien]
- INNER JOIN [DiaDiem_Phg]
- ON [NhanVien].[Phg] = [DiaDiem_Phg].[MaPhg]
- WHERE [DiaDiem_Phg].[DiaDiem] = 'Ha Noi'
- )
- SET @avg_hcm =
- (
- SELECT
- AVG([NhanVien].[Luong])
- FROM
- [NhanVien]
- INNER JOIN [DiaDiem_Phg]
- ON [NhanVien].[Phg] = [DiaDiem_Phg].[MaPhg]
- WHERE [DiaDiem_Phg].[DiaDiem] = 'TP HCM'
- )
- SET @diff = @avg_hn - @avg_hcm
- IF @diff >= 10000 OR @diff <= -10000
- BEGIN
- PRINT 'The different between average salary of employees in HCM and HN must fewer than 10000'
- ROLLBACK TRANSACTION
- END;
- END;
- /*
- INSERT INTO [NhanVien]
- VALUES ('ddd', 'eee', 'fff',222222262,'1966-06-06', 'xxx', 'Nam', 9000000,333445555, 4);
- */
- /* II.4 */
- CREATE TRIGGER [tr_4_ThanNhan] ON [ThanNhan]
- AFTER INSERT, UPDATE
- AS
- IF (
- SELECT [Count_Familiars].[Familiars]
- FROM
- (
- SELECT [Ma_NVien], COUNT([Ma_NVien]) AS [Familiars]
- FROM [ThanNhan]
- GROUP BY [Ma_NVien]
- ) AS [Count_Familiars]
- INNER JOIN INSERTED
- ON INSERTED.[Ma_NVien] = [Count_Familiars].[Ma_NVien]
- ) > 5
- BEGIN
- PRINT 'Can not have more than five familiars'
- ROLLBACK TRANSACTION
- END;
- /*
- INSERT INTO [ThanNhan] VALUES (333445555,'zz', 'Nu', '1999-01-01', 'Con gai');
- INSERT INTO [ThanNhan] VALUES (333445555,'yy', 'Nu', '1999-12-12', 'Con gai');
- INSERT INTO [ThanNhan] VALUES (333445555,'xx', 'Nu', '1999-12-31', 'Con gai');
- */
- /* II.5 */
- /*
- INSERT INTO [NhanVien] VALUES ('aaa', 'aaa', 'aaa',999999999,'1966-06-06', 'xxx', 'Nu', 200000,333445555, 4);
- INSERT INTO [NhanVien] VALUES ('bbb', 'bbb', 'bbb',999999998,'1966-06-06', 'xxx', 'Nu', 200000,333445555, 4);
- INSERT INTO [NhanVien] VALUES ('ccc', 'ccc', 'ccc',999999997,'1966-06-06', 'xxx', 'Nu', 200000,333445555, 4);
- INSERT INTO [NhanVien] VALUES ('ddd', 'ddd', 'ddd',999999996,'1966-06-06', 'xxx', 'Nu', 200000,333445555, 4);
- */
- CREATE TRIGGER [tr_5_Sex_diff] ON [NhanVien]
- AFTER INSERT, DELETE, UPDATE
- AS
- BEGIN
- DECLARE @diff float =
- (
- SELECT
- CAST(( SELECT COUNT(CASE WHEN [Phai] = 'Nam' THEN 1 END) AS [NumOfMale] FROM [NhanVien]) AS FLOAT)
- /
- CAST((SELECT COUNT(CASE WHEN [Phai] = 'Nu' THEN 1 END) AS [NumOfMale] FROM [NhanVien]) AS FLOAT)
- )
- IF @diff >= 1.1 OR @diff <= CAST(10 AS FLOAT) / CAST(11 AS FLOAT)
- BEGIN
- PRINT 'The different between number of male and female employees must fewer than 10%'
- ROLLBACK TRANSACTION
- END;
- END;
- /*
- INSERT INTO [NhanVien]
- VALUES ('eee', 'eee', 'eee',999999995,'1966-06-06', 'xxx', 'Nu', 200000,333445555, 4);
- */
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement