ตัวอย่างทริกเกอร์ในเซิร์ฟเวอร์ ms sql ทริกเกอร์ (ฐานข้อมูล) ทริกเกอร์ DDL และแอปพลิเคชัน

สิ่งกระตุ้น:

<Определение_триггера>::= (สร้าง | แก้ไข) TRIGGER trigger_name ON (table_name | view_name) ( ( ( FOR | AFTER | INSTEAD OF ) ( [ DELETE] [,] [ INSERT] [,] [ UPDATE] ) [ WITH APPEND ] [ NOT FOR การจำลอง ] AS sql_statement[...n] ) | ( (สำหรับ | หลังจาก | แทน ) ( [,] ) [ พร้อมผนวก] [ ไม่สำหรับการจำลอง] AS ( หากอัปเดต (คอลัมน์_ชื่อ) [ (และ | หรือ) อัปเดต ( column_name)] [...n] | IF (COLUMNS_UPDATES() (process_bit_operator) change_bit_mask) (comparison_bit_operator) bit_mask [...n]) sql_operator [...n] ) )

ทริกเกอร์สามารถสร้างได้ในฐานข้อมูลปัจจุบันเท่านั้น แต่สามารถเข้าถึงฐานข้อมูลอื่นๆ ภายในทริกเกอร์ได้ รวมถึงฐานข้อมูลที่อยู่บนเซิร์ฟเวอร์ระยะไกลด้วย

ลองดูที่วัตถุประสงค์ของข้อโต้แย้งจาก CREATE | เปลี่ยนทริกเกอร์

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

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

ประเภททริกเกอร์

ใน เซิร์ฟเวอร์ SQLมีพารามิเตอร์สองตัวที่กำหนดพฤติกรรมของทริกเกอร์:

หลังจาก. ทริกเกอร์จะถูกดำเนินการหลังจากคำสั่งที่เรียกใช้เสร็จสมบูรณ์แล้ว หากไม่สามารถดำเนินการคำสั่งได้สำเร็จไม่ว่าด้วยเหตุผลใดก็ตาม ทริกเกอร์จะไม่ถูกดำเนินการ ควรสังเกตว่าการเปลี่ยนแปลงข้อมูลอันเป็นผลมาจากการดำเนินการตามคำขอของผู้ใช้และการดำเนินการทริกเกอร์จะดำเนินการในเนื้อหาของธุรกรรมเดียว: หากทริกเกอร์ถูกย้อนกลับ การเปลี่ยนแปลงผู้ใช้ก็จะถูกปฏิเสธเช่นกัน คุณสามารถกำหนดทริกเกอร์ AFTER ได้หลายตัวสำหรับการดำเนินการแต่ละครั้ง (INSERT, UPDATE, DELETE) ถ้าคุณมีทริกเกอร์ AFTER หลายตัวบนตาราง คุณสามารถใช้ sp_settriggerorder ระบบที่จัดเก็บขั้นตอนเพื่อระบุว่าทริกเกอร์ใดจะทำงานก่อนและทริกเกอร์ใดจะทำงานสุดท้าย ตามค่าเริ่มต้น ใน SQL Server ทริกเกอร์ทั้งหมดจะเป็นทริกเกอร์ AFTER
แทน . ทริกเกอร์ถูกเรียกแทนการดำเนินการคำสั่ง ทริกเกอร์ INSTEAD OF ต่างจากทริกเกอร์ AFTER ที่สามารถกำหนดได้ทั้งตารางและมุมมอง สำหรับการดำเนินการ INSERT, UPDATE, DELETE แต่ละรายการ สามารถกำหนดทริกเกอร์ INSTEAD OF ได้เพียงตัวเดียวเท่านั้น

ทริกเกอร์จะแยกความแตกต่างตามประเภทของคำสั่งที่ทริกเกอร์ตอบสนอง

ทริกเกอร์มีสามประเภท:

INSERT TRIGGER – ทริกเกอร์เมื่อมีการพยายามแทรกข้อมูลโดยใช้คำสั่ง INSERT
UPDATE TRIGGER – ทริกเกอร์เมื่อมีการพยายามเปลี่ยนแปลงข้อมูลโดยใช้คำสั่ง UPDATE
DELETE TRIGGER – ทริกเกอร์เมื่อมีการพยายามลบข้อมูลโดยใช้คำสั่ง DELETE

การก่อสร้าง [ ลบ] [,] [ ใส่] [,] [ อัพเดต]และ สำหรับ | หลังจาก | แทน ) ([,]กำหนดว่าทริกเกอร์จะตอบสนองต่อคำสั่งใด เมื่อสร้างจะต้องระบุอย่างน้อยหนึ่งคำสั่ง อนุญาต สร้างทริกเกอร์ตอบสนองต่อสองหรือทั้งสามคำสั่ง

WITH APPEND ช่วยให้คุณสร้างทริกเกอร์หลายรายการในแต่ละประเภท

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

โครงสร้าง AS sql_operator[...n] กำหนดชุดของคำสั่ง SQL และคำสั่งที่จะดำเนินการเมื่อมีการเรียกใช้ทริกเกอร์

โปรดทราบว่าการดำเนินการบางอย่างไม่ได้รับอนุญาตภายในทริกเกอร์ เช่น:

การสร้าง แก้ไข และลบฐานข้อมูล
การกู้คืน สำเนาสำรองฐานข้อมูลหรือบันทึกธุรกรรม

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

การเขียนโปรแกรมทริกเกอร์

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

คำสั่ง INSERT – ตารางที่แทรกประกอบด้วยแถวทั้งหมดที่ผู้ใช้พยายามแทรกลงในตาราง จะไม่มีแถวเดียวในตารางที่ถูกลบ หลังจากที่ทริกเกอร์เสร็จสิ้น แถวทั้งหมดจากตารางที่แทรกไว้จะถูกย้ายไปยังตารางต้นฉบับ
คำสั่ง DELETE – ตารางที่ถูกลบจะมีแถวทั้งหมดที่ผู้ใช้พยายามลบ ทริกเกอร์สามารถตรวจสอบแต่ละแถวและพิจารณาว่าได้รับอนุญาตให้ลบหรือไม่ จะไม่มีแถวในตารางที่แทรก
คำสั่ง UPDATE - เมื่อดำเนินการตารางที่ถูกลบจะมีค่าแถวเก่าที่จะถูกลบเมื่อดำเนินการเสร็จสิ้น

อัปเดตครั้งล่าสุด: 11/09/2017

ทริกเกอร์เป็นขั้นตอนการจัดเก็บชนิดพิเศษที่จะถูกเรียกโดยอัตโนมัติเมื่อมีการดำเนินการบางอย่างบนตารางหรือมุมมอง โดยเฉพาะอย่างยิ่งเมื่อมีการเพิ่ม เปลี่ยนแปลง หรือลบข้อมูล นั่นคือ เมื่อดำเนินการคำสั่ง INSERT, UPDATE, DELETE

คำจำกัดความอย่างเป็นทางการของทริกเกอร์:

สร้างทริกเกอร์ trigger_name ON (table_name | view_name) (หลัง | แทน) AS sql_expressions

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

ทริกเกอร์แต่ละตัวเชื่อมโยงกับตารางหรือมุมมองเฉพาะ โดยชื่อจะระบุหลังคำว่า ON

จากนั้นจึงตั้งค่าประเภททริกเกอร์ เราสามารถใช้หนึ่งในสองประเภท:

AFTER: ดำเนินการหลังจากการดำเนินการเสร็จสิ้น กำหนดไว้สำหรับตารางเท่านั้น

แทน: ดำเนินการแทนการดำเนินการ (นั่นคือ ที่จริงแล้ว การดำเนินการ - การเพิ่ม การเปลี่ยนแปลง หรือการลบ - ไม่ได้ดำเนินการเลย) กำหนดไว้สำหรับตารางและมุมมอง

หลังจากประเภททริกเกอร์ จะมีการบ่งชี้การดำเนินการที่กำหนดไว้สำหรับทริกเกอร์: INSERT, UPDATE หรือ DELETE

สำหรับทริกเกอร์ AFTER คุณสามารถใช้เพื่อดำเนินการหลายอย่างพร้อมกันได้ เช่น UPDATE และ INSERT ในกรณีนี้ การดำเนินการจะถูกระบุโดยคั่นด้วยเครื่องหมายจุลภาค คุณสามารถกำหนดได้เพียงการกระทำเดียวสำหรับทริกเกอร์ INSTEAD OF

และหลังจากคำว่า AS ก็จะเกิดชุดของนิพจน์ SQL ซึ่งจริงๆ แล้วประกอบขึ้นเป็นเนื้อความของทริกเกอร์

มาสร้างทริกเกอร์กันเถอะ สมมติว่าเรามีฐานข้อมูล productsdb พร้อมคำจำกัดความต่อไปนี้:

สร้างฐานข้อมูลผลิตภัณฑ์db; ไปใช้ productdb; สร้างผลิตภัณฑ์ตาราง (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR (30) NOT NULL, ผู้ผลิต NVARCHAR (20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL);

เรามากำหนดทริกเกอร์ที่จะเริ่มทำงานเมื่อมีการเพิ่มและอัปเดตข้อมูล:

ใช้ผลิตภัณฑ์db; ไปสร้าง TRIGGER Products_INSERT_UPDATE บนผลิตภัณฑ์หลังจากแทรก อัปเดตเป็นอัปเดตผลิตภัณฑ์ SET ราคา = ราคา + ราคา * 0.38 WHERE Id = (SELECT Id FROM แทรก)

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

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

และหลังจากเพิ่มผลิตภัณฑ์ลงในตารางผลิตภัณฑ์แล้ว ในความเป็นจริง สินค้าจะมีราคาสูงกว่าราคาที่กำหนดเมื่อเพิ่มเล็กน้อย:

การถอดทริกเกอร์

หากต้องการลบทริกเกอร์ คุณต้องใช้คำสั่ง DROP TRIGGER:

วางทริกเกอร์ผลิตภัณฑ์_INSERT_UPDATE

ปิดการใช้งานทริกเกอร์

มันเกิดขึ้นที่เราต้องการหยุดทริกเกอร์ชั่วคราว แต่เราไม่ต้องการลบมันออกทั้งหมด ในกรณีนี้ สามารถปิดใช้งานได้ชั่วคราวโดยใช้คำสั่ง DISABLE TRIGGER:

ปิดการใช้งาน TRIGGER Products_INSERT_UPDATE บนผลิตภัณฑ์

และเมื่อคุณต้องการทริกเกอร์ คุณสามารถเปิดใช้งานได้โดยใช้คำสั่ง ENABLE TRIGGER:

เปิดใช้งานทริกเกอร์ Products_INSERT_UPDATE บนผลิตภัณฑ์

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

วัตถุประสงค์ของทริกเกอร์

ป้องกันการเปลี่ยนแปลง (เช่น ป้องกันการเปลี่ยนแปลงใบแจ้งหนี้หลังจากส่งออกไปแล้ว)
. บันทึกการเปลี่ยนแปลง (เช่น เก็บสำเนาข้อมูลเก่า)
. ตรวจสอบการเปลี่ยนแปลง (เช่น เก็บบันทึกของผู้ใช้และบทบาทที่เกี่ยวข้องกับการเปลี่ยนแปลง)
. บันทึกการเปลี่ยนแปลง (เช่น ตรวจสอบให้แน่ใจว่าการเปลี่ยนแปลงทั้งหมดเป็นไปตามวันที่ของเซิร์ฟเวอร์ ไม่ใช่ของไคลเอ็นต์)
. การดำเนินการตามกฎเกณฑ์ทางธุรกิจ
. การจำลองข้อมูล (เช่น เก็บบันทึกการเปลี่ยนแปลงทั้งหมดที่จะถูกส่งไปยังฐานข้อมูลอื่นในเวอร์ชันที่ใหม่กว่า)
. ผลผลิตที่เพิ่มขึ้น (เช่น การอัปเดตยอดคงเหลือหลังจากรายละเอียดธุรกรรมแต่ละรายการ เพื่อเร่งการสืบค้น)

ประกาศทริกเกอร์

สร้างทริกเกอร์ {ก่อน|หลัง} {ลบ|แทรก|อัปเดต [ของ ]} บน อ้างอิง {เก่า {[แถว]|โต๊ะ [เช่น] } ใหม่ {แถว|ตาราง} [เช่น] }] [แต่ละ {แถลงการณ์|แถว [เมื่อไร ]}]
[เริ่มต้นอะตอม]

[จบ]

คำหลัก

. ก่อน|หลัง– เวลาเริ่มต้นของทริกเกอร์ – ก่อน | หลังจากการดำเนินการอัพเดต
. ลบ|แทรก|อัปเดต= เหตุการณ์ทริกเกอร์
. สำหรับแต่ละแถว– สำหรับแต่ละบรรทัด (ทริกเกอร์บรรทัด จากนั้นเมื่อ)
. สำหรับแต่ละแถลงการณ์– สำหรับทั้งทีม (ถูกต้องตามค่าเริ่มต้น)
. อ้างอิง– อนุญาตให้คุณกำหนดนามแฝงได้สูงสุด 4 ชื่อให้กับ old และ | หรือบรรทัดใหม่และ | หรือตารางที่สามารถเข้าถึงได้โดยทริกเกอร์

ข้อจำกัดของทริกเกอร์

เนื้อความของทริกเกอร์ต้องไม่มีข้อความต่อไปนี้:
. การกำหนด การลบ และการเปลี่ยนแปลงวัตถุฐานข้อมูล (ตาราง โดเมน ฯลฯ)
. การประมวลผลธุรกรรม (COMMIT, ROLLBACK)
. การเชื่อมต่อและการตัดการเชื่อมต่อไปยังฐานข้อมูล (CONNECT, DISCONNECT)

คุณสมบัติของแอพพลิเคชั่น
. ทริกเกอร์จะดำเนินการหลังจากใช้การตรวจสอบความสมบูรณ์อื่นๆ (ที่ประกาศ) ทั้งหมดแล้ว และมีประโยชน์เมื่อเกณฑ์การทดสอบค่อนข้างซับซ้อน หากการตรวจสอบที่เปิดเผยปฏิเสธการดำเนินการอัพเดต ทริกเกอร์จะไม่ถูกดำเนินการ ทริกเกอร์ทำงานในบริบทของธุรกรรม แต่ข้อจำกัด FK จะไม่ทำงาน
. หากทริกเกอร์ทำให้เกิดการเปลี่ยนแปลงเพิ่มเติมในตารางฐาน บ่อยครั้งสิ่งนี้จะไม่นำไปสู่การดำเนินการแบบเรียกซ้ำ แต่ควรชี้แจงให้ชัดเจน SQL Server 2005 จัดเตรียมความสามารถในการระบุการเรียกซ้ำได้สูงสุดถึง 255 ระดับโดยใช้คีย์เวิร์ด OPTION (MAXRECURSIV 3)
. โดยทั่วไปทริกเกอร์จะไม่ถูกดำเนินการเมื่อประมวลผลคอลัมน์ไบนารีขนาดใหญ่ (BLOB)
. ควรจำไว้ว่าเมื่อใดก็ตามที่ข้อมูลได้รับการอัปเดต DBMS จะสร้างสิ่งที่เรียกว่าทริกเกอร์โดยอัตโนมัติ ตารางเสมือนซึ่งมีชื่อต่างกันใน DBMS ที่แตกต่างกัน ใน InterBase และ Oracle - สิ่งเหล่านี้ใหม่และเก่า ใน SQL Server - แทรกและลบแล้ว ยิ่งไปกว่านั้น เมื่อข้อมูลเปลี่ยนแปลง ทั้งสองก็จะถูกสร้างขึ้น ตารางเหล่านี้มีจำนวนคอลัมน์เท่ากัน โดยมีชื่อและโดเมนเดียวกันกับตารางที่กำลังอัปเดต SQL Server 2005 DBMS ให้ความสามารถในการระบุตาราง รวมถึงตารางชั่วคราว ที่ควรแทรกข้อมูลลงในโดยใช้คีย์เวิร์ด OUTPUT Inserted.ID,... INTO @
. ใน DBMS จำนวนหนึ่ง อนุญาตให้ประกาศทริกเกอร์สำหรับการดำเนินการหลายอย่างพร้อมกันได้ ในการใช้การตอบสนองที่แตกต่างกันต่อการดำเนินการต่างๆ Oracle จัดเตรียมเพรดิเคตการลบ การแทรก การอัปเดต ซึ่งจะคืนค่า True สำหรับประเภทการอัปเดตที่เกี่ยวข้อง
. ใน Oracle DBMS คุณสามารถระบุรายการคอลัมน์ (หลังจากอัปเดต) สำหรับทริกเกอร์การอัปเดต ซึ่งจะทำให้แน่ใจได้ว่าทริกเกอร์จะถูกเรียกเฉพาะเมื่อค่าของคอลัมน์เหล่านี้เท่านั้นที่เปลี่ยนแปลง
. สามารถประกาศทริกเกอร์ได้หลายตัวสำหรับแต่ละเหตุการณ์ทริกเกอร์ (Oracle มี 12 ทริกเกอร์ต่อตาราง) และโดยปกติแล้วลำดับที่ทริกเกอร์จะเริ่มทำงานจะขึ้นอยู่กับลำดับที่สร้างขึ้น ใน DBMS บางตัว เช่น InterBase ลำดับการเริ่มต้นระบบจะถูกระบุโดยใช้คีย์เวิร์ด POSITION เพิ่มเติม โดยทั่วไป ควรดำเนินการทริกเกอร์สำหรับแต่ละคำสั่งก่อน จากนั้นจึงสำหรับแต่ละบรรทัด
. ทริกเกอร์สามารถฝังอยู่ภายในกันและกันได้ ดังนั้น SQL Server อนุญาตให้มีระดับการซ้อน 32 ระดับ (คุณสามารถใช้ตัวแปรส่วนกลาง @@NextLevel เพื่อกำหนดระดับการซ้อนได้)

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

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

การแก้ไขและการลบทริกเกอร์

หากต้องการลบทริกเกอร์ ให้ใช้คำสั่ง DROP TRIGGER
. หากต้องการเปลี่ยนทริกเกอร์ ให้ใช้คำสั่ง ALTER TRIGGER...
. ปิดการใช้งานทริกเกอร์
ในบางกรณี เช่น ระหว่างการโหลดเป็นชุด จำเป็นต้องปิดใช้งานทริกเกอร์ DBMS จำนวนหนึ่งมีความสามารถที่สอดคล้องกัน ใน Oracle และ SQL Server คำหลักคือ DISABLE|ENABLE ใน InterBase INACTIVE|ACTIVE ในคำสั่ง ALTER TRIGGER

คุณสมบัติของเซิร์ฟเวอร์อุตสาหกรรม

1) อินเตอร์เบส/ไฟร์เบิร์ด

สร้างทริกเกอร์ สำหรับ {ใช้งานอยู่ | ไม่ใช้งาน} {ก่อน|หลัง} {แทรก|ลบ|อัปเดต} [ตำแหน่ง ]
เช่น [ประกาศตัวแปร [()]]
เริ่ม

จบ

ตัวอย่าง:

สร้างทริกเกอร์ BF_Del_Cust สำหรับลูกค้า
ใช้งานก่อนที่จะลบตำแหน่ง 1 AS
เริ่ม
ลบออกจากคำสั่งซื้อโดยที่ Order.CNum=Customer.CNum;
จบ;

2) เซิร์ฟเวอร์ SQL

สร้างทริกเกอร์ บน [ด้วยการเข้ารหัส] {สำหรับ|หลัง|แทน} {แทรก|อัปเดต|ลบ}
เช่น

ใช้ B1;
ไป
สร้างทริกเกอร์ InUpCust1 บนลูกค้าหลังจากแทรก อัปเดต
AS RISEERROR('ตารางลูกค้ามีการเปลี่ยนแปลง');

ทริกเกอร์ประเภทเพิ่มเติม

Oracle และ SQL Server มอบความสามารถในการสร้าง (แทนที่) ทริกเกอร์สำหรับมุมมองที่ไม่ได้รับการอัพเดต เพื่อจุดประสงค์นี้ จึงมีการระบุคำหลัก INSTEAD OF:

สร้างทริกเกอร์แทนการแทรกเป็น ...

คุณสามารถตรวจสอบความพยายามของลูกค้าในการอัปเดตข้อมูลโดยใช้มุมมองและดำเนินการใดๆ จัดการมุมมองที่ไม่ได้รับการอัปเดต ฯลฯ
. SQL Server DBMS มีทริกเกอร์การย้อนกลับที่หยุดการกระทำทั้งหมดและแสดงข้อความ:

ทริกเกอร์ย้อนกลับ

บันทึกการบรรยายเรื่องสาขาวิชา “ฐานข้อมูล”

หัวข้อ: ขั้นตอนและทริกเกอร์

(ใช้ MS SQL Server เป็นตัวอย่าง)

ผู้เรียบเรียง: L. V. Shchegoleva

PetrSU ภาควิชาคณิตศาสตร์ประยุกต์และไซเบอร์เนติกส์

การแนะนำ................................................. ....... ........................................... ............ ......
	คําอธิบายโครงสร้างฐานข้อมูล................................................ ............ .............
	แนวคิดของขั้นตอน............................................ .... ....................................
	คำสั่งสำหรับการทำงานกับขั้นตอน............................................ ...................... ............
	แนวคิดของทริกเกอร์............................................ ...... ........................................
	คำสั่งสำหรับการทำงานกับทริกเกอร์............................................ .................... ....................
	ตัวอย่างการใช้งานทริกเกอร์............................................ .....................
	ตัวอย่างที่ 1................................................ ... ...............................................
	ตัวอย่างที่ 2................................................ ... ...............................................
	ตัวอย่างที่ 3................................................ ... ...............................................
	ตัวอย่างที่ 4................................................ ... ...............................................
	ตัวอย่างที่ 5................................................ ... ...............................................

PetrSU ภาควิชาคณิตศาสตร์ประยุกต์และไซเบอร์เนติกส์

การแนะนำ

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

กับ คำอธิบายงานของพวกเขา

คำสั่งทั้งหมดเขียนด้วยไวยากรณ์ MS SQL Server

ตัวอย่างจะได้รับสำหรับฐานข้อมูลที่มีโครงสร้างตามที่อธิบายไว้

ส่วนที่ 1.

PetrSU ภาควิชาคณิตศาสตร์ประยุกต์และไซเบอร์เนติกส์

1 คำอธิบายโครงสร้างฐานข้อมูล

ตาราง tblFaculty ประกอบด้วยข้อมูลเกี่ยวกับคณะต่างๆ ของมหาวิทยาลัย

ชื่อ			คำอธิบาย
คุณลักษณะ
			รหัสคณะ
			ชื่อคณะ
			ชื่อเต็มของคณบดี
			เบอร์สำนักงานคณบดี
			หมายเลขโทรศัพท์สำนักงานคณบดี
			จำนวนคณาจารย์

ตาราง tblStudent ประกอบด้วยข้อมูลเกี่ยวกับนักศึกษามหาวิทยาลัยในหนึ่งปีการศึกษา

ชื่อแอตทริบิวต์			คำอธิบาย
			รหัสนักศึกษา
			ชื่อของนักเรียน

			ทุนการศึกษา

ตาราง tblGroup ประกอบด้วยข้อมูลเกี่ยวกับกลุ่มนักศึกษามหาวิทยาลัยในหนึ่งปีการศึกษา

ชื่อแอตทริบิวต์			คำอธิบาย
			รหัสกลุ่ม
			ผู้ใหญ่บ้าน
			หมายเลขกลุ่ม
			คณะ

ตาราง tblSubject ประกอบด้วยข้อมูลเกี่ยวกับสาขาวิชาที่นักศึกษาคณะศึกษาในหนึ่งปีการศึกษา



	ชื่อแอตทริบิวต์			คำอธิบาย
				รหัสรายการ
				ชื่อรายการ
				จำนวนชั่วโมงบรรยาย
				จำนวนชั่วโมงฝึก
				คณะ

	บันทึกการบรรยายเรื่องวินัย “ฐานข้อมูล” (ขั้นตอนและตัวกระตุ้น)

PetrSU ภาควิชาคณิตศาสตร์ประยุกต์และไซเบอร์เนติกส์

ตาราง tblRoom ประกอบด้วยข้อมูลเกี่ยวกับห้องเรียนของมหาวิทยาลัย

ตาราง tblSchedule ประกอบด้วยข้อมูลเกี่ยวกับกำหนดการของกลุ่มนักเรียน

ชื่อแอตทริบิวต์			คำอธิบาย
			ตัวระบุ
			ผู้ชม
			วันของสัปดาห์



			ชื่อเต็มของอาจารย์

PetrSU ภาควิชาคณิตศาสตร์ประยุกต์และไซเบอร์เนติกส์

2 แนวคิดของขั้นตอน

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

ประโยชน์ของการใช้ขั้นตอน:

– การควบคุมการเข้าถึงข้อมูลแบบรวมศูนย์

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

– ลดการรับส่งข้อมูลเครือข่ายในระบบ“ไคลเอนต์-เซิร์ฟเวอร์” โดยส่งเฉพาะชื่อของขั้นตอนและพารามิเตอร์แทนการแลกเปลี่ยนข้อมูล และขั้นตอนจะดำเนินการบนเซิร์ฟเวอร์

– ซ่อนคุณสมบัติมากมายจากผู้ใช้ อุปกรณ์เฉพาะฐานข้อมูลซึ่งช่วยให้มั่นใจได้ถึงความเป็นอิสระของข้อมูลมากขึ้น

– ความปลอดภัยของข้อมูลมากขึ้น ผู้ใช้อาจมีสิทธิในการโทร

ขั้นตอน แต่ไม่ได้จัดการข้อมูลที่เรียกโดยขั้นตอนนี้ ข้อเสีย: ขาดมาตรฐานในการดำเนินการตามขั้นตอน

PetrSU ภาควิชาคณิตศาสตร์ประยุกต์และไซเบอร์เนติกส์

3 คำสั่งสำหรับการทำงานกับขั้นตอนใน MS SQL Server

การสร้างขั้นตอน

สร้างขั้นตอน<имя процедуры>

[@<имя параметра> <тип данных> , ...]

เริ่ม

<операторы>

ชื่อของตัวแปรทั้งหมดใน MS SQL Server จะต้องขึ้นต้นด้วยสัญลักษณ์

การเรียกขั้นตอน

ดำเนินการ<имя процедуры> [{@<имя переменной> | <значение параметра>}, ...]

การถอดขั้นตอน

ขั้นตอนการวาง<имя процедуры>

ขั้นตอนจะนับจำนวนนักเรียนที่ลงทะเบียนในแผนกซึ่งมีตัวระบุเป็นพารามิเตอร์อินพุตของขั้นตอน @id และส่งกลับค่านี้ในพารามิเตอร์ @total_sum

สร้างขั้นตอน prStudentsOfFaculty @id int, @total_sum int เอาต์พุต AS

ตั้งค่า @total_sum = 0

ตั้งค่า @total_sum = (เลือกจำนวน(*) จาก tblStudent, tblGroup โดยที่ (tblStudent.GroupId = tblGroup.GroupId) และ (tblGroup.FacultyId = @id)) สิ้นสุด

PetrSU ภาควิชาคณิตศาสตร์ประยุกต์และไซเบอร์เนติกส์

4 แนวคิดของทริกเกอร์

ทริกเกอร์ (กฎ) ถูกแนบไปกับตารางและจะถูกเรียกโดยอัตโนมัติโดยระบบการจัดการฐานข้อมูลเมื่อมีการดำเนินการอัปเดตบนโต๊ะ (การเพิ่ม ลบ การเปลี่ยนแปลงบันทึกตาราง)

คุณสมบัติของการใช้ทริกเกอร์ใน MS SQL Server

ในเซิร์ฟเวอร์ MS SQL:

– ทริกเกอร์สามารถเรียกได้หลังจากดำเนินการแล้วหรือแทนที่จะดำเนินการ

– ทริกเกอร์ถูกเรียกหนึ่งครั้งสำหรับบันทึกตารางทั้งหมดที่ต้องทำการดำเนินการ

– ดังนั้นบันทึกที่กำลังแก้ไขจะถูกจัดเก็บไว้ในสองตารางที่สร้างขึ้นโดยอัตโนมัติเมื่อมีการเรียกทริกเกอร์:

o ตารางที่แทรก – มีบันทึกตารางที่เปลี่ยนแปลงหรือเพิ่ม

o ตารางที่ถูกลบ - มีบันทึกก่อนที่จะทำการเปลี่ยนแปลงหรือลบบันทึกตาราง

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

– ในเนื้อความของทริกเกอร์ที่กำหนดไว้สำหรับการดำเนินการลบ มีเพียงตารางเท่านั้นที่พร้อมใช้งาน

– ในเนื้อความของทริกเกอร์ที่กำหนดไว้สำหรับการดำเนินการอัปเดต ทั้งสองตารางจะพร้อมใช้งาน

แทรกและลบ;

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

PetrSU ภาควิชาคณิตศาสตร์ประยุกต์และไซเบอร์เนติกส์

5 คำสั่งสำหรับการทำงานกับทริกเกอร์

การสร้าง

สร้างทริกเกอร์<имя триггера>บน<имя таблицы>

( สำหรับ | หลังจาก | แทน )

[แทรก] [,] [อัปเดต] [,] [ลบ] เช่น

ประกาศ @<имя переменной> <тип данных>, ...

เริ่ม<операторы>

การกำจัด

ปล่อยทริกเกอร์<имя триггера>

PetrSU ภาควิชาคณิตศาสตร์ประยุกต์และไซเบอร์เนติกส์

6 ตัวอย่างการใช้งานทริกเกอร์

ข้อจำกัดของสาขาวิชา:ทุนการศึกษาของนักเรียนไม่สามารถเพิ่มได้มากกว่า 5% ของทุนก่อนหน้า

สร้างทริกเกอร์ tgrStudentGrantUpdate

เปิด tblStudent หลังจากอัปเดต

ประกาศ @Grant_old float, @Grant_new float, @Id int;

เลือก @Grant_old = ให้สิทธิ์จากที่ถูกลบ

เลือก @Grant_new = Grant, @Id = StudentId จาก Inserted

หาก (@Grant_new - @Grant_old > 0.05 * @Grant_old)

อัปเดต tblStudent SET Grant = 1.05 * @Grant_old

โดยที่ StudentId = @Id

ทริกเกอร์ tgrStudentGrantUpdate ถูกสร้างขึ้นสำหรับตาราง tblStudent ทริกเกอร์จะเริ่มทำงานหลังจากการดำเนินการเปลี่ยนแปลงข้อมูลเสร็จสิ้น

ใน ทริกเกอร์จะกำหนดตัวแปรท้องถิ่นสามตัวแปร: @Grant_old (ประเภทจริง) เพื่อจัดเก็บทุนการศึกษาของนักเรียนเก่า @Grant_new (ประเภทจริง) เพื่อจัดเก็บทุนการศึกษาของนักเรียนใหม่ @Id (ประเภทจำนวนเต็ม) เพื่อจัดเก็บตัวระบุนักเรียน

เมื่อมีการเรียกทริกเกอร์ DBMS จะสร้างตารางขึ้นมาสองตาราง: Deleted ซึ่งมีบันทึกที่แก้ไขก่อนที่จะถูกแก้ไข และ Inserted ซึ่งมีบันทึกที่แก้ไขหลังจากที่มีการแก้ไข

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

ถัดไป ในส่วนของทริกเกอร์ จะมีการตรวจสอบเงื่อนไขเกี่ยวกับจำนวนการเปลี่ยนแปลงในทุนการศึกษา หากทุนการศึกษามีการเปลี่ยนแปลงมากกว่า 5% ตัวกระตุ้นจะทำการปรับเปลี่ยนข้อมูล โดยจะเพิ่มทุนการศึกษาเพียง 5% เมื่อเทียบกับมูลค่าทุนการศึกษาครั้งก่อนของนักเรียน การดำเนินการนี้ทำได้โดยการเรียกการดำเนินการอัปเดตบนตาราง tblStudent สำหรับนักเรียนที่เกี่ยวข้อง

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

คำจำกัดความของทริกเกอร์ในมาตรฐานภาษา SQL

ทริกเกอร์เป็นประเภทของขั้นตอนการจัดเก็บ พวกมันจะถูกดำเนินการเมื่อมีการดำเนินการตัวดำเนินการภาษาการจัดการข้อมูล (DML) บนตาราง ทริกเกอร์ใช้เพื่อตรวจสอบความสมบูรณ์ของข้อมูลและย้อนกลับธุรกรรม

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

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

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

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

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

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

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

รูปแบบพื้นฐานของคำสั่ง CREATE TRIGGER แสดงอยู่ด้านล่าง:

<Определение_триггера>::= สร้างทริกเกอร์ trigger_name ก่อน | หลังจาก<триггерное_событие>บน<имя_таблицы> <тело_триггера>

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

การดำเนินการที่ทำโดยทริกเกอร์จะถูกระบุสำหรับแต่ละแถว (สำหรับแต่ละแถว) ที่ครอบคลุมโดยเหตุการณ์ที่กำหนด หรือเพียงครั้งเดียวสำหรับแต่ละเหตุการณ์ (สำหรับแต่ละคำสั่ง)

การกำหนด <список_старых_или_новых_псевдонимов> หมายถึงส่วนประกอบเช่นเก่าหรือ บรรทัดใหม่(เก่า/ใหม่) ทั้งเก่าหรือ ตารางใหม่(โต๊ะเก่า/โต๊ะใหม่) เป็นที่ชัดเจนว่าค่าเก่าใช้ไม่ได้กับการแทรกเหตุการณ์ และค่าใหม่ใช้ไม่ได้กับการลบเหตุการณ์

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

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

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

การใช้งานทริกเกอร์ในสภาพแวดล้อม MS SQL Server

การใช้งาน MS SQL Server DBMS ใช้ตัวดำเนินการสร้างหรือแก้ไขทริกเกอร์ต่อไปนี้:

ลองดูที่วัตถุประสงค์ของข้อโต้แย้งจาก CREATE | เปลี่ยนทริกเกอร์

ประเภททริกเกอร์

มีสองตัวเลือกใน SQL Server ที่กำหนดลักษณะการทำงานของทริกเกอร์:

หลังจาก. ทริกเกอร์จะถูกดำเนินการหลังจากคำสั่งที่เรียกใช้เสร็จสมบูรณ์แล้ว หากไม่สามารถดำเนินการคำสั่งได้สำเร็จไม่ว่าด้วยเหตุผลใดก็ตาม ทริกเกอร์จะไม่ถูกดำเนินการ ควรสังเกตว่าการเปลี่ยนแปลงข้อมูลอันเป็นผลมาจากการดำเนินการตามคำขอของผู้ใช้และการดำเนินการทริกเกอร์จะดำเนินการในเนื้อหาของธุรกรรมเดียว: หากทริกเกอร์ถูกย้อนกลับ การเปลี่ยนแปลงผู้ใช้ก็จะถูกปฏิเสธเช่นกัน คุณสามารถกำหนดทริกเกอร์ AFTER ได้หลายตัวสำหรับการดำเนินการแต่ละครั้ง (INSERT, UPDATE, DELETE) ถ้าคุณมีทริกเกอร์ AFTER หลายตัวบนตาราง คุณสามารถใช้ sp_settriggerorder ระบบที่จัดเก็บขั้นตอนเพื่อระบุว่าทริกเกอร์ใดจะทำงานก่อนและทริกเกอร์ใดจะทำงานสุดท้าย ตามค่าเริ่มต้น ใน SQL Server ทริกเกอร์ทั้งหมดจะเป็นทริกเกอร์ AFTER
แทน . ทริกเกอร์ถูกเรียกแทนการดำเนินการคำสั่ง ทริกเกอร์ INSTEAD OF ต่างจากทริกเกอร์ AFTER ที่สามารถกำหนดได้ทั้งตารางและมุมมอง สำหรับการดำเนินการ INSERT, UPDATE, DELETE แต่ละรายการ สามารถกำหนดทริกเกอร์ INSTEAD OF ได้เพียงตัวเดียวเท่านั้น

ทริกเกอร์จะแยกความแตกต่างตามประเภทของคำสั่งที่ทริกเกอร์ตอบสนอง

ทริกเกอร์มีสามประเภท:

INSERT TRIGGER – ทริกเกอร์เมื่อมีการพยายามแทรกข้อมูลโดยใช้คำสั่ง INSERT
UPDATE TRIGGER – ทริกเกอร์เมื่อมีการพยายามเปลี่ยนแปลงข้อมูลโดยใช้คำสั่ง UPDATE
DELETE TRIGGER – ทริกเกอร์เมื่อมีการพยายามลบข้อมูลโดยใช้คำสั่ง DELETE

WITH APPEND ช่วยให้คุณสร้างทริกเกอร์หลายรายการในแต่ละประเภท

โปรดทราบว่าการดำเนินการบางอย่างไม่ได้รับอนุญาตภายในทริกเกอร์ เช่น:

การสร้าง แก้ไข และลบฐานข้อมูล
การกู้คืนฐานข้อมูลหรือการสำรองข้อมูลบันทึกธุรกรรม

การเขียนโปรแกรมทริกเกอร์

คำสั่ง INSERT – ตารางที่แทรกประกอบด้วยแถวทั้งหมดที่ผู้ใช้พยายามแทรกลงในตาราง จะไม่มีแถวเดียวในตารางที่ถูกลบ หลังจากที่ทริกเกอร์เสร็จสิ้น แถวทั้งหมดจากตารางที่แทรกไว้จะถูกย้ายไปยังตารางต้นฉบับ
คำสั่ง DELETE – ตารางที่ถูกลบจะมีแถวทั้งหมดที่ผู้ใช้พยายามลบ ทริกเกอร์สามารถตรวจสอบแต่ละแถวและพิจารณาว่าได้รับอนุญาตให้ลบหรือไม่ จะไม่มีแถวในตารางที่แทรก
คำสั่ง UPDATE - เมื่อดำเนินการตารางที่ถูกลบจะมีค่าแถวเก่าที่จะถูกลบเมื่อทริกเกอร์เสร็จสิ้น ค่าแถวใหม่มีอยู่ในตารางที่แทรก แถวเหล่านี้จะถูกเพิ่มลงในตารางต้นฉบับหลังจากที่เรียกใช้ทริกเกอร์สำเร็จ

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

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

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

คุณสามารถใช้ฟังก์ชัน COLUMNS_UPDATED() เพื่อรับรายการคอลัมน์ที่ได้รับการแก้ไขโดยคำสั่ง INSERT หรือ UPDATE ที่ทำให้ทริกเกอร์ดำเนินการ โดยจะส่งกลับเลขฐานสอง ซึ่งแต่ละบิตจะเริ่มต้นด้วยบิตที่มีนัยสำคัญน้อยที่สุด โดยจะสอดคล้องกับคอลัมน์หนึ่งของตาราง (ตามลำดับคอลัมน์เมื่อสร้างตาราง) หากบิตถูกตั้งค่าเป็น "1" แสดงว่าคอลัมน์ที่เกี่ยวข้องได้รับการแก้ไขแล้ว นอกจากนี้ ความจริงที่ว่าคอลัมน์มีการเปลี่ยนแปลงนั้นถูกกำหนดโดยฟังก์ชัน UPDATE (column_name)

สำหรับ ลบทริกเกอร์มีการใช้คำสั่ง

DROP TRIGGER (ทริกเกอร์_ชื่อ) [,...n]

นี่คือตัวอย่างการใช้ทริกเกอร์

ตัวอย่างที่ 14.1การใช้ทริกเกอร์เพื่อ การดำเนินการตามข้อจำกัดด้านมูลค่า. ในรายการที่เพิ่มลงในตารางธุรกรรม ปริมาณของผลิตภัณฑ์ที่ขายจะต้องไม่น้อยกว่ายอดคงเหลือจากตารางคลังสินค้า

คำสั่งสำหรับการแทรกบันทึกลงในตาราง Deal อาจเป็นเช่นนี้:

ใส่มูลค่าการค้า (3,1,-299,"01/08/2002")

ทริกเกอร์ที่สร้างขึ้นควรตอบสนองต่อการดำเนินการในลักษณะต่อไปนี้: จำเป็นต้องยกเลิกคำสั่งหากในตารางคลังสินค้ายอดคงเหลือผลิตภัณฑ์น้อยกว่าปริมาณที่ขายของผลิตภัณฑ์ด้วยรหัสที่ป้อน (ในตัวอย่าง รหัสผลิตภัณฑ์ = 3 ). ในบันทึกที่แทรก ปริมาณของผลิตภัณฑ์จะแสดงด้วยเครื่องหมาย “+” หากมีการจัดหาผลิตภัณฑ์ และจะมีเครื่องหมาย “-” หากจำหน่ายแล้ว ทริกเกอร์ที่นำเสนอได้รับการกำหนดค่าให้ประมวลผลบันทึกที่เพิ่มเพียงรายการเดียวเท่านั้น

สร้างทริกเกอร์ Trigger_ins ในธุรกรรมสำหรับการแทรก AS IF @@ROWCOUNT=1 เริ่มต้นหากไม่มีอยู่ (SELECT * FROM แทรก WHERE -inserted.quantity<=ALL(SELECT Склад.Остаток FROM Склад,Сделка WHERE Склад.КодТовара= Сделка.КодТовара)) BEGIN ROLLBACK TRAN PRINT "Отмена поставки: товара на складе нет" END END ตัวอย่างที่ 14.1 การใช้ทริกเกอร์เพื่อใช้ข้อจำกัดกับค่า

ตัวอย่างที่ 14.2การใช้ทริกเกอร์เพื่อรวบรวมข้อมูลทางสถิติ

สร้างทริกเกอร์เพื่อประมวลผลการดำเนินการแทรกบันทึกลงในตาราง Deal เช่น คำสั่งต่อไปนี้:

ใส่มูลค่าการค้า (3,1,200,"01/08/2002")

สินค้ารหัส 3 จัดส่งจากลูกค้ารหัส 1 จำนวน 200 หน่วย

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

ALTER TRIGGER Trigger_ins ในธุรกรรมสำหรับการแทรกตามที่ประกาศ @x INT, @y INT IF @@ROWCOUNT=1 --บันทึกถูกเพิ่มลงในตารางธุรกรรมเกี่ยวกับการจัดส่งสินค้า BEGIN --ปริมาณของสินค้าที่ขายจะต้องไม่เป็น -- น้อยกว่ายอดคงเหลือจากตารางคลังสินค้าหากไม่มีอยู่ (SELECT * FROM แทรก WHERE -inserted.quantity< =ALL(SELECT Склад.Остаток FROM Склад,Сделка WHERE Склад.КодТовара= Сделка.КодТовара)) BEGIN ROLLBACK TRAN PRINT "откат товара нет " END --если записи о поставленном товаре еще нет, --добавляется соответствующая запись --в таблицу Склад IF NOT EXISTS (SELECT * FROM Склад С, inserted i WHERE С.КодТовара=i.КодТовара) INSERT INTO Склад (КодТовара,Остаток) ELSE --если запись о товаре уже была в таблице --Склад, то определяется код и количество --товара издобавленной в таблицу Сделка записи BEGIN SELECT @y=i.КодТовара, @x=i.Количество FROM Сделка С, inserted i WHERE С.КодТовара=i.КодТовара --и производится изменения количества товара в --таблице Склад UPDATE Склад SET Остаток=остаток+@x WHERE КодТовара=@y END END ตัวอย่างที่ 14.2 การใช้ทริกเกอร์เพื่อรวบรวมข้อมูลทางสถิติ

ตัวอย่างที่ 14.3สร้างทริกเกอร์เพื่อประมวลผลการดำเนินการลบบันทึกจากตาราง Deal ตัวอย่างเช่น คำสั่งต่อไปนี้:

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

สร้างทริกเกอร์ Trigger_del บนธุรกรรมเพื่อลบ AS IF @@ROWCOUNT=1 -- หนึ่งบันทึกถูกลบ เริ่มต้นประกาศ @y INT,@x INT --รหัสและปริมาณของผลิตภัณฑ์ถูกกำหนดจากบันทึก -- ลบออกจากตารางคลังสินค้า SELECT @y=ProductCode, @ x=ปริมาณจากที่ถูกลบ --ในตารางคลังสินค้า ปริมาณของ --item จะถูกปรับปรุง UPDATE Warehouse SET Remaining=Remaining-@x WHERE Product Code=@y END ตัวอย่างที่ 14.3 ทริกเกอร์สำหรับการประมวลผลการดำเนินการลบบันทึกออกจากตาราง

ตัวอย่างที่ 14.4สร้างทริกเกอร์เพื่อประมวลผลการดำเนินการเปลี่ยนแปลงบันทึกในตาราง Deal โดยใช้คำสั่งต่อไปนี้:

ในการทำธุรกรรมทั้งหมดที่มีสินค้าที่มีรหัสเท่ากับ 3 ให้ลดปริมาณสินค้าลง 10 หน่วย

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

สร้างทริกเกอร์ Trigger_upd ในการทำธุรกรรมเพื่ออัปเดตตามที่ประกาศ @x INT, @x_old INT, @y INT, @y_old INT - เคอร์เซอร์ที่มีค่าใหม่ ประกาศ CUR1 เคอร์เซอร์สำหรับรหัสผลิตภัณฑ์ที่เลือก, จำนวนจากที่แทรก - เคอร์เซอร์ที่มีค่าเก่า ประกาศเคอร์เซอร์ CUR2 สำหรับรหัสผลิตภัณฑ์ที่เลือก จำนวนจากที่ถูกลบ OPEN CUR1 OPEN CUR2 -- เลื่อนแบบขนานผ่านเคอร์เซอร์ทั้งสอง FETCH NEXT จาก CUR1 เข้าสู่ @x, @y FETCH NEXT จาก CUR2 เข้าสู่ @x_old, @y_old ในขณะที่ @@FETCH_STATUS=0 BEGIN -- สำหรับรหัสสินค้าเก่าจะลดลง --ปริมาณในคลังสินค้า UPDATE Warehouse SET Remaining=Remaining-@y_old WHERE Product Code=@x_old --สำหรับรหัสสินค้าใหม่ หากสินค้าดังกล่าวยังไม่มีอยู่ในสต็อก บันทึกใหม่จะถูกป้อนหากไม่มีอยู่ (SELECT * FROM Warehouse WHERE Product Code=@x) INSERT INTO Warehouse(Product Code, Remaining) VALUES (@x,@y) ELSE --มิฉะนั้นสำหรับรหัสผลิตภัณฑ์ใหม่จะเพิ่ม -- ปริมาณในสต็อก UPDATE คลังสินค้า SET คงเหลือ=คงเหลือ+@y โดยที่ รหัสสินค้า=@x ดึงข้อมูลถัดไปจาก CUR1 เข้าสู่ @x, @y ดึงข้อมูลถัดไปจาก CUR2 เข้าสู่ @x_old, @y_old END ปิด CUR1 ปิด CUR2 ยกเลิกการจัดสรร CUR1 ยกเลิกการจัดสรร CUR2 ตัวอย่างที่ 14.4 ทริกเกอร์สำหรับการประมวลผลการดำเนินการเพื่อเปลี่ยนแปลงบันทึกในตาราง

ในทริกเกอร์ที่พิจารณา ไม่มีการเปรียบเทียบปริมาณของสินค้าเมื่อบันทึกธุรกรรมมีการเปลี่ยนแปลงกับยอดดุลในคลังสินค้า

ตัวอย่างที่ 14.5มาแก้ไขข้อบกพร่องนี้กัน ในการสร้างข้อความแสดงข้อผิดพลาด เราใช้คำสั่ง MS SQL Server RAISERROR ในเนื้อหาของทริกเกอร์ อาร์กิวเมนต์คือข้อความ ระดับความรุนแรง และสถานะข้อผิดพลาด

ALTER TRIGGER Trigger_upd บนธุรกรรมสำหรับการอัปเดตตามที่ประกาศ @x INT, @x_old INT, @y INT, @y_old INT,@o INT ประกาศ CUR1 CURSOR สำหรับรหัสรายการที่เลือก, จำนวนจากที่แทรกไว้ DECLARE CUR2 CURSOR สำหรับรหัสรายการที่เลือก, จำนวนจากที่ถูกลบ เปิด CUR1 เปิด CUR2 ดึงข้อมูลถัดไปจาก CUR1 เข้าสู่ @x, @y ดึงข้อมูลถัดไปจาก CUR2 เข้าสู่ @x_old, @y_old ในขณะที่ @@FETCH_STATUS=0 เริ่มต้นเลือก @o=คงเหลือจากคลังสินค้า โดยที่รหัสผลิตภัณฑ์=@x IF @o<-@y BEGIN RAISERROR("откат",16,10) CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR22 ROLLBACK TRAN RETURN END UPDATE Склад SET Остаток=Остаток-@y_old WHERE КодТовара=@x_old IF NOT EXISTS (SELECT * FROM Склад WHERE КодТовара=@x) INSERT INTO Склад(КодТовара,Остаток) VALUES (@x,@y) ELSE UPDATE Склад SET Остаток=Остаток+@y WHERE КодТовара=@x FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old END CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR2 ตัวอย่างที่ 14.5 เวอร์ชันที่ถูกต้องของทริกเกอร์สำหรับการประมวลผลการดำเนินการเปลี่ยนแปลงบันทึกในตาราง

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

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

เปลี่ยน TRIGGER Trigger_upd บนธุรกรรมแทนการอัปเดตตามประกาศ @k INT, @k_old INT DECLARE @x INT, @x_old INT, @y INT DECLARE @y_old INT ,@o INT DECLARE CUR1 CURSOR สำหรับรหัสธุรกรรมที่เลือก, รหัสผลิตภัณฑ์, จำนวนจาก แทรก ประกาศ CUR2 เคอร์เซอร์สำหรับรหัสธุรกรรมที่เลือก, รหัสผลิตภัณฑ์, จำนวนจากที่ถูกลบ เปิด CUR1 เปิด CUR2 ดึงข้อมูลถัดไปจาก CUR1 เข้าสู่ @k,@x, @y ดึงข้อมูลถัดไปจาก CUR2 เข้าสู่ @k_old,@x_old, @y_old ในขณะที่ @@FETCH_STATUS= 0 BEGIN SELECT @ o=คงเหลือจากคลังสินค้า WHERE Product Code=@x IF @o>=-@y BEGIN RAISERROR("change",16,10) UPDATE Transaction SET quantity=@y, Product Code=@x WHERE Transaction Code =@k อัปเดตชุดคลังสินค้าที่เหลืออยู่ =Remaining-@y_old WHERE Item Code=@x_old หากไม่มีอยู่ (SELECT * FROM Warehouse WHERE Item Code=@x) INSERT INTO Warehouse(Item Code, Remaining) VALUES (@x,@y) ELSE อัปเดตคลังสินค้า SET Remaining=Remaining+@y WHERE Item Code=@x END ELSE RAISERROR("บันทึกไม่เปลี่ยนแปลง",16,10) FETCH NEXT FROM CUR1 INTO @k,@x, @y FETCH NEXT FROM CUR2 INTO @k_old, @x_old, @y_old END ปิด CUR1 ปิด CUR2 ยกเลิกการจัดสรร CUR1 ยกเลิกการจัดสรร CUR2 ตัวอย่างที่ 14.6 ทริกเกอร์ที่ช่วยให้คุณสามารถเลิกทำการเปลี่ยนแปลงกับบางระเบียนเท่านั้น และทำการเปลี่ยนแปลงในส่วนที่เหลือ