SQL Triggers: The Essential Guide

เผยแพร่แล้ว: 2022-10-12

ทริกเกอร์คือเอนทิตีฐานข้อมูลใน SQL Server ในทางเทคนิคแล้ว ฟังก์ชันเหล่านี้เป็นคลาสเฉพาะของการเรียกใช้ฟังก์ชันที่ตอบสนองต่อการดำเนินการฐานข้อมูลเฉพาะ

คู่มือที่จำเป็นนี้จะให้ข้อมูลเชิงลึกเกี่ยวกับ SQL Triggers ที่อาจเป็นประโยชน์ในอาชีพของคุณ มาเริ่มกันเลย!

ทริกเกอร์ SQL คืออะไร?

คำว่า "ทริกเกอร์" อธิบายคำสั่งที่เซิร์ฟเวอร์ดำเนินการค้นหาโดยอัตโนมัติทุกครั้งที่มีการเปลี่ยนแปลงเนื้อหาในฐานข้อมูล

ทริกเกอร์คือกลุ่มของคิวรี SQL ที่มีชื่อพิเศษซึ่งจัดเก็บไว้ในพื้นที่หน่วยความจำ เป็นการเรียกฟังก์ชันเฉพาะประเภทที่เรียกใช้ทันทีทุกครั้งที่มีเหตุการณ์ฐานข้อมูลเกิดขึ้น ทริกเกอร์ทุกตัวมีตารางที่กำหนด

What-are-SQL-Triggers

ตัวอย่างเช่น ทริกเกอร์สามารถเปิดใช้งานเมื่อใดก็ตามที่มีการเพิ่มคอลัมน์ใหม่ลงในตารางใดตารางหนึ่ง หรือหากมีการเปลี่ยนแปลงระเบียนเฉพาะ

ตาม Microsoft Developer Network ทริกเกอร์เป็นคลาสเฉพาะของกระบวนงานที่เก็บไว้ ในคำสั่งทริกเกอร์ เรากำหนดในขั้นต้นว่าควรเรียกใช้ทริกเกอร์เมื่อใด จากนั้นเราจะจัดเตรียมการดำเนินการที่ควรดำเนินการหลังจากเปิดใช้งานทริกเกอร์แล้ว

ไวยากรณ์:

 CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON tableName FOR EACH ROW SET operation [trigger_body];

คำอธิบายของแต่ละพารามิเตอร์

  • CREATE TRIGGER trigger_name – ใช้เพื่อสร้างทริกเกอร์หรือเปลี่ยนชื่อของทริกเกอร์ที่มีอยู่
  • ก่อน/หลัง – แบบสอบถามนี้ใช้เพื่อกำหนดเวลาดำเนินการของทริกเกอร์ ( ก่อนหรือหลังเหตุการณ์บางอย่าง)
  • INSERT/UPDATE/DELETE – สิ่งนี้อธิบายการกระทำที่เราต้องการทำบนโต๊ะ
  • ON tableName – ที่นี่ เรากำหนดชื่อตารางเพื่อตั้งค่าทริกเกอร์
  • สำหรับแต่ละแถว – คำสั่งนี้เกี่ยวข้องกับการเรียกแถว หมายความว่าทริกเกอร์จะถูกดำเนินการทุกครั้งที่มีการเปลี่ยนแปลงแถว
  • trigger_body – ระบุการดำเนินการที่จะดำเนินการเมื่อทริกเกอร์ถูกเปิดใช้งาน

ทริกเกอร์คือฟังก์ชันที่จัดเก็บไว้ซึ่งมีข้อมูลเฉพาะตัวที่ทำให้เราสามารถนำการสืบค้นข้อมูลที่ดำเนินการแล้วและจัดเก็บไว้อย่างปลอดภัยในหน่วยความจำกลับมาใช้ใหม่ได้ ทีนี้มาทำความเข้าใจว่าทำไม SQL ถึงต้องการมัน

ทริกเกอร์ส่วนใหญ่จะใช้เพื่อควบคุมการปรับใช้โค้ดเมื่อใดก็ตามที่มีเหตุการณ์เกิดขึ้น กล่าวอีกนัยหนึ่ง การใช้ทริกเกอร์เป็นตัวเลือกที่เหมาะสมที่สุด หากคุณต้องการให้ส่วนย่อยของโค้ดเฉพาะทำงานอย่างต่อเนื่องเพื่อตอบสนองต่อเหตุการณ์ที่ระบุ

ต่อไปนี้คือข้อดีบางประการของการใช้ทริกเกอร์ในการดำเนินการฐานข้อมูล SQL

  1. ดำเนินการตรวจสอบเพิ่มเติมเมื่อแทรก อัปเดต หรือลบข้อมูลจากตารางที่ได้รับผลกระทบ
  2. ลดเวลาตอบสนองซึ่งช่วยเพิ่มค่าใช้จ่ายในการคำนวณ
  3. เปิดใช้งานการเข้ารหัสพารามิเตอร์เริ่มต้นที่ซับซ้อนซึ่งไม่สามารถเข้าถึงได้โดยข้อจำกัดเริ่มต้น

ความสมบูรณ์ของการอ้างอิงเป็นคุณสมบัติหลักของระบบฐานข้อมูลเชิงสัมพันธ์ นี่หมายความว่าข้อมูลที่เก็บไว้ในระบบฐานข้อมูลต้องถูกต้องเสมอสำหรับแต่ละรายการและการดำเนินการแต่ละครั้ง

หากตารางสองตารางอยู่ในฐานข้อมูลหรือระบบที่แยกจากกัน ไม่มีทางที่จะรับรองความถูกต้องของข้อมูลภายในตารางโดยใช้ค่าข้อจำกัด ในสถานการณ์เช่นนี้ ทริกเกอร์เป็นตัวเลือกเดียวสำหรับการดำเนินการ

การรวมกันของอาร์กิวเมนต์ทริกเกอร์

การรวมกันของทริกเกอร์อาร์กิวเมนต์

สำหรับทุกตาราง เราสามารถระบุประเภททริกเกอร์ที่แตกต่างกันได้หกประเภท นี่คือการรวมกันของอาร์กิวเมนต์ทริกเกอร์ที่รวมอยู่ในทริกเกอร์ระดับแถวของ SQL

ก่อน INSERT : ทริกเกอร์เหล่านี้ดำเนินการกับแถวก่อนที่จะดำเนินการ INSERT ใด ๆ ในตารางที่ระบุหรือบนฐานข้อมูล

AFTER INSERT : จะดำเนินการกับแถวต่างๆ ทันทีตามกิจกรรม INSERT ของฐานข้อมูล

ก่อนการอัป เดต: ด้วยทริกเกอร์เหล่านี้ ฟังก์ชันในแถวจะถูกดำเนินการก่อนที่จะดำเนินการ UPDATE บนฐานข้อมูล

AFTER UPDATE : จะดำเนินการกับแถวทันทีตามฐานข้อมูลหรือกิจกรรม UPDATE ตารางเฉพาะ

BEFORE DELETE : มันรันการดำเนินการบางอย่างในแถวก่อนที่ฐานข้อมูลหรือตารางจะถูกดำเนินการ DELETE

หลังจากลบ : ทริกเกอร์เหล่านี้ดำเนินการกับแถวหลังจากทุกธุรกรรม DELETE

ประเภทของทริกเกอร์ SQL

ประเภทของ SQL-Triggers

ทริกเกอร์ SQL เป็นฟังก์ชันที่จัดเก็บไว้ซึ่งทำงานทันทีเมื่อมีเหตุการณ์เฉพาะเกิดขึ้น คล้ายกับการจัดกำหนดการตามเหตุการณ์ สถานการณ์ต่อมาสามารถเริ่มดำเนินการทริกเกอร์ได้

ทริกเกอร์ DML - DML ย่อมาจากภาษาการจัดการข้อมูล การเรียกใช้โค้ดเพื่อตอบสนองต่อการแก้ไขข้อมูลทำได้โดยใช้ทริกเกอร์ DML ทริกเกอร์นี้จะเปิดใช้งานเมื่อมีการดำเนินการคำสั่ง DML เช่น INSERT, UPDATE และ DELETE สิ่งเหล่านี้เรียกอีกอย่างว่า "ตัวกระตุ้นระดับตาราง"

ทริกเกอร์ DDL - DDL ย่อมาจาก Data Definition Language ทริกเกอร์ DDL ช่วยให้เราสามารถเรียกใช้โค้ดตามการเปลี่ยนแปลงสคีมาของฐานข้อมูล เช่น การเพิ่มหรือลบตาราง หรือเหตุการณ์ของเซิร์ฟเวอร์ เช่น เมื่อผู้ใช้เช็คอิน สิ่งเหล่านี้เรียกว่า “ทริกเกอร์ระดับฐานข้อมูล”

ทริกเกอร์เหล่านี้สามารถเปิดใช้งานได้ เมื่อคำสั่ง DDL บางอย่าง เช่น CREATE, ALTER หรือ DROP ถูกดำเนินการในฐานข้อมูลที่ใช้งานอยู่ สิ่งเหล่านี้ยังสามารถใช้เพื่อจับตาดูและจัดการกิจกรรมที่ดำเนินการ

ทริกเกอร์ การล็อกออน – เมื่อใดก็ตามที่เหตุการณ์การล็อกออนเกิดขึ้น (การเริ่มต้น เข้าสู่ระบบ ออกจากระบบ การปิดระบบ) ทริกเกอร์การเข้าสู่ระบบจะเรียกใช้ทันที การดำเนินการเหล่านี้จะดำเนินการตามกระบวนการตรวจสอบสิทธิ์ผู้ใช้เท่านั้น แม้กระทั่งก่อนธุรกรรมของผู้ใช้จะเริ่มต้นขึ้น ทริกเกอร์ LOGON จะไม่ทำงานหากการอนุญาตล้มเหลว

ทริกเกอร์เหล่านี้สามารถใช้เพื่อบันทึกประวัติการเข้าสู่ระบบหรือสร้างการจำกัดเหตุการณ์สำหรับการเข้าสู่ระบบเฉพาะ ท่ามกลางฟังก์ชันการตรวจสอบและการจัดการข้อมูลประจำตัวอื่นๆ สำหรับการเชื่อมต่อเซิร์ฟเวอร์

ทริกเกอร์ CLR - CLR ย่อมาจาก Common Language Runtime ทริกเกอร์ CLR เป็นชุดย่อยที่ไม่ซ้ำกันของทริกเกอร์ที่สร้างขึ้นโดยส่วนใหญ่บน CLR ภายในเทคโนโลยี .NET ทริกเกอร์เหล่านี้มีประโยชน์หากทริกเกอร์จำเป็นต้องทำการคำนวณเป็นจำนวนมาก หรือจำเป็นต้องเกี่ยวข้องกับเอนทิตีอื่นที่ไม่ใช่ SQL

ทริกเกอร์ DML และ DDL สามารถสร้างได้ด้วยการเปิดใช้งานการเข้ารหัสทริกเกอร์ CLR ที่รองรับในเทคโนโลยี .NET รวมถึง Visual Basic, C# และ F-sharp

ตัวอย่าง SQL Server Trigger

Sample-SQL-Server-Trigger

มาทำความเข้าใจแนวคิดทริกเกอร์เหล่านี้ด้วยตัวอย่างกัน

ขั้นแรก ให้สร้างฐานข้อมูลโดยใช้คำสั่ง SQL

 CREATE DATABASE testdb; use testdb;

ที่นี่ฉันได้ให้ "testdb" เป็นชื่อของฐานข้อมูล และขั้นตอนต่อไปคือการสร้างตาราง

 CREATE TABLE student( name varchar(25), id int(2), maths int(2), physics int(2), biology int(2), social int(2), total int(2) );

ฉันได้สร้างตารางสำหรับจัดเก็บรายละเอียดนักเรียน และนี่คือคำสั่งอธิบายโครงสร้างของตาราง ที่นี่ "นักเรียน" คือชื่อตารางที่ฉันให้ไว้

 DESC student;

ด้านล่างเป็นโครงสร้างของตารางที่ฉันสร้างขึ้น

 +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(25) | YES | | NULL | | | id | int | YES | | NULL | | | maths | int | YES | | NULL | | | physics | int | YES | | NULL | | | biology | int | YES | | NULL | | | social | int | YES | | NULL | | | total | int | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)

หลังจากสร้างตารางแล้ว ขั้นตอนต่อไปคือการตั้งค่าทริกเกอร์ ลองใช้อาร์กิวเมนต์ BEFORE INSERT กัน

ชื่อของทริกเกอร์ที่ฉันสร้างคือ "เครื่องหมาย" ทันทีที่มีการแก้ไขตารางด้วยคะแนนของนักเรียน ทริกเกอร์ด้านล่างจะพยายามกำหนดเกรดโดยรวมของนักเรียนโดยอัตโนมัติ

 CREATE TRIGGER marks BEFORE INSERT ON student FOR EACH ROW set new.total=new.maths+new.physics+new.biology+new.social;

เนื่องจากเราจำเป็นต้องแทนที่ข้อมูลแถวแทนที่จะทำงานกับข้อมูลเก่า เราจึงกำหนด "ผลรวม" โดยใช้ชื่อคลาสใหม่ และนิพจน์ที่ตามมาทั้งหมดจะถูกนำหน้าด้วยคำหลักใหม่หลังผลรวมโดยใช้ตัวดำเนินการจุด ตอนนี้เราจะเพิ่มค่าในแต่ละแถวและดูผลลัพธ์ ในขั้นต้น คะแนนรวมเป็น 0 สำหรับนักเรียนทุกคน

 INSERT INTO student VALUES("George",02,99,87,92,91,0); INSERT INTO student VALUES("James",03,91,81,94,90,0); INSERT INTO student VALUES("Harry",04,86,70,73,88,0); INSERT INTO student VALUES("John",05,73,89,78,92,0); INSERT INTO student VALUES("Lisa",01,94,75,69,79,0);

คำสั่งทริกเกอร์จะถูกทริกเกอร์โดยอัตโนมัติเมื่อข้อมูลถูกแทรกลงในตารางนักเรียนในกรณีนี้ คะแนนรวมของนักเรียนแต่ละคนจะถูกคำนวณโดยทริกเกอร์ ตอนนี้ มาดูกันว่าทริกเกอร์ถูกเรียกใช้หรือไม่โดยใช้คำสั่ง SELECT

 SELECT * FROM table_name;

และนี่คือผลลัพธ์สุดท้าย

 mysql> select * from student; +--------+------+-------+---------+---------+--------+-------+ | name | id | maths | physics | biology | social | total | +--------+------+-------+---------+---------+--------+-------+ | George | 2 | 91 | 81 | 94 | 90 | 356 | | James | 3 | 86 | 70 | 73 | 88 | 317 | | Harry | 4 | 73 | 89 | 78 | 92 | 332 | | John | 5 | 94 | 75 | 69 | 79 | 317 | | Lisa | 1 | 99 | 87 | 92 | 91 | 369 | +--------+------+-------+---------+---------+--------+-------+ 5 rows in set (0.00 sec)

ในผลลัพธ์ข้างต้น คุณจะเห็นว่าเครื่องหมายหัวเรื่องทั้งหมดถูกเพิ่มโดยอัตโนมัติสำหรับนักเรียนแต่ละคน ดังนั้น เราสามารถสรุปได้ว่าทริกเกอร์ถูกเรียกสำเร็จ

การดำเนินการทริกเกอร์เพิ่มเติม

เราสามารถดำเนินการได้หลายอย่างโดยใช้ทริกเกอร์ บางอันอาจเรียบง่ายและบางอันอาจซับซ้อนเล็กน้อย แต่เมื่อเราตอบคำถามแล้วจะเข้าใจได้ง่าย เมื่อใช้คำสั่ง Transact-SQL คุณสามารถเปิดใช้งาน ปิดใช้งาน หรือลบทริกเกอร์ได้โดยใช้คำสั่งต่อไปนี้

แบบสอบถามเพื่อตรวจสอบว่ามีทริกเกอร์เฉพาะหรือไม่

คำสั่งนี้จะตรวจสอบทริกเกอร์ที่ระบุในฐานข้อมูลทั้งหมด

 SELECT * FROM [sys].[triggers] WHERE [name] = 'Trigger_name'

แบบสอบถามเพื่อแสดงทริกเกอร์

ทริกเกอร์ทั้งหมดที่มีอยู่ในฐานข้อมูลที่ใช้งานอยู่จะแสดงโดยคำสั่งต่อไปนี้

 SHOW TRIGGERS;

แบบสอบถามเพื่อปิดการใช้งานทริกเกอร์

คำสั่งด้านล่างปิดใช้งานทริกเกอร์ในฐานข้อมูลที่ทำงาน

 DISABLE TRIGGER trigger_name ON DATABASE;

คุณยังสามารถระบุชื่อตารางบางอย่างเพื่อปิดใช้งานทริกเกอร์ได้

 DISABLE TRIGGER trigger_name ON table_name;

แบบสอบถามเพื่อเปิดใช้งานทริกเกอร์

คำสั่งต่อไปนี้จะปิดใช้งานทริกเกอร์เฉพาะที่กำหนดไว้ในตารางที่ระบุในฐานข้อมูลที่ใช้งานอยู่ก่อนจะเปิดใช้งานอีกครั้ง

 ALTER TABLE table_name DISABLE TRIGGER trigger_name ALTER TABLE table_name ENABLE TRIGGER trigger_name

ต้องปิดทริกเกอร์ก่อนที่จะพยายามเปิดใช้งาน

แบบสอบถามเพื่อเปิดหรือปิดใช้งานทริกเกอร์ทั้งหมดในตาราง

เมื่อใช้คำสั่ง SQL ข้างต้น เราสามารถปิดใช้งานหรือเปิดใช้งานทริกเกอร์ตารางทั้งหมดได้พร้อมกันโดยแทนที่ "ALL" แทนชื่อทริกเกอร์เฉพาะ

 ALTER TABLE table_name DISABLE TRIGGER ALL ALTER TABLE table_name ENABLE TRIGGER ALL

แบบสอบถามเพื่อลบหรือวางทริกเกอร์

ทริกเกอร์สามารถกำจัดได้โดยการลบหรือทั้งตาราง ทุกทริกเกอร์ที่เกี่ยวข้องจะถูกลบด้วยเมื่อตารางถูกลบ

 DROP TRIGGER [trigger_name];

เมื่อใดก็ตามที่ทริกเกอร์ถูกลบ ข้อมูลที่เกี่ยวข้องจะถูกลบออกจากตารางข้อมูล sys.objects

ข้อดีของทริกเกอร์

  • ง่ายต่อการสร้างทริกเกอร์และทริกเกอร์เองสามารถเรียกใช้ฟังก์ชันและวิธีการที่เก็บไว้ได้
  • ผู้ใช้สามารถใช้การตรวจสอบอย่างง่ายโดยใช้ทริกเกอร์
  • น่าเสียดาย คุณไม่สามารถสร้างข้อจำกัดข้ามเอนทิตีในระบบฐานข้อมูลด้วย SQL Server แม้ว่าคุณจะสามารถจำลองการดำเนินการของข้อจำกัดได้โดยใช้ทริกเกอร์
  • ข้อจำกัดด้านความสมบูรณ์สามารถนำไปใช้กับฐานข้อมูลโดยใช้ทริกเกอร์
  • เมื่อจำเป็นต้องมีการตรวจสอบกลุ่มมากกว่าการตรวจสอบแบบทีละแถวของข้อมูลที่ป้อนใหม่หรือที่เปลี่ยนแปลง ทริกเกอร์อาจมีประโยชน์

ข้อเสียของทริกเกอร์

ทริกเกอร์ SQL อาจไม่ใช่ตัวเลือกที่ดีที่สุดในบางสถานการณ์เนื่องจากข้อจำกัด

  • ทริกเกอร์ต้องได้รับการบันทึกอย่างถูกต้อง
  • เนื่องจากการเรียกใช้ฐานข้อมูลพร้อมกันซึ่งส่วนประกอบแอปพลิเคชันอาจไม่สามารถเข้าถึงได้ ทริกเกอร์จึงอาจแก้ไขจุดบกพร่องได้ยาก
  • คำสั่ง DML จะซับซ้อนมากขึ้นเมื่อใช้ทริกเกอร์
  • แม้แต่ปัญหาทริกเกอร์เล็กน้อยก็มีโอกาสที่จะนำไปสู่ข้อผิดพลาดเชิงตรรกะในคำสั่ง

บทสรุป

ทริกเกอร์เป็นส่วนประกอบที่มีประโยชน์อย่างมากของ Transact-SQL และ SQL และคุณสามารถใช้ทริกเกอร์เหล่านี้ใน Oracle ได้เช่นกัน การใช้ทริกเกอร์เป็นสิ่งสำคัญเมื่อเรียกใช้เมธอดที่เก็บไว้ ทริกเกอร์ SQL เหล่านี้ช่วยให้เราวิเคราะห์ไทม์ไลน์ของกิจกรรมและกำหนดว่าจะตอบสนองอย่างไรหากจำเป็น เรายังสามารถตรวจสอบตารางบางตารางที่เชื่อมต่อกับทริกเกอร์เพื่อรับข้อมูล

การเรียกซ้ำสามารถเปิดใช้งานได้ด้วยทริกเกอร์ เมื่อใดก็ตามที่ทริกเกอร์บนโต๊ะรันคำสั่งบนตารางพาเรนต์ การวนซ้ำครั้งที่สองของทริกเกอร์จะถูกตั้งค่าปิด และสิ่งนี้เรียกว่าทริกเกอร์แบบเรียกซ้ำ สิ่งนี้ช่วยได้ในขณะที่พยายามแก้ไขความสัมพันธ์ของข้อมูลประจำตัว

นอกจากนี้ ทริกเกอร์จะควบคุมรูปแบบการอัปเดตที่ฐานข้อมูลได้รับอนุญาตให้ยอมรับ เป็นประโยชน์อย่างมากในการรักษาข้อจำกัดความสมบูรณ์ของข้อมูลในระบบฐานข้อมูล ถ้าไม่มีคีย์ข้อจำกัด SQL ส่วนใหญ่เป็นคีย์หลักและคีย์ภายนอก

ฉันหวังว่าคุณจะพบว่าบทความนี้มีประโยชน์ในการเรียนรู้เกี่ยวกับ SQL Trigger

หากคุณต้องการเรียนรู้ฐานข้อมูลในเชิงลึก ต่อไปนี้คือแหล่งข้อมูลที่ยอดเยี่ยมบางส่วนในการเรียนรู้ SQL และ NoSQL