table is mutating, trigger/function may not see it

1. Оршил
За энэ алдаа oracle бааз дээр, ямар нэг table-н row-level trigger дээрээс тухайн table рүүгээ хандаж байгаа үед гардаг алдаа юм. Нэлээн алдартай алдаа бөгөөд, mssql/mysql-н орчингоос oracle руу шилжилт хийж байгаа хүмүүст их тохиолдоно.

Хэсэг тодотгол хийе. Trigger дээрээс table рүү хандана гэдэг маань update/insert эсвэл зүгээр нэг select statement ч байж болох бөгөөд заавал trigger-н body дээрээ кодлогдсон байх албагүй. Өөр нэг функц/процедурт байж байгаад, тухайн trigger-г ажиллах үед дуудагдсан байж болно.

Мөн row-level trigger гэдгийг тайлбарлах гэж оролдоё. MSSql дээр ийм ойлголт байхгүй байгаа юм. Ямар ч update/insert/delete хийсэн, шинэ болон хуучин утгууд нь нэг set байдлаар ирдэг бөгөөд set байдлаар нь өөрчилж болдог, хэрэв нэг нэгээр нь өөрчлөх болбол cursor-р гүйлгэж хийнэ. (Би буруу санаагүй бол)

Харин oracle-д хоёр өөр төвшний trigger байдаг. Нэг нь statement-level, нөгөө нь row-level. Жишээ нь:
гэсэн update statement ажиллах үед тухайн table-н trigger-үүд

1. before update statement-level trigger
2. update хийгдэж буй бүх мөрийн хувьд
  2.1. before update row-level trigger
  2.2. after update row-level trigger
3. after update statement-level trigger

гэсэн дарааллаар ажиллана. Аа тэгээд 2.1, 2.2 дээр ажиллаж буй, row-level trigger-үүд дээр дээрх алдаа гарна гэсэн үг.

Row-level, statement level trigger-г яаж бичих вэ гэвэл, row-level trigger-г бичихдээ, "FOR EACH ROW" гэсэн түлхүүр үг ашиглана. Харин statement-level-н хувьд тийм зүйл бичихгүй.

Одоо тэгвэл, яаж row-level trigger-н үед тухайн table-с хамаарсан үйлдэл хийж update хийх вэ. Ерөнхий зарчим бол, дотроо array төрлийн хувьсагч, хэд хэдэн procedure агуулсан package бичих юм. Дараа нь 1. trigger дээр, өөрөөр хэлбэл, update хийгдэхээс өмнө тухайн array-аа цэвэрлэж эхлүүлэх procedure дуудна. 2.1 болон 2.2-н аль нэг дээр нь шинэ хуучин утгынхаа аль хэрэгтэйг нь авч array-даа хадгалах procedure дуудна. Харин 3. дээр дээрх цуглуулсан өгөгдлүүд дээр тухайн table-тай харьцаж ажиллах procedure дуудна.

2. Шийдэл
Бараа материалын категори гэдэг table байлаа гэж бодъё. Id, нэртэй. мөн бие биенээ агуулдаг учир parent_id-тай. Ингээд ашиглая гэж бодъё. Нэг категорийг сонгоод, тэрний доор агуулж буй, бүх категориудыг харуулахын тулд sortOrder гэдэг талбар ашиглая. (1., 1.1., 1.2., 1.2.1 гэх мэт) Ингэвэл table маань доорх байдалтай болно.
Ингээд тухайн table-н parent_id нь солигдох тоолонд sort_order нь автоматаар өөрчлөгддөг байг. Trigger дээр нь код бичиж болно. Бусад ийм бүтэцтэй table-үүдийг мөн ийм байдлаар шийдэхийн тулд, нэг generic package бичээд, тэрийгээ тал талаас нь trigger-ээр дуудъя. Даанч, package маань тухайн table рүү select хийх учраас "table is mutating.." алдаа заана. Иймээс л нөгөө нэг шийдэл маань хэрэг болно.

Package Header маань :
Package Body маань :
Before-Statement trigger :
After-Row trigger :
After-Statement trigger :



Мөн энэ талаар эндээс анх олж мэдсэн юм.

Жич : Бусад төрлийн бааз дээр өлхөөн хийчих юмыг ийм сүр, бөөн лай болгож хийж байгаа болохоор анхандаа дургүй хүрдэг л юм билээ. "Энэ нөгөө алдартай oracle уу?" гэж асуумаар санагдана. Мөн бас нэг ингэж асуумаар санагддаг зүйл нь auto-increment мөн л sequence, trigger гээд явж өгнө. Бас update-select (update join) байдаггүй.