SQL PROGRAMMING

Trình kích hoạt TRIGGERS trong SQL

Trình kích hoạt TRIGGERS trong SQL

Giới thiệu

Trong chương trước, chúng ta đã học cách làm việc với các mệnh đề GROUP BY, WHERE và HAVING trong SQL và cách sử dụng chúng với các tùy chọn khác nhau.
Trong chương này, chúng ta sẽ tìm hiểu về Trigger trong SQL, Các loại Trigger và cách sử dụng Trigger trong SQL với các điều kiện khác nhau.

Triggers

Triggers là các chương trình được lưu trữ được tự động thực thi hoặc kích hoạt khi một số sự kiện xảy ra. Nó là một đối tượng cơ sở dữ liệu được liên kết với một bảng và được thực thi tự động. Chúng tôi không thể gọi bất kỳ trình triggers nào một cách rõ ràng. Trình triggers cung cấp tính toàn vẹn của dữ liệu và được sử dụng để truy cập và kiểm tra dữ liệu trước và sau khi sửa đổi bằng cách sử dụng truy vấn DDL hoặc DML.

Triggers được sử dụng trong các sự kiện sau

  1. DML (Ngôn ngữ thao tác dữ liệu)(Insert ,Update ,Delete).
  2. DDL (Ngôn ngữ định nghĩa dữ liệu)(Create, Alter, Drop).
  3. Hoạt động cơ sở dữ liệu như LOGON, LOGOFF, SERVERERROR, SHUTDOWN, STARTUP.
Bây giờ chúng ta sẽ hiểu trigger là gì và nó hoạt động như thế nào?

Đầu tiên, tạo một bảng như sau:

  1. Create Table Student
  2. (
  3. IId int Not Null primary key,
  4. Name Nvarchar(MAX) Not NUll,
  5. Age Int Not Null,
  6. Class int not Null
  7. )

Bây giờ hãy chèn một số giá trị vào bảng như sau:

  1. Insert Into Student
  2. Select 1,‘A’,12,10 Union All
  3. Select 2,‘B’,16,11 Union All
  4. Select 3,‘C’,15,9 Union All
  5. Select 4,‘D’,13,12 Union All
  6. Select 5,‘E’,14,11 Union All
  7. Select 6,‘F’,17,8 Union All
  8. Select 7,‘G’,12,7 Union All
  9. Select 8,‘H’,17,12

Lúc này bảng sẽ có dạng như sau:

bàn
Sau đây là cú pháp của trigger,
  1. CREATE [OR REPLACE ] TRIGGER Trigger_Name
  2. ON Table_Name
  3.  {BEFORE | AFTER | INSTEAD OF }
  4. {INSERT [OR] | UPDATE [OR] | DELETE}
  5.   AS
  6. Begin
  7. Declaration Part
  8. {
  9. }
  10. Select Part
  11. {
  12. // Initialize Variables
  13. }
  15. Executable-Code
  16. EXCEPTION
  17. Exception-Handling-Code
  18. END;

Các loại Trình kích hoạt DML Triggers

Triggers có ba loại sau,

  1. After Triggers
  2. Instead Of Triggers

After Triggers

Sau khi trình triggers được gọi sau các thao tác DML (insert, update and delete). Chúng không được hỗ trợ bởi quan điểm. Một after triggers cũng được chia làm 3 phần như sau:

  • After Insert
  • After Delete
  • After Update

After Insert

Ví dụ

Một After Insert Trigger được gọi sau khi bất kỳ dữ liệu nào được chèn vào bảng.

  1. Create Trigger My_Trigger On Student
  3. AFTER Insert
  4. AS
  5. Declare @IId int ;
  6. Declare @Name Nvarchar(50) ;
  7. Declare @Age Int;
  8. Declare @Class int;
  10. Select @IId= Tab.IId From inserted Tab;
  11. Select @Name= Tab.Name From inserted Tab;
  12. Select @Age= Tab.Age From inserted Tab;
  13. Select @Class= Tab.Class From inserted Tab;
  15. Set @IId=@IId+1;
  16. Insert Into Student(IId,Name , Age ,Class) Values (@IId,@Name,@Age,@Class)
  17. Print ‘Inserted Successfully’

Bây giờ chúng ta sẽ chèn một số giá trị vào Student Table.

Insert Into Student(IId,Name,Age,Class) values(10,’Pankaj’,21,12)

Đầu ra

(1 row(s) affected)
Inserted Successfully
(1 row(s) affected)
Trong ví dụ trước, chúng ta sẽ tạo một trình trigger cho truy vấn chèn để khi bất kỳ dữ liệu nào được chèn vào student table, thì sau truy vấn insert, trình trigger này sẽ được gọi và một số thao tác mong muốn sẽ được thực hiện. Trong trình kích hoạt insert, chúng ta sử dụng bảng ma thuật “INSERTED”.

After Delete

After Delete Triggers được gọi sau khi bất kỳ dữ liệu nào bị xóa khỏi bảng.

  1. Create Trigger After_Delete On Student
  2. AFTER Delete
  3. AS
  4. Declare @IId int ;
  5. Declare @Name Nvarchar(50) ;
  6. Declare @Age Int;
  7. Declare @Class int;
  9. Select @IId= Tab.IId From Deleted Tab;
  10. Select @Name= Tab.Name From Deleted Tab;
  11. Select @Age= Tab.Age From Deleted Tab;
  12. Select @Class= Tab.Class From Deleted Tab;
  13. if @IId>10
  14. begin
  15. Insert Into Student(IId,Name , Age ,Class) Values (@IId,@Name,@Age,@Class)
  16. Print ‘Data Deleted Successfully’
  17. End
  18. else
  19. Begin
  20. Print ‘Data not Deleted Successfully’
  21. End

Bây giờ chúng ta sẽ xóa một số dữ liệu khỏi bảng:

Delete from Student Where IId=99
Đầu ra
(1 row(s) affected)
Data Deleted Successfully
(1 row(s) affected)
Bây giờ chúng ta sẽ lấy một ví dụ khác:
Delete from Student Where IId=9
Đầu ra
Data not Deleted Successfully
(0 row(s) affected)
Trong trình kích hoạt After Delete trigger, chúng ta đã sử dụng bảng ma thuật “Deleted”. Bảng này chứa tất cả dữ liệu đã bị xóa bởi truy vấn.

After Update

Một After Update Trigger được gọi sau khi bất kỳ loại cập nhật nào được thực hiện trên bảng.

  1. Create Trigger After_Update On Student
  2. AFTER Update
  3. AS
  4. Declare @IId int ;
  5. Declare @Name Nvarchar(50) ;
  6. Declare @Age Int;
  7. Declare @Class int;
  9. Declare @IId1 int ;
  10. Declare @Name1 Nvarchar(50) ;
  11. Declare @Age1 Int;
  12. Declare @Class1 int;
  14. Select @IId= Tab.IId From Deleted Tab;
  15. Select @Name= Tab.Name From Deleted Tab;
  16. Select @Age= Tab.Age From Deleted Tab;
  17. Select @Class= Tab.Class From Deleted Tab;
  19. Select @IId1= Tab.IId From inserted Tab;
  20. Select @Name1= Tab.Name From inserted Tab;
  21. Select @Age1= Tab.Age From inserted Tab;
  22. Select @Class1= Tab.Class From inserted Tab;
  23. Set @IId1=@IId1+1;
  24. if @IId>10
  25. begin
  26. Delete From Student Where IId=@iid
  27. Insert Into Student(IId,Name , Age ,Class) Values (@IId1,@Name1,@Age1,@Class1)
  28. Print ‘Data Updated Successfully’
  29. End
  30. else
  31. Begin
  32. Print ‘Data not Updated Successfully’
  33. End

Bây giờ chúng ta sẽ cập nhật một số dữ liệu.

Update Student Set Name=’Rahul’ , Age=30 where IId=101
Đầu ra
(1 row(s) affected)
(1 row(s) affected)
Data Updated Successfully
(1 row(s) affected)
Ví dụ trước cho thấy rằng trong trình kích hoạt After Update trigger, chúng ta có thể sử dụng cả hai bảng ma thuật “Inserted” và “Deleted”. Truy vấn Update cũng thực hiện hai bước, bước đầu tiên là xóa dữ liệu khỏi bảng. Trong trường hợp đó, bảng ma thuật “Deleted” chứa dữ liệu đã xóa và bước thứ hai là chèn dữ liệu vào bảng. Trong bước đó, bảng ma thuật “Inserted” chứa dữ liệu đã chèn.

Vì vậy, chúng ta có thể sử dụng cả hai bảng ma thuật trong trình “After Update” trigger.

Instead of Triggers

Chúng có thể được sử dụng như một công cụ chặn cho bất kỳ điều gì mà bất kỳ ai cố gắng thực hiện trên bảng hoặc chế độ xem của chúng ta. Loại trình kích hoạt này kích hoạt trước khi SQL Server bắt đầu thực hiện hành động đã kích hoạt nó. Chúng ta có thể có một INSTEAD OF insert/update/delete trigger trên một bảng đã được thực thi thành công nhưng không bao gồm thao tác insert/update/delete thực tế đối với bảng.

Giả sử chúng ta có một Instead Of Trigger cho thao tác Delete trên một bảng. Khi bất kỳ dữ liệu nào bị xóa khỏi bảng thì trình kích hoạt này sẽ thực thi trước nhưng sau khi thực hiện trình kích hoạt này, dữ liệu sẽ không bị xóa khỏi bảng trừ khi chúng ta đưa ra lệnh xóa khác từ bên trong trình kích hoạt.

Các loại Instead Of Triggers

  1. Instead Of Insert
  2. Instead Of Update
  3. Instead Of Delete
Ví dụ

Bảng trước khi thực hiện “Instead Of Delete”.

Thay vì Xóa

Bây giờ chúng ta tạo một “Instead Of Trigger“.

  1. Create Trigger Insted_Delete On Student
  2. Instead Of Delete
  3. as
  4. begin
  5. Declare @IId int ;
  6. Declare @Name Nvarchar(50) ;
  7. Declare @Age Int;
  8. Declare @Class int;
  10. Select @IId= Tab.IId From Deleted Tab;
  11. Select @Name= Tab.Name From Deleted Tab;
  12. Select @Age= Tab.Age From Deleted Tab;
  13. Select @Class= Tab.Class From Deleted Tab;
  14. Set @IId=@IId+1;
  15. Insert Into Student(IId,Name , Age ,Class) Values (@IId,@Name,@Age,@Class)
  16. Print ‘Data Updated Successfully’
  17. End

Sau đó, chúng ta cố gắng xóa một số dữ liệu khỏi dữ liệu và kiểm tra kết quả.

kiểm tra kết quả

Chúng ta có thể thấy rằng chúng ta thực hiện truy vấn xóa bản ghi sinh viên khỏi bảng có IId bằng 8 nhưng trong bản ghi kết quả với IId = 3 nó không bị xóa vì dữ liệu sẽ không xóa khỏi bảng trừ khi chúng ta thực hiện một hướng dẫn xóa khác từ bên trong trình kích hoạt.

Bây giờ chúng ta tạo một trình kích hoạt khác có chứa lệnh Delete Command.

  1. Create Trigger Insted_Delete1 On Student
  2. Instead Of Delete
  3. as
  4. begin
  5. Declare @IId int ;
  6. Declare @Name Nvarchar(50) ;
  7. Declare @Age Int;
  8. Declare @Class int;
  10. Select @IId= Tab.IId From Deleted Tab;
  11. Select @Name= Tab.Name From Deleted Tab;
  12. Select @Age= Tab.Age From Deleted Tab;
  13. Select @Class= Tab.Class From Deleted Tab;
  14. Delete From Student Where IId=@IId
  15. Set @IId=@IId+1;
  16. Insert Into Student(IId,Name , Age ,Class) Values (@IId,@Name,@Age,@Class)
  17. Print ‘Data Updated Successfully’
  18. End

Bây giờ chúng ta thực hiện thao tác xóa trên bảng.

kết quả

Xếp tầng theo chu kỳ trong Trigger

Đây là một tình huống không mong muốn khi có nhiều hơn một trình kích hoạt tham gia vào một vòng lặp vô hạn. Khi tạo trình kích hoạt, chúng ta nên đảm bảo rằng tình huống như vậy không thể xảy ra. Tình huống này có thể xảy ra khi hai trình kích hoạt tạo một vòng lặp.

Hãy để chúng ta tạo hai trình trigger như sau:

  • Trình kích hoạt đầu tiên dành cho phần chèn như sau:
    1. Create Trigger [dbo].[Insert_] On [dbo].[Student]
    2. For Insert
    3. as
    4. begin
    5. Declare @IId int ;
    6. Declare @Name Nvarchar(50) ;
    7. Declare @Age Int;
    8. Declare @Class int;
    10. Select @IId= Tab.IId From inserted Tab;
    11. Select @Name= Tab.Name From inserted Tab;
    12. Select @Age= Tab.Age From inserted Tab;
    13. Select @Class= Tab.Class From inserted Tab;
    16. Update Student set Name=@Name ,Age=@Age where IId=@IId
    17. Print ‘Data Inserted Successfully’
    18. End

     

  • Và trình kích hoạt thứ hai dành cho một bản cập nhật như sau:
    1. Create Trigger [dbo].[Update_] On [dbo].[Student]
    2. For Update
    3. as
    4. begin
    5. Declare @IId int ;
    6. Declare @Name Nvarchar(50) ;
    7. Declare @Age Int;
    8. Declare @Class int;
    10. Select @IId= Tab.IId From inserted Tab;
    11. Select @Name= Tab.Name From inserted Tab;
    12. Select @Age= Tab.Age From inserted Tab;
    13. Select @Class= Tab.Class From inserted Tab;
    14. Set @IId=@IId+1;
    15. Insert Into Student(IId,Name , Age ,Class) Values (@IId,@Name,@Age,@Class)
    16. Print ‘Data Updated Successfully’
    17. End

Bây giờ chúng ta chèn một số dữ liệu vào bảng và kiểm tra điều gì sẽ xảy ra.

Insert into Student values(9000,’A’,32,5000)

Đầu ra

Msg 217, Level 16, State 1, Procedure Update_, Line 15
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Trình kích hoạt đầu tiên (Insert_) chứa câu lệnh cập nhật. Khi câu lệnh này thực thi, nó tạo ra một lệnh gọi cho trình Update_trigger có chứa một câu lệnh chèn để khi câu lệnh này thực thi, nó sẽ tạo ra một lệnh gọi cho trình Insert_trigger. Vì vậy, một vòng lặp vô hạn sẽ xảy ra.

Ghi chú
Trong quá trình tạo trình kích hoạt, chúng ta phải đảm bảo loại bỏ điều kiện như vậy vì tình huống theo chu kỳ này vẫn tiếp diễn và sẽ dẫn đến một vòng lặp vô hạn sẽ làm sập cơ sở dữ liệu.

Bản tóm tắt

Trong chương này, chúng ta đã tìm hiểu về Trình kích hoạt Trigger trong SQL là gì và cách sử dụng Trình kích hoạt Trigger trong SQL với các tùy chọn khác nhau.

 

(Source: https://www.c-sharpcorner.com/)

Trả lời

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 *