ดัดแปลงมาจากบทความของ Robert Marda บน sqlservercentral.com: การตรวจสอบผ่านทริกเกอร์
ในบทความนี้ Robert จะให้ตัวอย่างโค้ดสำหรับทริกเกอร์หลายตัวที่ติดตั้งบนตารางเพื่อตรวจสอบการกระทำของผู้ใช้ในบันทึก MS เซิร์ฟเวอร์ SQL 7.0/2000.
สำหรับคำอธิบายวิธีการทำงานของทริกเกอร์โดยทั่วไปและวิธีการทำงานใน SQL Server 7.0 และ SQL Server 2000 คุณสามารถดูบทความต่อไปนี้ที่เขียนโดย Brian Kelley:
บทความแรกอธิบายวัตถุประสงค์ของการแทรกและลบตาราง
ตัวอย่างต่อไปนี้จะทำงานบน SQL Server 2000 อย่างไรก็ตาม ได้รับการทดสอบบน SQL Server 7.0 เท่านั้น
ก่อนอื่นเราต้องสร้างสิ่งที่จำเป็น ทำงานต่อไปตาราง เรียกใช้สคริปต์ด้านล่างใน Query Analyzer:
สร้างตาราง (
ตัวตน (1, 1) ไม่เป็นโมฆะ
โมฆะ
(35)โมฆะ
) บน
ไป
สร้างตาราง (
ไม่เป็นโมฆะ
(25) โมฆะ
(25) โมฆะ
(75) โมฆะ
(50) โมฆะ
โมฆะ
(35) โมฆะ
) บน
ไป
ทริกเกอร์ที่ติดตามการดำเนินการลบ
หากคุณต้องการบันทึกการลบออกจากตาราง คุณสามารถใช้ทริกเกอร์ตัวอย่างที่จะแทรกแถวลงใน ComponentsDeleted ทุกครั้งที่ลบแถวออกจากตาราง
ส่วนประกอบ: CREATE TRIGGER ถูกลบโดย ON dbo.Components
สำหรับการลบ
เช่น
แทรกลงใน ComponentsDeleted (Iden, ComponentName, SerialNumber,
ความคิดเห็น
ชื่อผู้ใช้, วันที่ลบ, ลบโดย)
เลือก Iden, ComponentName, SerialNumber, ความคิดเห็น, ชื่อผู้ใช้, getdate(),
SYSTEM_USER
จากที่ถูกลบ
ลบหนึ่งหรือสองแถวออกจากตารางส่วนประกอบ ตอนนี้ดูที่ตาราง ComponentsDeleted แล้วคุณจะเห็นแถวที่คุณลบที่นั่นพร้อมวันที่และเวลาที่ถูกลบ
การตั้งค่าระบบการตรวจสอบอย่างง่ายโดยใช้ทริกเกอร์ ตัวอย่างที่นำเสนอในบทความนี้ อาจมีประโยชน์ในกรณีที่คุณจำเป็นต้องรู้ว่าใครเป็นผู้ดำเนินการที่ทริกเกอร์ตรวจสอบในฐานข้อมูลของคุณและเมื่อใด
มีบทความมากมายบนอินเทอร์เน็ตเกี่ยวกับ ทริกเกอร์ sqlแต่ฉันจะเพิ่มอีกหนึ่งตัวอย่างที่เหมาะสมเพื่อเสริมเนื้อหาสำหรับผู้ที่ “มีความรู้” และเพื่อให้เข้าใจเนื้อหาได้ดีขึ้นสำหรับผู้ที่เพิ่งเริ่มเข้าใจ “เซนของ sql” ในขณะเดียวกัน ฉันจะสร้างการอภิปรายในหัวข้อนี้
ผมขอจองไว้ก่อนว่าความคิดเห็นของผมเป็นเพียงความคิดเห็นของผมเองและบางครั้งก็มีความเด็ดขาดมาก ด้วยเหตุผลหลายประการ คุณต้องทำงานกับไซต์ที่มีการโหลดสูงและแอปพลิเคชันเว็บที่ซับซ้อน
ประสบการณ์อันมีค่าอย่างหนึ่งที่ได้เรียนรู้จากการทำงานคือการติดตามลำดับความสำคัญและสถิติ มันหมายความว่าอะไร? ง่ายมาก: ถ้าคุณมีบล็อกและมีผู้เยี่ยมชม 2-3-4-10,012 ล้านคนต่อวัน และบทความถูกเขียนเพียง 1-2-3-3435 ครั้งต่อวัน (ลำดับความสำคัญน้อยกว่าจำนวนการดู) จากนั้นความเร็วในการบันทึกบทความ (และความซับซ้อนของสิ่งนี้) ที่สัมพันธ์กับความเร็วในการแสดงบทความอาจน้อยลงตามสัดส่วน ยิ่งเราแสดงมากเท่าใด การแสดงผลก็จะยิ่งมีความสำคัญมากขึ้นเท่านั้น ไม่ใช่การบันทึกบทความ/หน้า/ตาราง ซึ่งไม่ได้หมายความว่าคุณสามารถผ่อนคลายได้ การบันทึกบทความในบล็อกภายใน 3-5-10 วินาทีนั้นอยู่ภายในขอบเขตของความเพียงพอ แต่การสร้างหน้าในเวลามากกว่า 2 วินาที (+ ในขณะที่โหลดสคริปต์และสไตล์พร้อมรูปภาพ) เกือบจะเป็น "ไซต์ที่ช้ามาก ฉันจะอ่านอย่างอื่น" และที่แย่กว่านั้นคือ "ฉันจะไปซื้อมันที่อื่น"
หากเราพิจารณาเว็บไซต์ทั่วไปที่มีการโหวต/กรรม ความคิดเห็น ตัวนับการแสดงผลหน้า ฯลฯ นักพัฒนาจำนวนมากจะนึกถึงการออกแบบทันที เลือกนับ(*) จากความคิดเห็น โดยที่ comment.page=page_id ลองคิดคำนวณจำนวนเรตติ้งและจำนวนความคิดเห็นสำหรับแต่ละบทความดูสิ โอ้ เรามี 10 บทความจากแต่ละหัวข้อในหน้าหลัก ด้วยการรับส่งข้อมูล 10 คนต่อวินาที บน VPS โดยเฉลี่ย คุณสามารถจ่ายแบบสอบถาม SQL ได้ 60-100 รายการต่อหน้า (สวัสดี Bitrix)
แต่ลงนรกด้วยเนื้อเพลง (ฉันคงจะเบื่อมันแล้ว) ข้อมูลเปล่า:
ตารางบล็อก
สร้างตารางหากไม่มี `blog` (`id` int(11) ไม่เป็นโมฆะ AUTO_INCREMENT, `ชื่อ` varchar(128) ไม่เป็นโมฆะ, `ข้อความ` ข้อความไม่เป็นโมฆะ, `การสร้าง` วันที่และเวลาไม่เป็นโมฆะ, `การแก้ไข` วันที่และเวลาไม่เป็นโมฆะ , `img` varchar(128) ไม่เป็นโมฆะ ค่าเริ่มต้น "default.png", `สถานะ` Tinyint(4) ไม่เป็นโมฆะ ค่าเริ่มต้น "2", `user_id` int(11) ไม่เป็นโมฆะ, `rate` int(11) ไม่เป็นโมฆะ , `relax_type` Tinyint(4) ไม่เป็นโมฆะ, `ตัวจับเวลา` การประทับเวลา ไม่เป็นโมฆะ ค่าเริ่มต้น CURRENT_TIMESTAMP, `การแข่งขัน` Tinyint(1) ไม่เป็นโมฆะ ค่าเริ่มต้น "0", `มุมมอง` int(11) ไม่เป็นโมฆะ ค่าเริ่มต้น "0", `ความคิดเห็น ` int(11) ไม่เป็นโมฆะ, `url` varchar(128) ไม่เป็นโมฆะ, คีย์หลัก (`id`), คีย์เฉพาะ `url` (`url`), คีย์ `country_id` (`country_id`), คีย์ `user_id ` (`user_id`), คีย์ `สถานะ` (`สถานะ`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1456435 ;
ตารางความคิดเห็น
สร้างตารางหากไม่มี `ความคิดเห็น` (`owner_name` varchar(50) ไม่เป็นโมฆะ, `owner_id` int(12) ไม่เป็นโมฆะ, `id` int(12) ไม่เป็นโมฆะ AUTO_INCREMENT, `parent_id` int(12) ค่าเริ่มต้นเป็นโมฆะ, `user_id` int(12) DEFAULT NULL, `text` text, `creation` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `status` int(1) NOT NULL DEFAULT "0", คีย์หลัก (`id`), KEY `owner_name` ( `owner_name`,`owner_id`), KEY `parent_id` (`parent_id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=243254252 ;
อย่างที่คุณเห็นในตารางบล็อก แต่ละบทความจะมีตัวนับความคิดเห็น (ช่องแสดงความคิดเห็น)
แนวปฏิบัติทั่วไป:
1. เพิ่มความคิดเห็น - เพิ่มตัวนับสำหรับบล็อก
2. ลบ/ซ่อนความคิดเห็น - ลดตัวนับ
การทำเช่นนี้ในโค้ดนั้นสะดวกและคุ้นเคย แต่ก็มีอะไรที่มากกว่านั้น เครื่องมือที่มีประโยชน์- ทริกเกอร์
ดังนั้นเราจึงมี 2 กิจกรรม (อันที่จริง 3): การสร้างความคิดเห็นและการลบ (เหตุการณ์ที่สามคือการเปลี่ยนแปลงสถานะ (“การลบ” การแบน ฯลฯ)
ลองพิจารณาเฉพาะการสร้างและการลบ และปล่อยให้การเปลี่ยนสถานะเป็นการบ้านของคุณ
มีคุณลักษณะอย่างหนึ่งในตัวอย่าง: ความคิดเห็นสามารถใช้ได้กับบทความหลายประเภท
การสร้างความคิดเห็น:
สร้างทริกเกอร์ `add_count_comment` หลังจากแทรก `ความคิดเห็น` สำหรับแต่ละแถวเริ่มต้น // สำหรับผู้ใช้ใน บัญชีส่วนตัวลองนับจำนวนความคิดเห็นที่เขาเขียน UPDATE user SET user.countcomment= user.countcomment+1 WHERE user.id = NEW.user_id; // พิจารณาว่าความคิดเห็นหมายถึงอะไร และเพิ่มตัวนับในตารางเหล่านี้ทันที CASE NEW.`owner_name` เมื่อ "Blog" จากนั้น UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`+1 WHERE `บล็อก `.id = ใหม่.`owner_id` ; เมื่อ "บทความ" แล้วอัปเดต `บทความ` SET `article`.`comment` = `article`.`comment`+1 WHERE `article`.`id` = NEW.`owner_id` ; เมื่อ "PopulatePlace" จากนั้นอัปเดต `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`+1 WHERE `populate_place`.`id` = NEW.`owner_id` ; จบคดี; // ที่นี่เราทำให้การทำงานกับฟีดข่าวง่ายขึ้นสำหรับตัวเราเอง // เราเขียน url ของบทความทันที เพื่อที่เราจะได้ไม่ทำการเลือกที่ไม่จำเป็น กรณีใหม่`owner_name` เมื่อ "บล็อก" จากนั้นตั้งค่า userurl = (เลือก URL จาก `blog` โดยที่ `blog`.id= NEW.`owner_id`); เมื่อ "บทความ" แล้วตั้งค่า userurl = (เลือก url จาก `บทความ` WHERE article.id=NEW.`owner_id`); เมื่อ "PopulatePlace" จากนั้นตั้งค่า userurl = ``; จบคดี; // เขียนชื่อบทความทันทีเพื่อไม่ให้ทำการเลือก THEN CASE NEW.`owner_name` WHEN "Blog" THEN SET usertitle = (เลือกชื่อจาก `blog` โดยที่ blog.id=NEW.`owner_id`) ; เมื่อ "บทความ" แล้วตั้งค่า usertitle = (เลือกชื่อจาก `บทความ` โดยที่ article.id=NEW.`owner_id`); เมื่อ "PopulatePlace" จากนั้นตั้งค่า usertitle = ` `; จบคดี; แทรกค่า user_has_events ลงในค่า (NEW.user_id,NEW.id,"ความคิดเห็น",ตอนนี้(),userurl , usertitle); จบ
เช่นเดียวกับการลบความคิดเห็น:
สร้างทริกเกอร์ `del_count_comment` หลังจากลบ `ความคิดเห็น` สำหรับแต่ละแถว เริ่มต้นอัปเดตชุดผู้ใช้ user.countcomment= user.countcomment -1 WHERE user.id = OLD.user_id; CASE OLD.`owner_name` เมื่อ "บล็อก" จากนั้นอัปเดต `blog` SET `blog`.`comment` = `blog`.`comment`-1 WHERE `blog`.`id` = OLD.`owner_id` ; เมื่อ "บทความ" แล้วอัปเดต `บทความ` SET `article`.`comment` = `article`.`comment`-1 โดยที่ `article`.`id` = OLD.`owner_id` ; เมื่อ "PopulatePlace" จากนั้นอัปเดต `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`-1 WHERE `populate_place`.`id` = OLD.`owner_id` ; จบคดี; จบ
และสิ่งที่เราได้รับ:
1. เมื่อคุณใส่ความคิดเห็น เราจะโดยอัตโนมัติ ใช้ sqlเซิร์ฟเวอร์คำนวณจำนวนความคิดเห็นสำหรับวัตถุความคิดเห็นเฉพาะ (บทความ หน้า หมายเหตุ)
2. เราได้สร้างฟีดข่าว (สวัสดีชาวโซเชียลเน็ตเวิร์กทั้งหมด ฯลฯ)
3. เมื่อคุณลบความคิดเห็น เราจะหักข้อมูลทั้งหมด
4. เราไม่ได้ใช้เครื่องมือกรอบงานใดๆ
5. การดึงข้อมูลที่จำเป็นทั้งหมดเกิดขึ้นอย่างรวดเร็ว (เพียง 1 คำขอเมื่อแสดงเพจ ยกเว้นข้อมูล "ซ้าย" อื่น ๆ ในนั้น)
นอกจากนี้เรายังมีสฟิงซ์ซึ่งจะคัดเลือกบทความที่มีการเปลี่ยนแปลงในนาทีสุดท้ายเป็นระยะ เพื่อจุดประสงค์นี้ บล็อกจึงมีช่องแก้ไข
เพิ่มทริกเกอร์:
สร้างทริกเกอร์ `ins_blog` ก่อนที่จะแทรกใน `blog` // แทรกเวลาก่อนที่จะบันทึกข้อมูลโดย "แทนที่" ข้อมูล
สำหรับแต่ละแถว BEGIN SET NEW.modification = NOW(); จบ
ตอนนี้เมื่อทำการเลือกในนาทีสุดท้ายเราจะได้เอกสารทั้งหมดที่เพิ่มเข้ามาในนาทีสุดท้าย
สร้างทริกเกอร์ `ins_blog` ก่อนอัปเดตบน 'blog` // แทรกเวลาก่อนที่จะบันทึกข้อมูลโดย "แทนที่" ข้อมูล สำหรับแต่ละแถว BEGIN SET NEW.modification = NOW(); จบหากข้อมูลมีการเปลี่ยนแปลงเราจะอัปเดต
ดัชนีการค้นหา เดียวกัน.โดยปกติแล้ว ในโปรเจ็กต์ทั่วไป ทุกอย่างที่สามารถถ่ายโอนไปยังฝั่งเซิร์ฟเวอร์ sql จะถูกถ่ายโอน เซิร์ฟเวอร์ sql เองก็ทำงานคล้าย ๆ กันเร็วขึ้นและด้วย
ด้วยทรัพยากรที่น้อยลง
กว่านี้สามารถทำได้ผ่านภาษาโปรแกรมที่ใช้UPD: ประกาศเปิดโฮลิวาร์ที่อุทิศให้กับความเป็นไปได้ในการทำให้โครงสร้างของฐานข้อมูลซับซ้อนขึ้น
คำจำกัดความของทริกเกอร์ ขอบเขตการใช้งาน ตำแหน่งและบทบาทของทริกเกอร์ในการรับรองความสมบูรณ์ของข้อมูล มีการอธิบายประเภทของทริกเกอร์ ตัวดำเนินการสำหรับการสร้าง การเปลี่ยนแปลง และการลบทริกเกอร์จะได้รับการพิจารณา การเขียนโปรแกรมทริกเกอร์จะแสดงพร้อมตัวอย่างการสร้างทริกเกอร์เพื่อใช้ข้อจำกัดด้านความสมบูรณ์และรวบรวมข้อมูลทางสถิติ
คำจำกัดความของทริกเกอร์ในมาตรฐานภาษา SQL ทริกเกอร์เป็นประเภทของขั้นตอนการจัดเก็บ พวกมันจะถูกดำเนินการเมื่อมีการดำเนินการตัวดำเนินการภาษาการจัดการข้อมูล (DML) บนตาราง ทริกเกอร์ใช้เพื่อตรวจสอบความสมบูรณ์ของข้อมูลและย้อนกลับธุรกรรมทริกเกอร์คือขั้นตอน SQL ที่คอมไพล์แล้ว ซึ่งการดำเนินการจะถูกกำหนดเงื่อนไขโดยการเกิดเหตุการณ์บางอย่างภายใน ฐานสัมพันธ์ข้อมูล. การใช้ทริกเกอร์นั้นสะดวกสำหรับผู้ใช้ฐานข้อมูลเป็นส่วนใหญ่ แต่การใช้งานมักเกี่ยวข้องกับต้นทุนทรัพยากรเพิ่มเติมสำหรับการดำเนินการ I/O ในกรณีที่สามารถบรรลุผลลัพธ์เดียวกัน (โดยมีค่าใช้จ่ายน้อยกว่ามาก) สามารถทำได้โดยใช้ขั้นตอนการจัดเก็บหรือ
ทริกเกอร์เป็นเครื่องมือเซิร์ฟเวอร์ SQL พิเศษที่ใช้เพื่อรักษาความสมบูรณ์ของข้อมูลในฐานข้อมูล ข้อจำกัดด้านความสมบูรณ์ กฎ และค่าเริ่มต้นอาจไม่บรรลุระดับการทำงานที่ต้องการเสมอไป บ่อยครั้งจำเป็นต้องใช้อัลกอริธึมการยืนยันข้อมูลที่ซับซ้อนเพื่อให้มั่นใจในความน่าเชื่อถือและความเป็นจริง นอกจากนี้บางครั้งจำเป็นต้องติดตามการเปลี่ยนแปลงค่าตารางเพื่อที่จะ ในทางที่ถูกต้องเปลี่ยนแปลงข้อมูลที่เกี่ยวข้อง ทริกเกอร์ถือได้ว่าเป็นตัวกรองประเภทหนึ่งที่มีผลใช้บังคับหลังจากการดำเนินการทั้งหมดเสร็จสิ้นตามกฎเกณฑ์ ค่ามาตรฐาน ฯลฯ
ทริกเกอร์เป็นขั้นตอนการจัดเก็บชนิดพิเศษที่เซิร์ฟเวอร์เปิดใช้งานโดยอัตโนมัติเมื่อมีการพยายามเปลี่ยนแปลงข้อมูลในตารางที่เกี่ยวข้องกับทริกเกอร์ ทริกเกอร์แต่ละตัวเชื่อมโยงกับตารางเฉพาะ การแก้ไขข้อมูลทั้งหมดที่ทำจะถือเป็นธุรกรรมเดียว หากตรวจพบข้อผิดพลาดหรือการละเมิดความสมบูรณ์ของข้อมูล ธุรกรรมจะถูกย้อนกลับ การเปลี่ยนแปลงจึงเป็นสิ่งต้องห้าม การเปลี่ยนแปลงใดๆ ที่ทริกเกอร์ทำไว้แล้วก็จะถูกยกเลิกเช่นกัน
เฉพาะเจ้าของฐานข้อมูลเท่านั้นที่สามารถสร้างทริกเกอร์ได้ ข้อจำกัดนี้ช่วยให้คุณหลีกเลี่ยงการเปลี่ยนแปลงโครงสร้างของตารางโดยไม่ตั้งใจ วิธีการเชื่อมต่อวัตถุอื่นเข้ากับตาราง ฯลฯ
ทริกเกอร์เป็นเครื่องมือที่มีประโยชน์มากและในขณะเดียวกันก็เป็นอันตราย ดังนั้น หากตรรกะของการดำเนินการไม่ถูกต้อง คุณสามารถทำลายฐานข้อมูลทั้งหมดได้อย่างง่ายดาย ดังนั้นทริกเกอร์จึงต้องได้รับการดีบั๊กอย่างระมัดระวัง
ทริกเกอร์จะถูกดำเนินการโดยปริยายทุกครั้งที่เกิดขึ้น ซึ่งต่างจากรูทีนย่อยทั่วไป ทริกเกอร์เหตุการณ์นอกจากนี้ยังไม่มีข้อโต้แย้ง การสั่งงานบางครั้งเรียกว่าการเหนี่ยวไกปืน การใช้ทริกเกอร์ทำให้บรรลุเป้าหมายต่อไปนี้:
- ตรวจสอบความถูกต้องของข้อมูลที่ป้อนและบังคับใช้ข้อจำกัดด้านความสมบูรณ์ของข้อมูลที่ซับซ้อนซึ่งยากหรือเป็นไปไม่ได้ ในการรักษาโดยใช้ข้อจำกัดด้านความสมบูรณ์ที่ตั้งไว้บนโต๊ะ
- การออกคำเตือนเตือนให้คุณดำเนินการบางอย่างเมื่ออัปเดตตารางที่นำไปใช้ในลักษณะใดลักษณะหนึ่ง
- การรวบรวมข้อมูลการตรวจสอบโดยการบันทึกข้อมูลเกี่ยวกับ มีการเปลี่ยนแปลงและผู้ที่กระทำการนั้น
- การสนับสนุนการจำลองแบบ
รูปแบบพื้นฐานของคำสั่ง CREATE TRIGGER แสดงอยู่ด้านล่าง:
<Определение_триггера>::= สร้างทริกเกอร์ trigger_name ก่อน | หลังจาก<триггерное_событие>บน<имя_таблицы> <тело_триггера>
ทริกเกอร์เหตุการณ์ประกอบด้วยการแทรก การลบ และการอัปเดตแถวในตาราง ในกรณีหลังสำหรับ ทริกเกอร์เหตุการณ์คุณสามารถระบุชื่อคอลัมน์ตารางเฉพาะได้ ระยะเวลาของทริกเกอร์ถูกกำหนดโดยใช้คีย์เวิร์ด BEFORE (ทริกเกอร์เริ่มทำงานก่อนเหตุการณ์ที่เกี่ยวข้องกับทริกเกอร์จะถูกดำเนินการ) หรือ AFTER (หลังจากทริกเกอร์ถูกดำเนินการ)
การดำเนินการที่ทำโดยทริกเกอร์จะถูกระบุสำหรับแต่ละแถว (สำหรับแต่ละแถว) ที่ครอบคลุมโดยเหตุการณ์ที่กำหนด หรือเพียงครั้งเดียวสำหรับแต่ละเหตุการณ์ (สำหรับแต่ละคำสั่ง)
การกำหนด <список_старых_или_новых_псевдонимов> หมายถึงส่วนประกอบเช่นเก่าหรือ บรรทัดใหม่(เก่า / ใหม่) หรือตารางเก่าหรือใหม่ (ตารางเก่า / ตารางใหม่) เป็นที่ชัดเจนว่าค่าเก่าใช้ไม่ได้กับการแทรกเหตุการณ์ และค่าใหม่ใช้ไม่ได้กับการลบเหตุการณ์
ระบุว่า การใช้งานที่ถูกต้องตัวกระตุ้นอาจเป็นกลไกที่ทรงพลังมาก ข้อได้เปรียบหลักของพวกเขาก็คือ คุณสมบัติมาตรฐานจะถูกจัดเก็บไว้ในฐานข้อมูลและเปิดใช้งานอย่างสม่ำเสมอทุกครั้งที่มีการอัปเดต สิ่งนี้สามารถทำให้แอปพลิเคชันง่ายขึ้นอย่างมาก อย่างไรก็ตาม เป็นเรื่องที่ควรค่าแก่การกล่าวถึงข้อเสียโดยธรรมชาติของทริกเกอร์:
- ความซับซ้อน: เมื่อฟังก์ชันบางอย่างถูกย้ายไปยังฐานข้อมูล งานของการออกแบบ การใช้งาน และการดูแลระบบจะซับซ้อนมากขึ้น
- ฟังก์ชั่นที่ซ่อนอยู่: การย้ายฟังก์ชั่นบางอย่างลงในฐานข้อมูลและจัดเก็บเป็นทริกเกอร์ตั้งแต่หนึ่งตัวขึ้นไป บางครั้งส่งผลให้ฟังก์ชั่นบางอย่างถูกซ่อนไม่ให้ผู้ใช้เห็น แม้ว่าสิ่งนี้จะทำให้การดำเนินงานง่ายขึ้นในระดับหนึ่ง แต่น่าเสียดายที่อาจทำให้เกิดสิ่งที่ไม่คาดคิด อาจไม่เป็นที่ต้องการ และเป็นอันตรายได้ ผลข้างเคียงเนื่องจากในกรณีนี้ผู้ใช้ไม่สามารถควบคุมกระบวนการทั้งหมดที่เกิดขึ้นในฐานข้อมูลได้
- ผลกระทบด้านประสิทธิภาพ: ก่อนที่จะดำเนินการแต่ละคำสั่งเพื่อเปลี่ยนสถานะของฐานข้อมูล DBMS จะต้องตรวจสอบเงื่อนไขทริกเกอร์เพื่อพิจารณาว่าจำเป็นต้องใช้ทริกเกอร์สำหรับคำสั่งนี้หรือไม่ การคำนวณดังกล่าวส่งผลต่อประสิทธิภาพโดยรวมของ DBMS และในช่วงเวลาที่มีโหลดสูงสุด การลดลงอาจสังเกตเห็นได้ชัดเจนเป็นพิเศษ เห็นได้ชัดว่าเมื่อจำนวนทริกเกอร์เพิ่มขึ้น ต้นทุนค่าโสหุ้ยที่เกี่ยวข้องกับการดำเนินการดังกล่าวก็เพิ่มขึ้นเช่นกัน
ทริกเกอร์ที่เขียนไม่ถูกต้องอาจทำให้เกิดปัญหาร้ายแรง เช่น การล็อคที่ไม่ทำงาน ทริกเกอร์มีความสามารถ เวลานานบล็อกทรัพยากรจำนวนมาก ดังนั้นคุณควรเปิด ความสนใจเป็นพิเศษเพื่อลดข้อขัดแย้งในการเข้าถึง
การใช้งานทริกเกอร์ในสภาพแวดล้อม MS SQL Server
การใช้งาน MS SQL Server DBMS ใช้ตัวดำเนินการสร้างหรือแก้ไขทริกเกอร์ต่อไปนี้:
<Определение_триггера>::= (สร้าง | แก้ไข) TRIGGER trigger_name ON (table_name | view_name) ( ( ( FOR | AFTER | INSTEAD OF ) ( [ DELETE] [,] [ INSERT] [,] [ UPDATE] ) [ WITH APPEND ] [ NOT FOR การจำลอง ] AS sql_statement[...n] ) |. ( (สำหรับ | หลังจาก | แทน ) ( [,] ) [ พร้อมภาคผนวก] [ ไม่ใช่สำหรับการจำลอง] AS ( IF UPDATE(column_name) [ (และ | OR) UPDATE( 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 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 ช่วยให้คุณสร้างทริกเกอร์หลายรายการในแต่ละประเภท
ที่ สร้างทริกเกอร์ด้วยอาร์กิวเมนต์ NOT FOR REPLICATION ห้ามไม่ให้ทำงานในขณะที่ตารางกำลังถูกแก้ไขโดยกลไกการจำลองแบบ
โครงสร้าง AS sql_operator[...n] กำหนดชุดของคำสั่ง SQL และคำสั่งที่จะดำเนินการเมื่อมีการเรียกใช้ทริกเกอร์
โปรดทราบว่าการดำเนินการบางอย่างไม่ได้รับอนุญาตภายในทริกเกอร์ เช่น:
- การสร้าง แก้ไข และลบฐานข้อมูล
- การกู้คืน สำเนาสำรองฐานข้อมูลหรือบันทึกธุรกรรม
คำสั่งเหล่านี้ไม่ได้รับอนุญาตให้ดำเนินการเนื่องจากไม่สามารถย้อนกลับได้หากธุรกรรมที่เรียกใช้ทริกเกอร์ถูกย้อนกลับ ข้อห้ามนี้ไม่น่าจะส่งผลกระทบในทางใดทางหนึ่งต่อการทำงานของทริกเกอร์ที่สร้างขึ้น เป็นเรื่องยากที่จะค้นหาสถานการณ์ เช่น หลังจากเปลี่ยนแถวของตารางแล้ว คุณจะต้องกู้คืนข้อมูลสำรองบันทึกธุรกรรม
การเขียนโปรแกรมทริกเกอร์
เมื่อดำเนินการคำสั่งเพื่อเพิ่ม แก้ไข และลบบันทึก เซิร์ฟเวอร์จะสร้างตารางพิเศษสองตาราง: แทรกและ ลบแล้ว- ประกอบด้วยรายการแถวที่จะถูกแทรกหรือลบเมื่อธุรกรรมเสร็จสมบูรณ์ โครงสร้างของตารางที่แทรกและลบจะเหมือนกับโครงสร้างของตารางที่มีการกำหนดทริกเกอร์ ทริกเกอร์แต่ละตัวจะสร้างชุดตารางที่แทรกและลบของตัวเอง ดังนั้นจึงไม่มีทริกเกอร์อื่นใดที่สามารถเข้าถึงได้ ขึ้นอยู่กับประเภทของการดำเนินการที่ทำให้ทริกเกอร์ดำเนินการ เนื้อหาของตารางที่แทรกและลบอาจแตกต่างกัน:
- คำสั่ง INSERT – ตารางที่แทรกประกอบด้วยแถวทั้งหมดที่ผู้ใช้พยายามแทรกลงในตาราง จะไม่มีแถวเดียวในตารางที่ถูกลบ หลังจากที่ทริกเกอร์เสร็จสิ้น แถวทั้งหมดจากตารางที่แทรกไว้จะถูกย้ายไปยังตารางต้นฉบับ
- คำสั่ง DELETE – ตารางที่ถูกลบจะมีแถวทั้งหมดที่ผู้ใช้พยายามลบ ทริกเกอร์สามารถตรวจสอบแต่ละแถวและพิจารณาว่าได้รับอนุญาตให้ลบหรือไม่ จะไม่มีแถวเดียวในตารางที่แทรก
- คำสั่ง UPDATE - เมื่อดำเนินการตารางที่ถูกลบจะมีค่าแถวเก่าที่จะถูกลบเมื่อทริกเกอร์เสร็จสิ้น ค่าแถวใหม่มีอยู่ในตารางที่แทรก แถวเหล่านี้จะถูกเพิ่มลงในตารางต้นฉบับหลังจากที่เรียกใช้ทริกเกอร์สำเร็จ
หากต้องการรับข้อมูลเกี่ยวกับจำนวนแถวที่จะแก้ไขเมื่อทริกเกอร์เสร็จสมบูรณ์ คุณสามารถใช้ฟังก์ชัน @@ROWCOUNT
ส่งคืนจำนวนแถวที่ประมวลผลโดยคำสั่งสุดท้าย ควรเน้นย้ำว่าทริกเกอร์ไม่ได้ทำงานเมื่อมีการพยายามเปลี่ยนบรรทัดใดบรรทัดหนึ่ง แต่ในขณะที่คำสั่งเปลี่ยนถูกดำเนินการ คำสั่งดังกล่าวหนึ่งคำสั่งมีผลกับหลายแถว ดังนั้นทริกเกอร์จะต้องประมวลผลแถวเหล่านี้ทั้งหมด
หากทริกเกอร์ตรวจพบว่ามีการแทรก แก้ไข หรือลบแถวจากทั้งหมด 100 แถว มีเพียงแถวเดียวเท่านั้นที่ไม่ตรงตามเงื่อนไขที่กำหนด ก็จะไม่มีการแทรก ปรับเปลี่ยน หรือลบแถวใดเลย ลักษณะการทำงานนี้เกิดจากข้อกำหนดของธุรกรรม - จะต้องดำเนินการแก้ไขทั้งหมดหรือไม่ต้องดำเนินการเลย
ทริกเกอร์ดำเนินการเป็นธุรกรรมที่กำหนดไว้โดยปริยาย ดังนั้นคำสั่งควบคุมธุรกรรมจึงสามารถใช้ได้ภายในทริกเกอร์ โดยเฉพาะอย่างยิ่ง เมื่อตรวจพบการละเมิดข้อจำกัดด้านความสมบูรณ์ ต้องใช้คำสั่ง ROLLBACK TRANSACTION เพื่อยกเลิกทริกเกอร์และเลิกทำการเปลี่ยนแปลงใดๆ ที่ผู้ใช้พยายามทำ
คุณสามารถใช้ฟังก์ชัน COLUMNS_UPDATED() เพื่อรับรายการคอลัมน์ที่ได้รับการแก้ไขโดยคำสั่ง INSERT หรือ UPDATE ที่ทำให้ทริกเกอร์ดำเนินการ โดยจะส่งกลับเลขฐานสอง ซึ่งแต่ละบิตจะเริ่มต้นด้วยบิตที่มีนัยสำคัญน้อยที่สุด โดยจะสอดคล้องกับคอลัมน์หนึ่งของตาราง (ตามลำดับคอลัมน์เมื่อสร้างตาราง) หากบิตถูกตั้งค่าเป็น "1" แสดงว่าคอลัมน์ที่เกี่ยวข้องได้รับการแก้ไขแล้ว นอกจากนี้ ความจริงที่ว่าคอลัมน์มีการเปลี่ยนแปลงนั้นถูกกำหนดโดยฟังก์ชัน UPDATE (column_name) สำหรับลบทริกเกอร์
มีการใช้คำสั่ง
DROP TRIGGER (ทริกเกอร์_ชื่อ) [,...n]
นี่คือตัวอย่างการใช้ทริกเกอร์ตัวอย่างที่ 14.1 การใช้ทริกเกอร์เพื่อการดำเนินการตามข้อจำกัดด้านมูลค่า
- ในรายการที่เพิ่มลงในตารางธุรกรรม ปริมาณของผลิตภัณฑ์ที่ขายจะต้องไม่น้อยกว่ายอดคงเหลือจากตารางคลังสินค้า
คำสั่งสำหรับการแทรกบันทึกลงในตาราง Deal อาจเป็นเช่นนี้:
ทริกเกอร์ที่สร้างขึ้นควรตอบสนองต่อการดำเนินการในลักษณะต่อไปนี้: จำเป็นต้องยกเลิกคำสั่งหากในตารางคลังสินค้ายอดคงเหลือผลิตภัณฑ์น้อยกว่าปริมาณที่ขายของผลิตภัณฑ์ด้วยรหัสที่ป้อน (ในตัวอย่าง รหัสผลิตภัณฑ์ = 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 หน่วย
เมื่อขายหรือรับสินค้า จะต้องปรับปริมาณสินค้าคงคลังให้เหมาะสม หากสินค้ายังไม่มีอยู่ในสต็อก คุณจะต้องเพิ่มรายการที่เกี่ยวข้องลงในตารางคลังสินค้า ทริกเกอร์จะประมวลผลแถวที่เพิ่มเพียงแถวเดียวเท่านั้น< =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 ALTER TRIGGER Trigger_ins ในธุรกรรมสำหรับการแทรกตามที่ประกาศ @x INT, @y INT IF @@ROWCOUNT=1 --บันทึกถูกเพิ่มลงในตารางธุรกรรมเกี่ยวกับการจัดส่งสินค้า BEGIN --ปริมาณของสินค้าที่ขายจะต้องไม่เป็น -- น้อยกว่ายอดคงเหลือจากตารางคลังสินค้าหากไม่มีอยู่ (SELECT * FROM แทรก WHERE -inserted.quantity
ตัวอย่างที่ 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
ในการทำธุรกรรมทั้งหมดที่มีสินค้าที่มีรหัสเท่ากับ 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
ALTER TRIGGER Trigger_upd บนธุรกรรมสำหรับการอัปเดตตามที่ประกาศ @x INT, @x_old INT, @y INT, @y_old INT ,@o INT ประกาศ CUR1 เคอร์เซอร์สำหรับรหัสรายการที่เลือก จำนวนจากที่แทรกไว้ ประกาศ CUR2 เคอร์เซอร์สำหรับรหัสรายการที่เลือก จำนวนจากที่ถูกลบ เปิด 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ทริกเกอร์ที่ช่วยให้คุณสามารถเลิกทำการเปลี่ยนแปลงกับบางระเบียนเท่านั้น และทำการเปลี่ยนแปลงในส่วนที่เหลือ
สิ่งกระตุ้นฐานข้อมูลเป็นบล็อก PL/SQL ที่มีชื่อออกแบบมาเป็นพิเศษซึ่งจัดเก็บไว้ในฐานข้อมูล ทริกเกอร์แต่ละตัวเชื่อมโยงกับตารางเฉพาะ และ ORACLE เปิดใช้งานโดยอัตโนมัติเมื่อมีการดำเนินการคำสั่ง DML อันใดอันหนึ่ง (INSERT, DELETE, UPDATE) หรือทั้งสองอย่างรวมกันบนตารางนี้
วัตถุประสงค์ของทริกเกอร์
2) ป้องกันการทำธุรกรรมที่ไม่ถูกต้อง;
3) ดำเนินการตามขั้นตอนสำหรับการตรวจสอบสิทธิ์การเข้าถึงและความลับของข้อมูลอย่างครอบคลุม
4) การสร้างนิพจน์บางส่วนตามค่าที่มีอยู่ในคอลัมน์ของตาราง
5) การใช้กฎทางธุรกิจที่ซับซ้อนสำหรับการประมวลผลข้อมูล (ความสามารถในการติดตาม "เสียงสะท้อน" เช่นความสามารถในการอัปเดตข้อมูลของตารางที่เกี่ยวข้องเมื่อมีการเปลี่ยนแปลงตารางหนึ่ง)
การสร้างและเปิดใช้งานทริกเกอร์ในการสร้างและทริกเกอร์ทริกเกอร์โดยอัตโนมัติ จะใช้ไวยากรณ์ทั่วไปต่อไปนี้:
สร้างทริกเกอร์ trigger_name
(ก่อน | หลัง)
(แทรก | ลบ | อัปเดต)
บน table_name
< PL/SQL_блок >
หากมีคำหลักหรือแทนที่อยู่ ทริกเกอร์จะถูกสร้างขึ้นใหม่หากมีอยู่แล้ว
ก่อนการออกแบบ | AFTER ระบุว่าเมื่อใดที่ทริกเกอร์ถูกยิง ตัวเลือก BEFORE หมายความว่าทริกเกอร์จะเริ่มทำงานก่อนการดำเนินการคำสั่ง DML ที่ทริกเกอร์ ตัวเลือก AFTER หมายความว่าทริกเกอร์จะเริ่มทำงานหลังจากการดำเนินการคำสั่ง DML ที่เปิดใช้งาน
แทรก | ลบ | UPDATE ระบุประเภทของคำสั่ง DML ที่เรียกใช้ทริกเกอร์ อนุญาตให้ใช้ การดำเนินการเชิงตรรกะหรือ ระบุชุดของตัวดำเนินการเปิดใช้งาน เช่น: INSERT OR DELETE เมื่อใช้ตัวเลือก UPDATE หากมีการระบุรายการคอลัมน์ ทริกเกอร์จะเริ่มทำงานเมื่อมีการแก้ไขคอลัมน์ใดคอลัมน์หนึ่งที่ระบุ หากไม่มีรายการคอลัมน์ ทริกเกอร์จะเริ่มทำงานเมื่อคอลัมน์ใดๆ ของตารางที่เกี่ยวข้องกับทริกเกอร์เปลี่ยนแปลง
โครงสร้าง FOR EACH ROW บ่งบอกถึงลักษณะของเอฟเฟกต์ของทริกเกอร์: สตริงหรือตัวดำเนินการ หากมีส่วนคำสั่ง FOR EACH ROW แสดงว่าทริกเกอร์จะเป็นสตริง ในกรณีที่ไม่มีอยู่ ทริกเกอร์คือตัวดำเนินการ ทริกเกอร์คำสั่งจะเริ่มทำงานหนึ่งครั้งก่อนหรือหลังคำสั่ง DML ที่เรียกใช้ทริกเกอร์ถูกดำเนินการ ไม่ว่าแถวในตารางที่เกี่ยวข้องกับทริกเกอร์จะได้รับการแก้ไขกี่แถวก็ตาม ทริกเกอร์แถวจะเริ่มทำงานหนึ่งครั้งสำหรับแต่ละแถวที่ถูกแก้ไขโดยคำสั่ง DML ที่เริ่มการทำงานของทริกเกอร์
โดยการใช้ คำหลักเมื่อใดที่สามารถระบุได้ ข้อ จำกัด เพิ่มเติมไปยังแถวของตารางที่เกี่ยวข้องกับทริกเกอร์ เมื่อมีการแก้ไขทริกเกอร์ที่สามารถเริ่มทำงานได้
โครงสร้าง PL/SQL_block แสดงถึงบล็อก PL/SQL ที่ ORACLE ทำงานเมื่อมีการเรียกใช้ทริกเกอร์
การจำแนกประเภทของทริกเกอร์โดยพื้นฐานแล้วทริกเกอร์มีสิบสองประเภท ประเภททริกเกอร์ถูกกำหนดโดยการรวมกันของพารามิเตอร์สามตัวต่อไปนี้:
1) ลักษณะของผลกระทบของทริกเกอร์ต่อแถวของตารางที่เกี่ยวข้อง (สตริงหรือตัวดำเนินการ)
2) ช่วงเวลาที่ทริกเกอร์: ก่อน (ก่อน) หรือหลัง (หลัง) การดำเนินการของคำสั่ง DML ที่เปิดใช้งานทริกเกอร์;
3) ประเภทของตัวดำเนินการ DML ที่เปิดใช้งานทริกเกอร์ (INSERT, DELETE, UPDATE);
ลำดับที่ทริกเกอร์ถูกเปิดใช้งานหากตารางมีทริกเกอร์หลายประเภท ทริกเกอร์เหล่านั้นจะถูกเปิดใช้งานตามรูปแบบต่อไปนี้:
1) ตัวดำเนินการ BEFORE ดำเนินการ (หากมีหลายรายการก็ไม่สามารถพูดได้เกี่ยวกับลำดับการดำเนินการ)
2) เรียกใช้ทริกเกอร์สตริง BEFORE;
3) ตัวดำเนินการ DML ที่เปิดใช้งานทริกเกอร์จะถูกดำเนินการ ตามด้วยการตรวจสอบข้อจำกัดด้านความสมบูรณ์ของข้อมูลทั้งหมด
4) ดำเนินการทริกเกอร์สตริง AFTER ตามด้วยการตรวจสอบข้อจำกัดด้านความสมบูรณ์ของข้อมูลทั้งหมด
5) ทริกเกอร์ตัวดำเนินการ AFTER จะถูกดำเนินการ
เพรดิเคตทริกเกอร์หากทริกเกอร์ระบุชุดของตัวดำเนินการ DML ที่เปิดใช้งานทริกเกอร์ (เช่น INSERT หรือ DELETE) ดังนั้นเพื่อให้รับรู้ว่าตัวดำเนินการ DML เฉพาะใดที่ถูกดำเนินการบนตารางที่เกี่ยวข้องกับทริกเกอร์ จะใช้เพรดิเคตของทริกเกอร์: การแทรก การลบ การอัพเดต พวกเขาเป็นตัวแทน ฟังก์ชันลอจิคัลโดยส่งคืน TRUE หากประเภทของตัวดำเนินการเปิดใช้งานตรงกับประเภทของเพรดิเคต และส่งคืน FALSE เป็นอย่างอื่น เพื่อระบุการกระทำเดียวกันเมื่อดำเนินการคำสั่ง DML ที่แตกต่างกัน ตัวดำเนินการแบบมีเงื่อนไขเพรดิเคตทริกเกอร์จะถูกรวมเข้าด้วยกันโดยใช้การดำเนินการเชิงตรรกะ
การบันทึกหลอกสำหรับทริกเกอร์สตริง มีโครงสร้างพิเศษที่อนุญาตให้เข้าถึงทั้งค่าเก่าที่อยู่ในนั้นก่อนการแก้ไขและค่าใหม่ที่จะปรากฏในแถวหลังการแก้ไขเมื่อดำเนินการการดำเนินการ DML บนแถวของตาราง โครงสร้างเหล่านี้เรียกว่าบันทึกเทียมและระบุทั้งเก่าและใหม่ โครงสร้างของบันทึกหลอกเหล่านี้จะเหมือนกันกับโครงสร้างของแถวของตารางที่กำลังแก้ไข แต่คุณสามารถดำเนินการได้เฉพาะในแต่ละฟิลด์ของบันทึกหลอกเท่านั้น เข้าถึงฟิลด์ของบันทึกหลอกได้ตามรูปแบบต่อไปนี้: ก่อนที่จะวางสัญลักษณ์ “:” เก่าหรือใหม่ จากนั้นชื่อของฟิลด์จะถูกระบุผ่านจุด ค่าที่ฟิลด์บันทึกหลอกใช้เมื่อเปิดใช้งานคำสั่ง DML ถูกกำหนดไว้ดังนี้
คำสั่ง INSERT - pseudo-record:new เทียบเท่ากับแถวที่กำลังแทรก และ pseudo-record:old เป็น NULL ในทุกฟิลด์
คำสั่งลบ– pseudo-record:old เทียบเท่ากับแถวที่กำลังถูกลบ และ pseudo-record:new มีค่าเป็น NULL ในทุกฟิลด์
คำสั่ง UPDATE - pseudo-record:new เทียบเท่ากับแถวที่เกิดจากการแก้ไข และ pseudo-record:old ในทุกฟิลด์มี มูลค่าเดิมเส้น
การเปิดและปิดทริกเกอร์ทริกเกอร์ที่จัดเก็บไว้ในฐานข้อมูลสามารถปิดใช้งานชั่วคราวได้โดยไม่ต้องลบออกจากฐานข้อมูล เมื่อต้องการทำเช่นนี้ ใช้คำสั่งต่อไปนี้:
เปลี่ยนทริกเกอร์ trigger_name ปิดการใช้งาน;
คุณสามารถเปิดใช้งานทริกเกอร์ได้หลังจากช่วงระยะเวลาหนึ่งโดยใช้คำสั่ง
เปลี่ยนทริกเกอร์ trigger_name เปิดใช้งาน;
คุณสามารถปิดใช้งานหรืออนุญาตให้ทริกเกอร์ทั้งหมดที่เกี่ยวข้องกับตารางบางตารางได้โดยใช้คำสั่ง
แก้ไขตาราง table_name (ปิดการใช้งาน | เปิดใช้งาน) ทริกเกอร์ทั้งหมด;
โดยที่ตัวเลือก DISABLE ใช้เพื่อปิดการใช้งาน และใช้ตัวเลือก ENABLE เพื่อเปิดใช้งานทริกเกอร์ทั้งหมดของตารางที่กำหนด
การลบทริกเกอร์ออกจากฐานข้อมูลการทำลายทริกเกอร์ เช่น การลบทริกเกอร์ออกจากฐานข้อมูล ทำได้โดยใช้คำสั่งต่อไปนี้:
วางทริกเกอร์ trigger_name;
การรับข้อมูลเกี่ยวกับทริกเกอร์ทริกเกอร์จะถูกจัดเก็บไว้ในฐานข้อมูล ดังนั้นข้อมูลเกี่ยวกับทริกเกอร์สามารถดึงได้จากมุมมองพจนานุกรมข้อมูล USER_TRIGGERS เช่น ด้วยคำสั่งต่อไปนี้:
SELECT * จาก USER_TRIGGERS;
ตัวอย่าง.
1. สร้างทริกเกอร์ที่ก่อนที่จะแทรกแถวถัดไปลงในตาราง KNIGA_POSTAVKA ให้ตรวจสอบการมีอยู่ของรหัสหนังสือที่ระบุในตาราง KNIGA หากไม่มีรหัสหนังสือที่ระบุในตาราง KNIGA ควรสร้างข้อยกเว้นด้วยข้อความที่เกี่ยวข้อง
แถวใหม่จะถูกเพิ่มลงในตาราง KNIGA_POSTAVKA โดยใช้คำสั่ง INSERT เนื่องจากทริกเกอร์ต้องเริ่มทำงานก่อนดำเนินการแต่ละคำสั่ง INSERT ดังนั้นจึงต้องเป็นสตริงก่อนทริกเกอร์ เพื่อรักษาความสมบูรณ์ของข้อมูล จำเป็นต้องตรวจสอบว่ารหัสหนังสือที่ป้อนอยู่ในตาราง KNIGA ด้วยหรือไม่ เมื่อต้องการทำเช่นนี้ ให้ใช้ซับเดียว คำสั่งเลือกข้อมูลถูกดึงมาจากตาราง KNIGA โดยที่ฟิลด์ BOOK_CODE ของบันทึกหลอกถูกใช้ในเงื่อนไขการเลือก: ใหม่ หากจำนวนแถวที่มีรหัสหนังสือที่กำหนดในตาราง KNIGA คือ เท่ากับศูนย์ข้อยกเว้นจะถูกส่งออกไปและข้อความที่เกี่ยวข้องจะออก
ทริกเกอร์ TR1 ถูกสร้างขึ้นโดยการป้อนคำสั่งต่อไปนี้:
สร้างหรือแทนที่ทริกเกอร์ TR1
ก่อนใส่ KNIGA_POSTAVKA
เลือก COUNT(*) เข้าสู่ KOL จาก KNIGA
โดยที่ BOOK_CODE = :NEW.BOOK_CODE;
ถ้า KOL = 0 ดังนั้น RAISE_APPLICATION_ERROR
(–20212,"ตาราง KNIGA ไม่มีข้อมูลเกี่ยวกับหนังสือเล่มนี้");
สามารถตรวจสอบการทำงานของทริกเกอร์ TR1 ได้โดยดำเนินการคำสั่งต่อไปนี้ ซึ่งจะแทรกแถวลงในตาราง KNIGA_POSTAVKA และทำให้ทริกเกอร์ TR2 เปิดใช้งาน:
ใส่ลงในค่า KNIGA_POSTAVKA(21,15,'Ivanov',15,
เนื่องจากรหัสหนังสือ 15 ไม่ได้อยู่ในตาราง KNIGA จึงมีข้อยกเว้นเกิดขึ้นและจะมีการออกข้อความที่เกี่ยวข้อง
2. สร้างทริกเกอร์ที่ห้ามไม่ให้เข้าสู่แถวของตาราง KNIGA โดยมีค่าฟิลด์ PRICE มากกว่า 5,000 รูเบิล รวมถึงเพิ่มราคาหนังสือซึ่งข้อมูลที่เก็บไว้ในตาราง KNIGA มากกว่า 20% ในกรณีที่มีการละเมิด ข้อกำหนดนี้จะต้องส่งข้อยกเว้นพร้อมกับข้อความที่เกี่ยวข้อง
เนื่องจากแถวใหม่ถูกเพิ่มลงในตาราง KNIGA อันเป็นผลมาจากการดำเนินการคำสั่ง INSERT และค่าของฟิลด์ PRICE ในตาราง KNIGA ซึ่งมีราคาของหนังสือจึงสามารถเปลี่ยนแปลงได้อันเป็นผลมาจากการดำเนินการคำสั่ง UPDATE ทริกเกอร์ระบุชุดของคำสั่ง DML ที่ทริกเกอร์ เนื่องจากทริกเกอร์ต้องเริ่มทำงานก่อนดำเนินการแต่ละคำสั่ง DML ที่ระบุ ดังนั้นจึงเป็นสตริงก่อนทริกเกอร์ เนื่องจากการดำเนินการที่ทำโดยทริกเกอร์จะแตกต่างกันสำหรับแต่ละคำสั่ง DML ที่ทริกเกอร์ซึ่งแก้ไขตาราง KNIGA เพรดิเคตทริกเกอร์ที่เกี่ยวข้อง INSERTING และ UPDAITING จะถูกใช้เพื่อจดจำประเภทของคำสั่ง DML เนื่องจากเมื่อแทรกแถวใหม่จะต้องตรวจสอบค่าใหม่ของฟิลด์ PRICE และเมื่อแก้ไขค่าของฟิลด์ PRICE จะต้องเปรียบเทียบค่าใหม่กับค่าเก่าจึงจำเป็นต้องใช้บันทึกหลอก : ใหม่และ: เก่า
การสร้างทริกเกอร์ TR2 ทำได้โดยการป้อนคำสั่งต่อไปนี้:
สร้างหรือแทนที่ทริกเกอร์ TR2
ก่อนที่จะแทรกหรืออัปเดตราคาของ KNIGA
หากใส่แล้ว
ถ้า: ใหม่ ราคา > 5,000 แล้ว
RAISE_APPLICATION_ERROR
(–20102, "ไม่สามารถสร้างบันทึกที่มีราคาหนังสือ> 5,000 ในตาราง KNIGA ได้");
หากอัปเดตแล้ว
IF:NEW.PRICE > :OLD.PRICE*1.2 แล้ว
RAISE_APPLICATION_ERROR
(–20103, “ในตาราง KNIGA คุณไม่สามารถเปลี่ยนแปลงราคาหนังสือได้มากกว่า 20%”);
สามารถทดสอบผลกระทบของทริกเกอร์ TR2 ได้โดยดำเนินการคำสั่งต่อไปนี้ ซึ่งโดยการแทรกแถวลงในตาราง KNIGA และอัปเดตแถวในตาราง KNIGA จะทำให้เปิดใช้งานได้
ตัวดำเนินการสำหรับการแทรกแถวลงในตาราง KNIGA ทำให้ทริกเกอร์ TR2 เปิดใช้งาน:
ใส่ลงในค่า KNIGA (21, "Dune", "Herbert", 5268, "Ast",
"มหัศจรรย์");
ตัวดำเนินการอัปเดตแถวในตาราง KNIGA ทำให้ทริกเกอร์ TR2 ถูกเปิดใช้งาน:
อัพเดทชุด KNIGA ราคา=6000;
เนื่องจากข้อความเหล่านี้ละเมิดข้อกำหนดสำหรับมูลค่าและการเปลี่ยนแปลงราคาหนังสือ จึงจะมีการสร้างข้อยกเว้นในทุกกรณีและจะมีการออกข้อความที่เกี่ยวข้อง
3. สร้างทริกเกอร์ที่เข้าสู่ตาราง STAT ที่สร้างขึ้นซึ่งมีคอลัมน์:
ชื่อผู้จัดพิมพ์ – IZD,
จำนวนหนังสือในประเภท "นวนิยาย" - KOL_ROM
จำนวนหนังสือประเภทนิยายวิทยาศาสตร์ – KOL_FAN
ด้วยการปรับเปลี่ยนตารางแต่ละครั้ง KNIGA จะสร้างและป้อนจำนวนหนังสือทั้งหมดสำหรับสำนักพิมพ์แต่ละแห่งในคอลัมน์ที่เกี่ยวข้องของตาราง STAT ในบริบทของหัวข้อที่ระบุ: "นวนิยาย" และ "นิยาย"
การแก้ไขตาราง KNIGA ดำเนินการโดยดำเนินการคำสั่ง DML ต่อไปนี้: INSERT, DELETE หรือตัวดำเนินการ UPDATE ซึ่งจะแก้ไขค่าของคอลัมน์ GENRE ในตาราง KNIGA เนื่องจากการดำเนินการเพื่อสร้างข้อมูลในตาราง STAT จะดำเนินการหลังจากการดำเนินการของตัวดำเนินการแต่ละรายที่แก้ไขตาราง KNIGA นี่คือทริกเกอร์ตัวดำเนินการ AFTER ตามประเภท เนื่องจากการดำเนินการที่ทำโดยทริกเกอร์จะเหมือนกันสำหรับโอเปอเรเตอร์ทุกประเภทที่เปิดใช้งาน ดังนั้นเพรดิเคตของทริกเกอร์จะไม่ถูกนำมาใช้ ก่อนที่จะสร้างทริกเกอร์ ต้องสร้างตาราง STAT ก่อน
การสร้างตาราง STAT สามารถทำได้โดยการป้อนชุดคำสั่งต่อไปนี้:
วางสถิติตาราง;
สร้างตารางสถิติ
(อิซดี วาร์ชาร์2(15),
KOL_ROM หมายเลข (7)
KOL_FAN หมายเลข (7)
การสร้างทริกเกอร์ TR3 ทำได้โดยการป้อนคำสั่งต่อไปนี้:
สร้างหรือแทนที่ทริกเกอร์ TR3
หลังจากแทรกหรือลบหรืออัปเดตประเภท
เคอร์เซอร์ V1 เลือกสำนักพิมพ์แล้ว
นับ(ชื่อ) KOL1
จาก KNIGA โดยที่ประเภท = "นวนิยาย"
จัดกลุ่มตามสำนักพิมพ์;
เคอร์เซอร์ V2 เลือกสำนักพิมพ์
นับ(ชื่อ) KOL2
จาก KNIGA โดยที่ประเภท = "นิยาย"
จัดกลุ่มตามสำนักพิมพ์;
ลบออกจากสถานะ;
สำหรับ Z1 ใน V1 LOOP
แทรกลงในค่าสถิติ (Z1.PUBLISHER,
สำหรับ Z1 ใน V2 LOOP
อัปเดตชุดสถิติ KOL_FAN = Z1.KOL2
โดยที่ IZD = Z1.PUBLISHER;
หาก SQL% ไม่พบแล้ว
แทรกลงในค่าสถิติ (Z1.PUBLISHER, 0,
สามารถทดสอบการทำงานของทริกเกอร์ได้โดยดำเนินการคำสั่งต่อไปนี้ ซึ่งโดยการแทรกแถวลงในตาราง KNIGA ลบแถวและอัปเดตแถวในตาราง KNIGA ซึ่งจะทำให้ทริกเกอร์ TR3 เปิดใช้งาน
ตัวดำเนินการสำหรับการแทรกแถวลงในตาราง KNIGA ทำให้ทริกเกอร์ TR3 เปิดใช้งาน:
แทรกลงในค่า KNIGA (46, "Heretics of Dune", "Herbert",368,
"แอสต์", "มหัศจรรย์");
แทรกลงในค่า KNIGA (42, "Ingvar และ Alder",
"นิกิติน", 168, "Ast", "โรมัน");
ตัวดำเนินการสำหรับการลบแถวออกจากตาราง KNIGA ทำให้ทริกเกอร์ TR3 เปิดใช้งาน:
ลบ KNIGA โดยที่ NAME = "คอสแซค";
ตัวดำเนินการแก้ไขแถวในตาราง KNIGA ที่ทำให้ทริกเกอร์ TR3 เปิดใช้งาน:
อัพเดท KNIGA SET GENRE="Fiction" WHERE NAME =
"อิงวาร์และออลเดอร์";
คุณสามารถดูข้อมูลในตาราง STAT ด้วยคำสั่งต่อไปนี้
มีบทความมากมายบนอินเทอร์เน็ตเกี่ยวกับทริกเกอร์ sql แต่ฉันจะเพิ่มอีกหนึ่งบทความพร้อมตัวอย่างที่เหมาะสมเพื่อเสริมเนื้อหาสำหรับผู้ที่ “รู้” และเพื่อให้เข้าใจเนื้อหาได้ดีขึ้นสำหรับผู้ที่เพิ่งเริ่มเข้าใจ “เซนของ sql” ในขณะเดียวกัน ฉันจะสร้างการอภิปรายในหัวข้อนี้
ผมขอจองไว้ก่อนว่าความคิดเห็นของผมเป็นเพียงความคิดเห็นของผมเองและบางครั้งก็มีความเด็ดขาดมาก ด้วยเหตุผลหลายประการ คุณต้องทำงานกับไซต์ที่มีการโหลดสูงและแอปพลิเคชันเว็บที่ซับซ้อน
ประสบการณ์อันมีค่าอย่างหนึ่งที่ได้เรียนรู้จากการทำงานคือการติดตามลำดับความสำคัญและสถิติ มันหมายความว่าอะไร? ง่ายมาก: ถ้าคุณมีบล็อกและมีผู้เยี่ยมชม 2-3-4-10,012 ล้านคนต่อวัน และบทความถูกเขียนเพียง 1-2-3-3435 ครั้งต่อวัน (ลำดับความสำคัญน้อยกว่าจำนวนการดู) จากนั้นความเร็วในการบันทึกบทความ (และความซับซ้อนของสิ่งนี้) ที่สัมพันธ์กับความเร็วในการแสดงบทความอาจน้อยลงตามสัดส่วน ยิ่งเราแสดงมากเท่าใด การแสดงผลก็จะยิ่งมีความสำคัญมากขึ้นเท่านั้น ไม่ใช่การบันทึกบทความ/หน้า/ตาราง ซึ่งไม่ได้หมายความว่าคุณสามารถผ่อนคลายได้ การบันทึกบทความในบล็อกภายใน 3-5-10 วินาทีนั้นอยู่ภายในขอบเขตของความเพียงพอ แต่การสร้างหน้าในเวลามากกว่า 2 วินาที (+ ในขณะที่โหลดสคริปต์และสไตล์พร้อมรูปภาพ) เกือบจะเป็น "ไซต์ที่ช้ามาก ฉันจะอ่านอย่างอื่น" และที่แย่กว่านั้นคือ "ฉันจะไปซื้อมันที่อื่น"
หากเราคำนึงถึงเว็บไซต์ทั่วไปที่มีการโหวต/กรรม ความคิดเห็น ตัวนับการแสดงผลหน้าเว็บ ฯลฯ นักพัฒนาจำนวนมากจะนึกถึงโครงสร้างเช่น SELECT count(*) FROM comment WHERE comment.page=page_id ทันที ลองคิดคำนวณจำนวนเรตติ้งและจำนวนความคิดเห็นสำหรับแต่ละบทความดูสิ โอ้ เรามี 10 บทความจากแต่ละหัวข้อในหน้าหลัก ด้วยการรับส่งข้อมูล 10 คนต่อวินาที บน VPS โดยเฉลี่ย คุณสามารถจ่ายแบบสอบถาม SQL ได้ 60-100 รายการต่อหน้า (สวัสดี Bitrix)
แต่ลงนรกด้วยเนื้อเพลง (ฉันคงจะเบื่อมันแล้ว) ข้อมูลเปล่า:
ตารางบล็อก
สร้างตารางหากไม่มี `blog` (`id` int(11) ไม่เป็นโมฆะ AUTO_INCREMENT, `ชื่อ` varchar(128) ไม่เป็นโมฆะ, `ข้อความ` ข้อความไม่เป็นโมฆะ, `การสร้าง` วันที่และเวลาไม่เป็นโมฆะ, `การแก้ไข` วันที่และเวลาไม่เป็นโมฆะ , `img` varchar(128) ไม่เป็นโมฆะ ค่าเริ่มต้น "default.png", `สถานะ` Tinyint(4) ไม่เป็นโมฆะ ค่าเริ่มต้น "2", `user_id` int(11) ไม่เป็นโมฆะ, `rate` int(11) ไม่เป็นโมฆะ , `relax_type` Tinyint(4) ไม่เป็นโมฆะ, `ตัวจับเวลา` การประทับเวลา ไม่เป็นโมฆะ ค่าเริ่มต้น CURRENT_TIMESTAMP, `การแข่งขัน` Tinyint(1) ไม่เป็นโมฆะ ค่าเริ่มต้น "0", `มุมมอง` int(11) ไม่เป็นโมฆะ ค่าเริ่มต้น "0", `ความคิดเห็น ` int(11) ไม่เป็นโมฆะ, `url` varchar(128) ไม่เป็นโมฆะ, คีย์หลัก (`id`), คีย์เฉพาะ `url` (`url`), คีย์ `country_id` (`country_id`), คีย์ `user_id ` (`user_id`), คีย์ `สถานะ` (`สถานะ`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1456435 ;
ตารางความคิดเห็น
สร้างตารางหากไม่มี `ความคิดเห็น` (`owner_name` varchar(50) ไม่เป็นโมฆะ, `owner_id` int(12) ไม่เป็นโมฆะ, `id` int(12) ไม่เป็นโมฆะ AUTO_INCREMENT, `parent_id` int(12) ค่าเริ่มต้นเป็นโมฆะ, `user_id` int(12) DEFAULT NULL, `text` text, `creation` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `status` int(1) NOT NULL DEFAULT "0", คีย์หลัก (`id`), KEY `owner_name` ( `owner_name`,`owner_id`), KEY `parent_id` (`parent_id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=243254252 ;
อย่างที่คุณเห็นในตารางบล็อก แต่ละบทความจะมีตัวนับความคิดเห็น (ช่องแสดงความคิดเห็น)
แนวปฏิบัติทั่วไป:
1. เพิ่มความคิดเห็น - เพิ่มตัวนับสำหรับบล็อก
2. ลบ/ซ่อนความคิดเห็น - ลดตัวนับ
การทำเช่นนี้ในโค้ดนั้นสะดวกและคุ้นเคย แต่มีเครื่องมือที่สะดวกกว่า - ทริกเกอร์
ดังนั้นเราจึงมี 2 กิจกรรม (อันที่จริง 3): การสร้างความคิดเห็นและการลบ (เหตุการณ์ที่สามคือการเปลี่ยนแปลงสถานะ (“การลบ” การแบน ฯลฯ)
ลองพิจารณาเฉพาะการสร้างและการลบ และปล่อยให้การเปลี่ยนสถานะเป็นการบ้านของคุณ
มีคุณลักษณะอย่างหนึ่งในตัวอย่าง: ความคิดเห็นสามารถใช้ได้กับบทความหลายประเภท
การสร้างความคิดเห็น:
สร้างทริกเกอร์ `add_count_comment` หลังจากแทรกใน `ความคิดเห็น` สำหรับแต่ละแถวเริ่มต้น // ในบัญชีส่วนตัวของผู้ใช้ เราจะนับจำนวนความคิดเห็นที่เขาเขียน อัปเดตผู้ใช้ SET user.countcomment= user.countcomment+1 WHERE user.id = NEW.user_id ; // พิจารณาว่าความคิดเห็นหมายถึงอะไร และเพิ่มตัวนับในตารางเหล่านี้ทันที CASE NEW.`owner_name` เมื่อ "Blog" จากนั้น UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`+1 WHERE `บล็อก `.id = ใหม่.`owner_id` ; เมื่อ "บทความ" แล้วอัปเดต `บทความ` SET `article`.`comment` = `article`.`comment`+1 WHERE `article`.`id` = NEW.`owner_id` ; เมื่อ "PopulatePlace" จากนั้นอัปเดต `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`+1 WHERE `populate_place`.`id` = NEW.`owner_id` ; จบคดี; // ที่นี่เราทำให้การทำงานกับฟีดข่าวง่ายขึ้นสำหรับตัวเราเอง // เราเขียน url ของบทความทันที เพื่อที่เราจะได้ไม่ทำการเลือกที่ไม่จำเป็น กรณีใหม่`owner_name` เมื่อ "บล็อก" จากนั้นตั้งค่า userurl = (เลือก URL จาก `blog` โดยที่ `blog`.id= NEW.`owner_id`); เมื่อ "บทความ" แล้วตั้งค่า userurl = (เลือก url จาก `บทความ` WHERE article.id=NEW.`owner_id`); เมื่อ "PopulatePlace" จากนั้นตั้งค่า userurl = ``; จบคดี; // เขียนชื่อบทความทันทีเพื่อไม่ให้ทำการเลือก THEN CASE NEW.`owner_name` WHEN "Blog" THEN SET usertitle = (เลือกชื่อจาก `blog` โดยที่ blog.id=NEW.`owner_id`) ; เมื่อ "บทความ" แล้วตั้งค่า usertitle = (เลือกชื่อจาก `บทความ` โดยที่ article.id=NEW.`owner_id`); เมื่อ "PopulatePlace" จากนั้นตั้งค่า usertitle = ` `; จบคดี; แทรกค่า user_has_events ลงในค่า (NEW.user_id,NEW.id,"ความคิดเห็น",ตอนนี้(),userurl , usertitle); จบ
เช่นเดียวกับการลบความคิดเห็น:
สร้างทริกเกอร์ `del_count_comment` หลังจากลบ `ความคิดเห็น` สำหรับแต่ละแถว เริ่มต้นอัปเดตชุดผู้ใช้ user.countcomment= user.countcomment -1 WHERE user.id = OLD.user_id; CASE OLD.`owner_name` เมื่อ "บล็อก" จากนั้นอัปเดต `blog` SET `blog`.`comment` = `blog`.`comment`-1 WHERE `blog`.`id` = OLD.`owner_id` ; เมื่อ "บทความ" แล้วอัปเดต `บทความ` SET `article`.`comment` = `article`.`comment`-1 โดยที่ `article`.`id` = OLD.`owner_id` ; เมื่อ "PopulatePlace" จากนั้นอัปเดต `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`-1 WHERE `populate_place`.`id` = OLD.`owner_id` ; จบคดี; จบ
และสิ่งที่เราได้รับ:
1. เมื่อแทรกความคิดเห็น เราจะคำนวณจำนวนความคิดเห็นสำหรับวัตถุความคิดเห็นเฉพาะ (บทความ หน้า หมายเหตุ) โดยอัตโนมัติโดยใช้เซิร์ฟเวอร์ sql
2. เราได้สร้างฟีดข่าว (สวัสดีชาวโซเชียลเน็ตเวิร์กทั้งหมด ฯลฯ)
3. เมื่อคุณลบความคิดเห็น เราจะหักข้อมูลทั้งหมด
4. เราไม่ได้ใช้เครื่องมือกรอบงานใดๆ
5. การดึงข้อมูลที่จำเป็นทั้งหมดเกิดขึ้นอย่างรวดเร็ว (เพียง 1 คำขอเมื่อแสดงเพจ ยกเว้นข้อมูล "ซ้าย" อื่น ๆ ในนั้น)
นอกจากนี้เรายังมีสฟิงซ์ซึ่งจะคัดเลือกบทความที่มีการเปลี่ยนแปลงในนาทีสุดท้ายเป็นระยะ เพื่อจุดประสงค์นี้ บล็อกจึงมีช่องแก้ไข
เพิ่มทริกเกอร์:
สร้างทริกเกอร์ `ins_blog` ก่อนที่จะแทรกใน `blog` // แทรกเวลาก่อนที่จะบันทึกข้อมูลโดย "แทนที่" ข้อมูล
สำหรับแต่ละแถว BEGIN SET NEW.modification = NOW(); จบ
ตอนนี้เมื่อทำการเลือกในนาทีสุดท้ายเราจะได้เอกสารทั้งหมดที่เพิ่มเข้ามาในนาทีสุดท้าย
หากข้อมูลมีการเปลี่ยนแปลง เราจะอัปเดตดัชนีการค้นหาด้วย
โดยปกติแล้ว ในโปรเจ็กต์ทั่วไป ทุกอย่างที่สามารถถ่ายโอนไปยังฝั่งเซิร์ฟเวอร์ sql จะถูกถ่ายโอน เซิร์ฟเวอร์ sql เองก็ดำเนินการดังกล่าวได้เร็วกว่าและมีทรัพยากรน้อยกว่าที่สามารถทำได้ผ่านภาษาการเขียนโปรแกรมที่ใช้
ด้วยทรัพยากรที่น้อยลง
