Advertisement
ppupil2

lab-5

Jul 14th, 2020
2,378
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 4.14 KB | None | 0 0
  1. /* II.2 */
  2.  
  3. CREATE TRIGGER [tr_2_Salary] ON [NhanVien]
  4. AFTER INSERT, DELETE, UPDATE
  5. AS
  6.  
  7. IF
  8.     (
  9.     SELECT COUNT(*) --đếm số phòng ban có lương trưởng phòng < nhân viên
  10.     FROM
  11.         (
  12.         SELECT *
  13.         FROM
  14.             ( --lấy ra lương của từng trưởng phòng
  15.             SELECT
  16.                 [PhongBan].[MaPh],
  17.                 [NhanVien].[Luong] AS [Head_salary]
  18.             FROM
  19.                 [PhongBan]
  20.                 INNER JOIN [NhanVien]
  21.                 ON [PhongBan].[TrPhg] = [NhanVien].[MaNV]
  22.             ) AS [Dept_Head_salary]
  23.             LEFT JOIN
  24.             ( --lấy ra lương của nhân viên theo từng phòng, không tính trưởng phòng
  25.             SELECT
  26.                 [NhanVien].[Phg],
  27.                 [NhanVien].[Luong]
  28.             FROM
  29.                 [NhanVien]
  30.                 INNER JOIN [PhongBan]
  31.                 ON [PhongBan].[MaPh] = [NhanVien].[Phg]
  32.             WHERE [NhanVien].[MaNV] != [PhongBan].[TrPhg]
  33.             ) AS [Emp_Salary_except_head]
  34.             ON [Dept_Head_salary].[MaPh] = [Emp_Salary_except_head].[Phg]
  35.         WHERE [Dept_Head_salary].[Head_salary] < [Emp_Salary_except_head].[Luong]
  36.             AND (
  37.                 [Dept_Head_salary].[MaPh] = (SELECT INSERTED.[Phg] FROM INSERTED)
  38.                 OR [Dept_Head_salary].[MaPh] = (SELECT DELETED.[Phg] FROM DELETED))
  39.         ) AS [Table_should_empty] --lấy ra danh sách phòng ban có lương trưởng phòng < nhân viên
  40.     ) > 0  
  41. BEGIN
  42.     PRINT 'The salary of the head of each department must be greater than or equal to salary of all employees in this department.'
  43.     ROLLBACK TRANSACTION
  44. END;
  45.  
  46. /*
  47. UPDATE [NhanVien] SET [Luong] = 25000 WHERE [MaNV] = 987987987
  48. */
  49.  
  50. /* II.3 */
  51. CREATE TRIGGER [tr_3_Salary_diff] ON [NhanVien]
  52. AFTER INSERT, DELETE, UPDATE
  53. AS
  54. BEGIN
  55.     DECLARE @diff float;
  56.     DECLARE @avg_hn float;
  57.     DECLARE @avg_hcm float;
  58.    
  59.     SET @avg_hn =
  60.         (
  61.         SELECT
  62.             AVG([NhanVien].[Luong])
  63.         FROM
  64.             [NhanVien]
  65.             INNER JOIN [DiaDiem_Phg]
  66.             ON [NhanVien].[Phg] = [DiaDiem_Phg].[MaPhg]
  67.         WHERE [DiaDiem_Phg].[DiaDiem] = 'Ha Noi'
  68.         )
  69.     SET @avg_hcm =
  70.         (
  71.         SELECT
  72.             AVG([NhanVien].[Luong])
  73.         FROM
  74.             [NhanVien]
  75.             INNER JOIN [DiaDiem_Phg]
  76.             ON [NhanVien].[Phg] = [DiaDiem_Phg].[MaPhg]
  77.         WHERE [DiaDiem_Phg].[DiaDiem] = 'TP HCM'
  78.         )
  79.     SET @diff = @avg_hn - @avg_hcm
  80.     IF  @diff >= 10000 OR @diff <= -10000
  81.     BEGIN
  82.         PRINT 'The different between average salary of employees in HCM and HN must fewer than 10000'
  83.         ROLLBACK TRANSACTION
  84.     END;
  85. END;
  86. /*
  87. INSERT INTO [NhanVien]
  88. VALUES ('ddd', 'eee', 'fff',222222262,'1966-06-06', 'xxx', 'Nam', 9000000,333445555, 4);
  89. */
  90.  
  91. /* II.4 */
  92. CREATE TRIGGER [tr_4_ThanNhan] ON [ThanNhan]
  93. AFTER INSERT, UPDATE
  94. AS
  95. IF  (
  96.     SELECT [Count_Familiars].[Familiars]
  97.     FROM
  98.         (
  99.         SELECT [Ma_NVien], COUNT([Ma_NVien]) AS [Familiars]
  100.         FROM [ThanNhan]
  101.         GROUP BY [Ma_NVien]
  102.         ) AS [Count_Familiars]
  103.         INNER JOIN INSERTED
  104.         ON INSERTED.[Ma_NVien] = [Count_Familiars].[Ma_NVien]
  105.     ) > 5
  106. BEGIN
  107.     PRINT 'Can not have more than five familiars'
  108.     ROLLBACK TRANSACTION
  109. END;
  110.  
  111. /*
  112. INSERT INTO [ThanNhan] VALUES (333445555,'zz', 'Nu', '1999-01-01', 'Con gai');
  113. INSERT INTO [ThanNhan] VALUES (333445555,'yy', 'Nu', '1999-12-12', 'Con gai');
  114. INSERT INTO [ThanNhan] VALUES (333445555,'xx', 'Nu', '1999-12-31', 'Con gai');
  115. */
  116.  
  117. /* II.5 */
  118. /*
  119. INSERT INTO [NhanVien] VALUES ('aaa', 'aaa', 'aaa',999999999,'1966-06-06', 'xxx', 'Nu', 200000,333445555, 4);
  120. INSERT INTO [NhanVien] VALUES ('bbb', 'bbb', 'bbb',999999998,'1966-06-06', 'xxx', 'Nu', 200000,333445555, 4);
  121. INSERT INTO [NhanVien] VALUES ('ccc', 'ccc', 'ccc',999999997,'1966-06-06', 'xxx', 'Nu', 200000,333445555, 4);
  122. INSERT INTO [NhanVien] VALUES ('ddd', 'ddd', 'ddd',999999996,'1966-06-06', 'xxx', 'Nu', 200000,333445555, 4);
  123. */
  124.  
  125. CREATE TRIGGER [tr_5_Sex_diff] ON [NhanVien]
  126. AFTER INSERT, DELETE, UPDATE
  127. AS
  128. BEGIN
  129.     DECLARE @diff float =
  130.         (
  131.         SELECT
  132.             CAST(( SELECT COUNT(CASE WHEN [Phai] = 'Nam' THEN 1 END) AS [NumOfMale] FROM [NhanVien]) AS FLOAT)
  133.             /
  134.             CAST((SELECT COUNT(CASE WHEN [Phai] = 'Nu' THEN 1 END) AS [NumOfMale] FROM [NhanVien]) AS FLOAT)
  135.         )
  136.     IF @diff >= 1.1 OR @diff <= CAST(10 AS FLOAT) / CAST(11 AS FLOAT)
  137.     BEGIN
  138.         PRINT 'The different between number of male and female employees must fewer than 10%'
  139.         ROLLBACK TRANSACTION
  140.     END;
  141. END;
  142. /*
  143. INSERT INTO [NhanVien]
  144. VALUES ('eee', 'eee', 'eee',999999995,'1966-06-06', 'xxx', 'Nu', 200000,333445555, 4);
  145. */
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement