Sharing & Linking: [Database-Tự Học SQL] COM2034_SQL Server

MỤC TIÊU:

Sử dụng Sql trigger thực thi các ràng buộc, qui định trong csdl.

- Khai báo, tạo trigger

- Thực thi trigger.

- Trigger DML, Trigger AFTER, Trigger INSTEAD OF

YÊU CẦU:

Bài 1:

Viết trigger DML:

- Ràng buộc khi thêm mới nhân viên thì mức lương phải lớn hơn 15000, nếu vi phạm thì xuất thông báo “luong phải >15000’

- Ràng buộc khi thêm mới nhân viên thì độ tuổi phải nằm trong khoảng 18 <= tuổi <=65.

- Ràng buộc khi cập nhật nhân viên thì không được cập nhật những nhân viên ở TP HCM

Bài 2:

Viết các Trigger AFTER:

- Hiển thị tổng số lượng nhân viên nữ, tổng số lượng nhân viên nam mỗi khi có hành động thêm mới nhân viên.

- Hiển thị tổng số lượng nhân viên nữ, tổng số lượng nhân viên nam mỗi khi có hành động cập nhật phần giới tính nhân viên

- Hiển thị tổng số lượng đề án mà mỗi nhân viên đã làm khi có hành động xóa trên bảng DEAN

Bài 3:

Viết các Trigger INSTEAD OF

- Xóa các thân nhân trong bảng thân nhân có liên quan khi thực hiện hành động xóa nhân viên trong bảng nhân viên.

- Khi thêm một nhân viên mới thì tự động phân công cho nhân viên làm đề án có MADA là 1.

THỰC HIỆN:

Bài 1:

Viết trigger DML:

- Ràng buộc khi thêm mới nhân viên thì mức lương phải lớn hơn 15000, nếu vi phạm thì xuất thông báo “luong phải >15000’

Code:

CREATE OR ALTER TRIGGER trg_CheckLuong_NV ON NHANVIEN 
FOR INSERT 
AS
	BEGIN
		IF (select LUONG from inserted) < 15000
		BEGIN
			PRINT N'Luong phai > 15000'
			ROLLBACK TRANSACTION
		END
	END
GO

Test trigger (cố tình nhập mức lương <15000, trong trường hợp này là 4000)

insert into NHANVIEN
values(N'Nguyễn',N'Văn',N'Ti','0921','1960-03-11',N'45 Lê Văn Sỹ,TP HCM','Nam',4000,'001',4,35)

- Ràng buộc khi thêm mới nhân viên thì độ tuổi phải nằm trong khoảng 18 <= tuổi <=65.

Có nhiều cách làm các bạn có thể chọn 1 trong các cách bên dưới

[Dùng OR] Code:

CREATE OR ALTER TRIGGER trg_KiemTraTuoi ON NHANVIEN 
FOR INSERT 
AS
BEGIN
	IF (SELECT YEAR(GETDATE()) - YEAR(NGSINH) FROM INSERTED) < 18 
    	OR (SELECT YEAR(GETDATE()) - YEAR(NGSINH) FROM INSERTED) > 65 
    
		BEGIN
			PRINT '[Trigger cua OR] Tuoi trong khoang 18 - 65'
			ROLLBACK TRANSACTION
		END
END
GO

[Dùng NOT BETWEEN] Code:

CREATE OR ALTER TRIGGER trg_KiemTraTuoi ON NHANVIEN 
FOR INSERT 
AS
BEGIN
	IF ((SELECT YEAR(GETDATE()) - YEAR(NGSINH) FROM INSERTED) 
		NOT BETWEEN 18 AND 65)
		BEGIN
			PRINT '[Trigger cua NOT BETWEEN] Tuoi trong khoang 18 - 65'
			ROLLBACK TRANSACTION
		END
END
GO

[Dùng BIẾN] Code:

CREATE OR ALTER TRIGGER trg_KiemTraTuoi ON NHANVIEN 
FOR INSERT 
AS
BEGIN
	DECLARE @age INT
	SET @age = (SELECT YEAR(GETDATE()) - YEAR(NGSINH) FROM INSERTED)

	IF ( @age NOT BETWEEN 18 AND 65)
		BEGIN
			PRINT '[Trigger cua DUNG BIEN] Tuoi trong khoang 18 - 65'
			ROLLBACK TRANSACTION
		END
END
GO

Test trigger (xóa trigger trg_CheckLuong_NV, thực hiện thêm nhân viên mới như bình thường, riêng trường ngày sinh cố tình nhập 2020 hoặc 193x)

INSERT INTO NHANVIEN VALUES(N'Nguyễn',N'Văn',N'Ti','0921','2020-03-11',N'45 Lê Văn Sỹ,TP HCM','Nam',1600,'001',4,35)
GO

- Ràng buộc khi cập nhật nhân viên thì không được cập nhật những nhân viên ở TP HCM

Code:

CREATE OR ALTER TRIGGER trg_KiemTra_DiaChi_HCM ON NHANVIEN 
FOR UPDATE
AS
BEGIN
	IF (SELECT DCHI FROM inserted) LIKE '%HCM%'
		BEGIN
			PRINT 'Khong duoc cap nhat dia chi HCM'
			ROLLBACK TRANSACTION
		END
END
GO

Để test chúng ta nên query bảng nhân viên để xem những nhân viên nào có địa chỉ HCM, sau đó mới test

SELECT * FROM NHANVIEN
GO

Test trigger:

UPDATE NHANVIEN SET	LUONG = 30001 WHERE MANV = 001
GO

Bài 2:

Viết các Trigger AFTER:

- Hiển thị tổng số lượng nhân viên nữ, tổng số lượng nhân viên nam mỗi khi có hành động THÊM MỚI nhân viên.

Code:

CREATE OR ALTER TRIGGER tg_Dem_NamNu ON NHANVIEN 
AFTER INSERT
AS
BEGIN
	DECLARE @a INT;
	SELECT @a = COUNT(*) 
		FROM NHANVIEN 
		WHERE PHAI = 'Nam'
		GROUP BY PHAI
	PRINT 'So nhan vien NAM hien tai la: ' + CAST(@a AS CHAR(5));
	
	DECLARE @b INT;
	SELECT @b = COUNT (*) 
		FROM NHANVIEN 
		WHERE PHAI = N'Nữ'
		GROUP BY PHAI
	PRINT 'So nhan vien NU hien tai la: ' + CONVERT(CHAR(5), @b);
END
GO

Test trigger

INSERT INTO NHANVIEN VALUES(N'Nguyễn',N'Văn',N'Ti','99999','1978-03-11',N'45 Lê Văn Sỹ,TP HCM','Nam',1600,'001',4,35)
GO

- Hiển thị tổng số lượng nhân viên nữ, tổng số lượng nhân viên nam mỗi khi có hành động CẬP NHẬT phần giới tính nhân viên

Code:

CREATE OR ALTER TRIGGER tg_Dem_NamNu_CapNhat ON NHANVIEN 
AFTER UPDATE
AS
BEGIN
	DECLARE @a INT;
	SELECT @a = COUNT(*) 
		FROM NHANVIEN 
		WHERE PHAI = 'Nam'
		GROUP BY PHAI
	PRINT 'So nhan vien NAM hien tai la: ' + CAST(@a AS CHAR(5));
	
	DECLARE @b INT;
	SELECT @b = COUNT (*) 
		FROM NHANVIEN 
		WHERE PHAI = N'Nữ'
		GROUP BY PHAI
	PRINT 'So nhan vien NU hien tai la: ' + CONVERT(CHAR(5), @b);
END
GO

Test trigger

UPDATE NHANVIEN SET PHAI = N'Nữ' WHERE MANV = '003'
GO

- Hiển thị tổng số lượng đề án mà mỗi nhân viên đã làm khi có hành động xóa trên bảng DEAN

Code:

CREATE OR ALTER TRIGGER trg_DemSoLuongDeAn_Xoa ON DEAN 
AFTER DELETE 
AS
BEGIN
	SELECT PHANCONG.MA_NVIEN, COUNT (*) 
		FROM DEAN
			INNER JOIN CONGVIEC ON DEAN.MADA = CONGVIEC.MADA
			INNER JOIN PHANCONG ON CONGVIEC.MADA = PHANCONG.MADA AND CONGVIEC.STT = PHANCONG.STT
		GROUP BY PHANCONG.MA_NVIEN
END
GO

Test trigger

DELETE FROM DEAN WHERE PHONG = 1;
GO

Bài 3:

Viết các Trigger INSTEAD OF

- Xóa các thân nhân trong bảng thân nhân có liên quan khi thực hiện hành động xóa nhân viên trong bảng nhân viên.

Code:

CREATE OR ALTER TRIGGER trg_DELETE_NhanVien_ThanNhan ON NHANVIEN
INSTEAD OF DELETE
AS
BEGIN
	DECLARE @Ma_nv NVARCHAR(9) 
	SELECT  @Ma_nv = MANV FROM deleted;
	DELETE FROM PHANCONG WHERE MA_NVIEN IN (SELECT MANV FROM deleted);
	DELETE FROM PHONGBAN WHERE TRPHG IN (SELECT MANV FROM deleted);
	DELETE FROM THANNHAN WHERE MA_NVIEN IN (SELECT MANV FROM deleted);
	DELETE FROM NHANVIEN WHERE MANV IN (SELECT MANV FROM deleted);
	SELECT 'Da xoa THANH CONG ' + @Ma_nv;
END
GO

Thực hiện xóa

DELETE FROM NHANVIEN WHERE MANV ='022';

- Khi thêm một nhân viên mới thì tự động phân công cho nhân viên làm đề án có MADA là 1.

Code:

CREATE OR ALTER TRIGGER trg_TuDong_PhanCong ON NHANVIEN 
AFTER INSERT AS
BEGIN
	INSERT INTO PHANCONG VALUES ((SELECT MANV FROM inserted),1 ,1 ,30)
END
GO

Thực hiện chèn để test

INSERT [dbo].[NHANVIEN] ([HONV], [TENLOT], [TENNV], [MANV], [NGSINH], [DCHI], [PHAI], [LUONG], [MA_NQL], [PHG]) 
	VALUES (N'Đinh ', N'Bá ', N'Tiên', N'091', CAST(N'2000-02-11T00:00:00.000' AS DateTime), N'119 Cống Quỳnh, Tp Y', N'N', 17000, N'005', 5)
GO

Xong!

Sharing & Linking

Pages

[Database-Tự Học SQL] COM2034_SQL Server - Lab 6: SQL Trigger

Để có những bài viết tốt hơn, hãy cho chúng tôi bình luận của bạn về bài viết này. Cám ơn các bạn!

No comments:

Post a Comment