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.
1.2 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ị.
1.3 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.
1.4 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:
1.4.1 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.
1.4.2 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.
1.5 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:
1.5.1 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.
1.5.2 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.
1.5.3 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)
2. 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
2.1 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
2.2 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
3. Ví dụ
3.1 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
3.2 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.
Tags: CREATECREATE TRIGGERTRIGGER
Next story ALTER TRIGGER – Lệnh sửa Trigger
Previous story DROP FUNCTION – Lệnh xóa hàm
You may also like...
0
CREATE TABLE # – Lệnh tạo bảng tạm
16/05/2020
by SQL.edu.vn · Published 16/05/2020 · Last modified 13/05/2023
0
CREATE VIEW – Lệnh tạo bảng ảo
11/04/2020
by SQL.edu.vn · Published 11/04/2020 · Last modified 14/05/2023
0
CREATE PROC – Lệnh tạo Stored Procedure
25/05/2020
by SQL.edu.vn · Published 25/05/2020 · Last modified 14/05/2023
Để lại một bình luận Hủy
Email của bạn sẽ không được hiển thị công khai.Các trường bắt buộc được đánh dấu *
Bình luận *
Tên *
Email *
Trang web
Lưu tên của tôi, email, và trang web trong trình duyệt này cho lần bình luận kế tiếp của tôi.
0 
0 
0