CREATE TRIGGER – Lệnh tạo Trigger

Mô tả

Trigger là gì?

  • Lệnh CREATE TRIGGER dùng để tạo Trigger trong CSDL.
  • Trigger là một thủ tục nội tại (Stored Procedure) đặc biệt, được thực thi tự động mỗi khi có một câu lệnh INSERT/ UPDATE/ DELETE làm thay đổi dữ liệu trên bảng.
    – Phải được liên kết với một bảng/ bảng ảo.
    – Không thể gọi mà được thực hiện tự động.
    – Không có tham số.
  • Lệnh cập nhật dữ liệu trên bảng > xảy ra biến cố (event) > Trigger sẽ được tự động gọi thực hiện.

Tại sao phải sử dụng Trigger

  • Để kiểm tra các ràng buộc dữ liệu phức tạp.
  • Tính toán và tự động cập nhật giá trị.

Các hạn chế khi viết Trigger

  • Không được tạo bảng tạm.
  • Không được tham chiếu đến bảng tạm hoặc bảng hệ thống.
  • Không tạo/ sửa/ xoá cấu trúc các đối tượng sẵn có trong CSDL: CREATE/ ALTER/ DROP.
  • Không gán/ cấp quyền cho người dùng: GRANT/ REVOKE.

DDL Trigger là gì?

  • Là Trigger xảy ra với các biến cố của Database hoặc Server.

Có hai loại:

DDL Trigger mức Database

  • Lệnh CREATE , ALTER , DROP… Được lưu trong CSDL đã tạo ra nó. Ví dụ: tạo DDL Trigger trên Database để mỗi khi người dùng tạo bảng/ sửa bảng/ xóa bảng thì ghi nhận lại thông tin của các thay đổi này.

DDL Trigger mức Server

  • Lệnh CREATE USER, CREATE LOGIN… Được lưu trong CSDL master. Ví dụ: tạo DDL Trigger trên Server để không cho người dùng tạo Login.

DML Trigger là gì?

  • Trigger xảy ra với các hành động INSERT/ UPDATE/ DELETE trên bảng.
  • Dùng để kiểm tra các ràng buộc toàn vẹn phức tạp.
  • Dùng để xử lý tính toán và cập nhật tự động.

Có hai loại:

AFTER (FOR) Trigger

  • Dùng cho việc cập nhật bảng.
  • Chạy sau các hành động kiểm tra dữ liệu của các Constraint.
  • Dữ liệu đã bị tạm thời thay đổi trong bảng.
  • Chỉ áp dụng cho bảng.

INSTEAD OF Trigger

  • Dùng cho việc cập nhật bảng hoặc bảng ảo.
  • Chạy trước các hành động kiểm tra dữ liệu.
  • Dữ liệu chưa bị thay đổi.
  • Có thể thay thế hành động cập nhật dữ liệu bằng các hành động khác.
  • Có thể áp dụng cho bảng hoặc bảng ảo.
  • Thường dùng cho việc cập nhật bảng ảo.

Bảng ảo Inserted và Deleted

  • Bảng Inserted:
    – Chứa dữ liệu được thêm mới trong hành động INSERT/ UPDATE.
    – Chỉ có tại thời điểm xảy ra Trigger.
    – Cấu trúc bảng giống với bảng của Trigger.
  • Bảng Deleted:
    – Chứa dữ liệu bị xoá trong hành động DELETE/ UPDATE.
    – Chỉ có tại thời điểm xảy ra Trigger.
    – Cấu trúc bảng giống với bảng của Trigger.
  • Bảng Inserted sử dụng cho lệnh INSERT
  • Bảng Deleted sử dụng cho lệnh DELETE
  • Bảng Inserted + Deleted sử dụng cho lệnh UPDATE
    – Bảng Inserted chứa các dữ liệu mới (đã được cập nhật).
    – Bảng Deleted chứa các dữ liệu cũ (trước khi cập nhật).
    – UPDATE = INSERT mới + DELETE cũ.
Danh sách các loại biến cố (sự kiện)

Cú pháp

  • Xem danh sách các loại biến cố (sự kiện)
USE master
GO
SELECT * FROM sys.trigger_event_types
GO

DDL Trigger

  • DDL Trigger mức Database
CREATE TRIGGER Tên_Trigger
ON DATABASE
FOR Các_biến_cố
AS
	DECLARE Biến_cục_bộ
	Các_lệnh
GO
  • DDL Trigger mức Server
CREATE TRIGGER Tên_Trigger
ON ALL SERVER
FOR Các_biến_cố
AS
	DECLARE Biến_cục_bộ
	Các_lệnh
GO

DML Trigger

  • AFTER (FOR) Trigger
CREATE TRIGGER Tên_Trigger
ON Tên_bảng
	AFTER | FOR
	INSERT | UPDATE | DELETE
AS
	Các_lệnh
GO
  • INSTEAD OF Trigger
CREATE TRIGGER Tên_Trigger
ON Tên_bảng_ảo | Tên_bảng
	INSTEAD OF
	INSERT | UPDATE | DELETE
AS
	Các_lệnh
GO

Ví dụ

DDL Trigger

  • DDL Trigger mức Database
/*Tạo DDL Trigger trên Database để mỗi khi người dùng tạo/ sửa/ xóa bảng
thì ghi nhận lại thông tin của các thay đổi này*/
--1. Tạo CSDL TestDB
CREATE DATABASE TestDB
GO
USE TestDB
GO
--2. Tạo bảng GhiNhan
CREATE TABLE GhiNhan
(
	ThongTin xml NOT NULL,
	Ngay datetime NOT NULL DEFAULT GETDATE(),
	NguoiDung char(50) NOT NULL
)
GO
--3. Tạo Trigger để ghi lại lịch sử vào bảng GhiNhan
CREATE TRIGGER db_trg_GhiNhanDDLTable
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
	SET NOCOUNT ON
	INSERT GhiNhan(ThongTin, NguoiDung)	VALUES (EVENTDATA(), USER)
GO
--Kiểm tra
SELECT * FROM GhiNhan
--4.1 Tạo bảng TestTable
CREATE TABLE TestTable
(
	Stt int PRIMARY KEY,
	Mota nvarchar(30)
)
GO
--Kiểm tra
SELECT * FROM GhiNhan 
--4.2 Sửa bảng TestTable
ALTER TABLE TestTable
ADD ngay date DEFAULT GETDATE()
GO
--Kiểm tra
SELECT * FROM GhiNhan
--4.3 Xóa bảng TestTable
DROP TABLE TestTable
GO
--Kiểm tra
SELECT * FROM GhiNhan
DDL Trigger mức Database
  • DDL Trigger mức Server
--1. Tạo DDL Trigger trên Server để không cho người dùng tạo Login (tài khoản)
USE master
GO
CREATE TRIGGER srv_trg_KhongChoTaoLogin
ON ALL SERVER
FOR CREATE_LOGIN
AS
	PRINT N'Không được phép tạo Login'
    ROLLBACK
GO
--Kiểm tra
CREATE LOGIN User1 WITH PASSWORD = 'Abc123'
GO
/*2. Tạo DDL Trigger trên Server để không cho người dùng đăng nhập
ngoài thời gian làm việc (từ 7h đến 17h)*/
--Tạo bảng GhiNhan
CREATE TABLE GhiNhan
(
	TenLogin nvarchar(100) NOT NULL,
	Ngay datetime NOT NULL DEFAULT GETDATE()
)
GO
--Tạo Trigger để ghi lại lịch sử người dùng đăng nhập vào bảng GhiNhan
CREATE TRIGGER trg_logon_attempt
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF NOT (DATEPART(hh, GETDATE()) BETWEEN 7 AND 17)
  BEGIN
      ROLLBACK
      INSERT GhiNhan(TenLogin, Ngay) VALUES(ORIGINAL_LOGIN(), GETDATE())
  END
END
GO
--Thử đăng nhập vào SSMS sẽ thông báo lỗi nếu thời gian ngoài phạm vi từ 7h đến 17h
--Kiểm tra ghi nhận lịch sử
SELECT * FROM GhiNhan
DDL Trigger mức Server

DML Trigger

  • AFTER (FOR) Trigger
USE HumanResource
GO
--1.
CREATE TRIGGER tg_DEP_I1
ON DEPARTMENTS
AFTER INSERT
AS
	PRINT N'Thêm dữ liệu thành công!'
GO
--Kiểm tra
INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME) VALUES(900, N'Phòng 900')
GO
--2.
CREATE TRIGGER tg_DEP_I2
ON DEPARTMENTS
AFTER INSERT
AS
	--Nếu là thứ bảy thì không cho thêm và hiện thông báo bên dưới
	IF DATEPART(DW, GETDATE()) = 7
	BEGIN
		ROLLBACK
		PRINT N'Không thêm dữ liệu vào thứ Bảy!'
	END
GO
--Kiểm tra
INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME) VALUES(901, N'Phòng 901')
GO
USE QLBanHang
GO
--Tạo Trigger không cho người dùng thêm/ sửa/ xóa NCC vào thứ 7 hoặc cn
CREATE TRIGGER tg_NCC_IUD
ON NHACC
AFTER INSERT, UPDATE, DELETE
AS
	IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1
	BEGIN
		ROLLBACK
		RAISERROR(N'Không được phép cập nhật dữ liệu!', 16, 1)
		RETURN
	END
GO
--Kiểm tra
INSERT NHACC(MaNhaCC, TenNhaCC, DiaChi, DienThoai) VALUES('C08', N'Nguyễn Văn A', N'123 Lê Lợi', 7964252)
UPDATE NHACC SET TenNhaCC = N'Nguyễn Văn B' WHERE MaNhaCC = 'C08'
DELETE NHACC WHERE MaNhaCC = 'C08'
USE QLBanHang
GO
/*1. Viết Trigger khi thêm nhiều đơn ĐH thì kiểm tra nếu mã NCC 
không có trong bảng NHACC thì không cho thêm và thông báo lỗi*/
CREATE TRIGGER tg_DDH_I1
ON DONDH
AFTER INSERT
AS
	DECLARE @MaNhaCC char(3)
    --Đọc dữ liệu trong bảng tạm inserted
	SELECT @MaNhaCC = MaNhaCC FROM inserted
	--Kiểm tra mã NCC
	IF NOT EXISTS(SELECT 1 FROM NHACC WHERE MaNhaCC = @MaNhaCC)
	BEGIN
		ROLLBACK
		RAISERROR(N'Mã NCC không hợp lệ!', 16, 1)
		RETURN
	END
GO
--Kiểm tra
INSERT DONDH VALUES('D090', GETDATE(), 'C01') --OK
INSERT DONDH VALUES('D091', GETDATE(), 'X01') --NOT OK
--Thêm nhiều đơn ĐH
INSERT DONDH VALUES
	('D092', GETDATE(), 'C01'),
	('D093', GETDATE(), 'X01'),
	('D094', GETDATE(), 'C01')
/*2. Viết Trigger khi thêm nhiều đơn ĐH thì kiểm tra nếu có một mã NCC nào
không có trong bảng NHACC thì không cho thêm và thông báo lỗi*/
CREATE TRIGGER tg_DDH_I2
ON DONDH
AFTER INSERT
AS
	--Kiểm tra
	IF EXISTS(SELECT 1 FROM inserted WHERE MaNhaCC NOT IN(SELECT MaNhaCC FROM NHACC))
	BEGIN
		ROLLBACK
		RAISERROR(N'Mã NCC không hợp lệ!', 16, 1)
		RETURN
	END
GO
--Kiểm tra
INSERT DONDH VALUES
	('D092', GETDATE(), 'C01'),
	('D093', GETDATE(), 'X01'),
	('D094', GETDATE(), 'C01')
INSERT DONDH VALUES
	('D092', GETDATE(), 'C01'),
	('D093', GETDATE(), 'C02'),
	('D094', GETDATE(), 'C01')
USE HumanResource
GO
--Tạo Trigger không cho phép giảm lương
CREATE TRIGGER tg_EMP_U
ON EMPLOYEES
AFTER UPDATE
AS
	--Kiểm tra lương mới >= lương cũ
	IF EXISTS(SELECT 1 FROM inserted i JOIN deleted d ON i.Employee_id = d.Employee_id WHERE d.Salary > i.Salary)
	BEGIN
		ROLLBACK
		RAISERROR(N'Chỉ có tăng lương!', 16, 1)
		RETURN
	END
GO
--Kiểm tra
UPDATE EMPLOYEES SET Salary = Salary - 100 WHERE Employee_id = 105
UPDATE EMPLOYEES SET Salary = Salary + 100 WHERE Employee_id = 105
DML Trigger: AFTER (FOR) Trigger
  • INSTEAD OF Trigger
USE QLBanHang
GO
--Tạo bảng ảo
CREATE VIEW myview
AS
	SELECT d.*, n.TenNhaCC
	FROM DONDH d JOIN NHACC n ON d.MaNhaCC = n.MaNhaCC
GO
SELECT * FROM myview
--Kiểm tra
DELETE myview WHERE SoDh = 'D003'
--1.
CREATE TRIGGER tg_myview_D1
ON myview
INSTEAD OF DELETE
AS
	PRINT N'Thông báo!'
GO
--Kiểm tra
DELETE myview WHERE SoDh = 'D003'
GO
--2.
CREATE TRIGGER tg_myview_D2
ON myview
INSTEAD OF DELETE
AS
	SET NOCOUNT ON
	IF EXISTS(SELECT 1 FROM deleted WHERE SoDh IN (SELECT SoDh FROM CTDONDH))
	BEGIN
		RAISERROR(N'Không xóa vì đã có đặt hàng!', 16, 1)
		RETURN
	END
	DELETE DONDH WHERE Sodh IN (SELECT SoDH FROM deleted)
GO
--Kiểm tra
DELETE myview WHERE SoDh IN('D003','D002')
DELETE myview WHERE SoDh IN('D090','D092')
DML Trigger: INSTEAD OF Trigger

Xem thêm: lệnh sửa Trigger ALTER TRIGGER, lệnh xóa Trigger DROP TRIGGER.

Leave a Reply

Your email address will not be published. Required fields are marked *