Преглед на задействанията Задейства DML задейства DDL задейства DML събития: Вмъкване, изтриване, актуализиране Задейства DDL събития: Създаване, отпадане, промяна на влизане Въведено в SQL Server 2005
DML - тригер Обект - таблица, VIEW Event - вмъкване, актуализиране, изтриване за таблица и за VIEW. Време за активиране - преди (вместо) или след изпълнението на оператора.
DML тригери Тригерът е блок, който се изпълнява автоматично всеки път, когато възникне определено събитие - за разлика от процедура, която трябва да бъде извикана изрично Събитие - INSERT, UPDATE и DELETE за таблица, изглед - тригер не може да бъде дефиниран за заявка
DML тригери Създава се тригер в една таблица на база данни. Може да има достъп до други таблици и обекти от други бази данни. Тригерите не могат да се създават върху временни таблици или системни таблици, а само върху дефинирани от потребителя таблици или изгледи. Таблицата, на която е дефиниран тригерът, се нарича тригерна таблица.
Кога са необходими тригери За оценка на състоянието на таблица преди и след промени в данните и предприемане на действия въз основа на тази разлика. Множество DML тригери от един и същи тип (INSERT, UPDATE или DELETE) в таблица ви позволяват да предприемете няколко различни действия в отговор на един израз за промяна на данните.
Когато са необходими тригери За каскадни промени в свързаните таблици на база данни (ако не могат да бъдат направени с помощта на каскадни ограничения за референтна цялост). За предотвратяване на случайни или неправилни операции INSERT, UPDATE и DELETE За налагане на ограничения за целостта, които не могат да бъдат дефинирани с помощта на ограничение CHECK. DML тригерите могат да се отнасят до колони в други таблици.
Още ... Регистриране и одит. Тригерите могат да се използват за проследяване на промени в таблици, които изискват подобрена поддръжка за сигурност. Данните за промените в таблицата могат да се съхраняват в други таблици и включват например потребителски идентификатор, време на операцията за актуализиране; самите актуализирани данни и др. Координиране и почистване на данните. Всеки прост SQL израз, който актуализира таблица, може да бъде свързан с тригери, които съответно актуализират други таблици. Операции, които не са свързани с модификация на базата данни. Тригерите правят повече от просто актуализиране на базата данни. Стандартът SQL ви позволява да дефинирате съхранени процедури (които могат да бъдат извикани от тригери), които изпращат имейли, отпечатват документи и т.н.
Когато не е необходимо да използвате тригери Не е необходимо да внедрявате тригери с възможностите, постигнати чрез използване на декларативни средства на СУБД (ограничения за целостта или външни ключове) Избягвайте сложни вериги за задействане
Съвети Не използвайте тригери, ако можете да приложите ограничение CHECK Не използвайте ограничение CHECK, ако можете да заобиколите ограничение UNIQUE.
Основни параметри на задействане Име на таблица (или изглед) Време на задействане: СЛЕД (ЗА) или ВМЕСТО Събитие: INSERT, UPDATE, DELETE (TRUNCATE TABLE не е изтриване!) Тяло на тригера! Последователността на задействане на същия тип тригери е произволна
Групиране на събития Например, можете да създадете тригер, който ще се задейства, когато се изпълни оператор UPDATE или INSERT и ние ще наричаме този тригер като тригер UPDATE / INSERT. Можете дори да създадете тригер, който се задейства, когато настъпи някое от трите събития за промяна на данни (задействане UPDATE / INSERT / DELETE).
Правила за задействане Тригерите се задействат след завършване на изявлението, което е причинило тяхното задействане. Например, тригер UPDATE няма да се задейства, докато не бъде издаден израз UPDATE. Ако някой израз се опита да изпълни операция, която нарушава някакво ограничение на таблицата или причинява някаква друга грешка, тогава свързаният тригер няма да бъде задействан.
Как работи тригерът Тригерът се третира като част от една транзакция заедно с оператора, който го извиква. Следователно, оператор за връщане може да бъде извикан от тригер и този оператор ще върне обратно както тригера, така и съответното събитие за промяна на данни. Ако възникне грешка по време на изпълнение на тригера, цялата транзакция автоматично се връща обратно. Тригерът се задейства само веднъж на оператор, дори ако този израз засяга множество редове данни.
Пример CREATE TRIGGER trg НА my_table ЗА INSERT, UPDATE, DELETE AS изберете "това е тригер"
Когато тригерът бъде извикан, SQL изразите, посочени след ключовата дума AS, ще бъдат изпълнени. Тук можете да поставите множество изрази, включително програмни конструкции като IF и WHILE.
Избор на тип тригер ВМЕСТО тригерите се използват за: - Селективно инхибиране на изпълнението на команда, за която е дефиниран тригер (проверка на предварително условие); - Преброяване на стойностите на колоните до завършване на команда INSERT или UPDATE. Тригерите AFTER се използват за: - Записване на извършени операции; - Проверка на пост-условията на изпълнение на командата.
Завъртане и влагане на SQL Server позволява вложени тригери, до 32 нива на влагане. Ако някое от вложените тригери ROLLBACK, не се задействат последващи тригери. Тригерите се анулират, ако възникне безкраен цикъл.
INSTEAD OF тригер Тригерът INSTEAD OF се изпълнява вместо да се изпълнява SQL изразът. Това отменя действието на задействащия израз. Можете да дефинирате едно задействане INSTEAD OF за инструкция INSERT, UPDATE или DELETE. Тригер INSTEAD OF може да бъде зададен на таблица и/или изглед. Можете да използвате каскади на тригери INSTEAD OF, като дефинирате изгледи отгоре на изгледите, където всеки изглед има отделен тригер INSTEAD OF. INSTEAD OF тригери не са разрешени в модифицирани изгледи, които съдържат опцията WITH CHECK.
Тригер AFTER Тригерите AFTER могат да бъдат дефинирани само в таблици. Тригерът AFTER се задейства, след като всички операции, посочени в задействащия израз или SQL изразите, са завършени успешно. Това включва цялата каскада от действия за връзка и всички проверки на ограничения.
AFTER тригер Ако имате множество задействания AFTER, дефинирани от таблица за конкретен израз или набор от изрази, можете да посочите кой тригер да се задейства първо и кой тригер да се задейства последен. Ако имате дефинирани повече от две задействания, можете да посочите реда, в който да се задействат само първият и последният задействане. Всички други тригери се активират на случаен принцип.
СЛЕД поръчка на задействане sp_settriggerorder @triggername = "Друг. Тригер", @order = "първи" sp_settriggerorder @triggername = "Моят. Тригер", @order = "последен" sp_settriggerorder @triggername = "Моето. Друго. Тригер", @order = "none" sp_settriggerorder @triggername = "Още. Друго. Тригер", @order = "няма"
Използване вмъкнато, изтрито Специални таблици: вмъкнати - вмъкнати стойности (за INSERT, UPDATE) изтрити - изтрити стойности (за UPDATE, DELETE)
Използване на изтритите и вмъкнатите таблици Когато създадете тригер, имате достъп до две временни таблици, наречени изтрити и вмъкнати. Те се съхраняват в паметта, а не на диска. Тези две таблици имат същата структура като таблицата (същите колони и типове данни), от която е дефиниран този тригер.
Използване на изтритите и вмъкнатите таблици Изтритата таблица съдържа копия на редове, които са били засегнати от оператор DELETE или UPDATE. Редовете, изтрити от таблицата на този тригер, се преместват в изтритата таблица. След това данните в изтритата таблица могат да бъдат достъпни от този тригер. Вмъкнатата таблица съдържа копия на редовете, добавени към таблицата на този тригер от оператор INSERT или UPDATE. Тези редове се добавят едновременно към таблицата за задействане и към вмъкнатата таблица.
Използване на изтритите и вмъкнатите таблици Тъй като изразът UPDATE се третира като DELETE, последван от INSERT, операторът UPDATE копира стари стойности на редове в изтритата таблица и стойности на нови редове в таблицата за задействане и вмъкнатата таблица. Тригер INSERT => изтрито е празно DELETE тригер => вмъкнат е празен, но няма да се генерира съобщение за грешка!
Създайте тригер CREATE TRIGGER [име_на_схема. ] trigger_name ON (таблица | изглед) (ЗА | СЛЕД | ВМЕСТО) ([INSERT] [,] [UPDATE] [,] [DELETE]) AS (sql_statement)
CREATE TRIGGER plus_1 НА таблица 1 вместо вмъкване AS вмъкване на таблица 1 (id, col 1) изберете id + 1, col 1 от вмъкнат;
Обработка на изключения Командата ROLLBACK инструктира сървъра да спре да обработва актуализацията и да отхвърли транзакцията. Има и команда RAISEERROR, с която можете да изпратите съобщение за грешка до потребителя. ОПИТАЙ ДА ХВАНЕШ
Съобщение за грешка при обработка на изключения RAISERROR ("Грешка е възникнала поради грешни данни.", - Текст на съобщението. 16, - Тежест. 1 - Състояние.); Тежестта е число между 0 и 25. Дефинираното от потребителя ниво на сериозност на грешката. От 0 до 18 могат да бъдат зададени от всеки потребител. 19 до 25 могат да бъдат посочени само от системния администратор. 20 до 25 се считат за фатални - връзката с клиента се прекратява и се записва съобщение за грешка в регистрационните файлове за приложения и грешки. Състояние Цяло число от 0 до 255. Отрицателните стойности или стойности по-големи от 255 водят до грешка. Ако една и съща персонализирана грешка се появи на няколко местоположения, можете да използвате уникалния номер на състоянието за всяко местоположение, за да определите къде е възникнала грешката в кода.
Функции за докладване на грешки Функцията ERROR_LINE () връща номера на реда, където е възникнала грешката. Функцията ERROR_MESSAGE () връща текста на съобщението, което ще бъде върнато на приложението. Текстът съдържа стойностите на заместени параметри като дължина, имена на обекти или време. ERROR_NUMBER () връща номера на грешката. ERROR_PROCEDURE () връща името на съхранената процедура или тригера, където е възникнала грешката. Тази функция връща NULL, ако дадената грешка не е била извършена в съхранена процедура или тригер. ERROR_SEVERITY () връща сериозността на грешката. ERROR_STATE () връща състоянието.
Пример за CREATE TRIGGER Нисък тригер. Кредит при покупка. Покупка. Поръчка. Заглавка СЛЕД ВМЪКВАНЕ КАТО НАЧАЛО ДЕКЛАРИРАНЕ @creditrating tinyint, @vendorid int; ИЗБЕРЕТЕ @кредитиране = v. Кредит. Рейтинг, @vendorid = стр. Доставчик. ID ОТ покупка. Покупка. Поръчка. Заглавие p JOIN вмъкнато i ON p. Покупка. Поръчка. ID = i. Покупка. Поръчка. ID ПРИСЪЕДИНЕТЕ се Покупка. Доставчик v ON v. Доставчик. ID = i. Доставчик. ДОКУМЕНТ ЗА САМОЛИЧНОСТ; АКО @creditrating = 5 RAISERROR (Кредитният рейтинг на този доставчик е твърде нисък, за да приеме нови поръчки за покупка, 16, 1); КРАЙ
Управление на тригери Деактивиране / активиране на задействане: - ИЗКЛЮЧВАНЕ / АКТИВИРАНЕ НА TRIGGER trigger_name ON object_name Деактивиране / активиране на всички тригери в таблицата: - DISABLE / ENABLE TRIGGER ALL ON object_name Промяна на задействане: - ALTER TRIGGER trigger_name… Премахване на тригер: - DROP trigger TRIG_name
Активиране/деактивиране на тригера ИЗКЛЮЧВАНЕ НА TRIGGER (име на тригера [,... N] | ВСИЧКИ) ON (име_обект); АКТИВИРАНЕ НА TRIGGER (име на задействане [,... N] | ВСИЧКИ) ВКЛ. (име_на_обект)
Използване на тригери Защита - Отказ на достъп въз основа на стойности на данните Счетоводство - Регистриране на промени Целитет на данните - Сложни правила за интегритет - Комплексна референтна цялост Извлечени данни - автоматично изчисляване на стойности
Типове тригери Функция Тригер AFTER ВМЕСТО Обекти Таблици и изгледи Брой тригери на таблица / изглед Множество за събитие Едно задействане на събитие Няма ограничения ВМЕСТО АКТУАЛИЗИРАНЕ и ИЗТРИВАНЕ не могат да бъдат дефинирани за таблици, подложени на каскадни RI ограничения. Каскадни връзки След следните операции: Ограничения за обработка. Извършване на декларативни референтни действия. Създайте вмъкнатите и изтрити таблици. Действието, което задейства спусъка. Преди следващата стъпка: Ограничения за обработка. Вместо следната операция: Действието, което задейства спусъка. След следните операции: Създайте вмъкнатите и изтрити таблици.
DDL - тригери DDL тригерите могат да се използват за административни задачи като одит и регулиране на операции с база данни. Тези тригери се прилагат за всички команди от един и същи тип в цялата база данни или целия сървър.
DDL - Задейства DDL тригери, като обикновените тригери, задействат съхранени процедури в отговор на събитие. Задейства се в отговор на различни събития на езика за дефиниране на данни (DDL). Тези събития най-вече съответстват на Transact-SQL изрази, които започват с ключовите думи CREATE, ALTER или DROP.
Задачи за DDL тригери Предотвратяване на определени промени в схемата на базата данни. Изпълнете някои действия върху базата данни в отговор на промени в схемата на базата данни. Записвайте промени или събития в схемата на базата данни. DDL задейства задействане само след като съответните DDL оператори са били изпълнени. DDL тригерите не могат да се използват като ВМЕСТО тригери.
CREATE TRIGGER trigger_name ON (БАЗА ДАННИ | ВСИЧКИ СЪРВЪР) (ЗА | СЛЕД) (тип_събитие | група_събития) КАТО (sql_statement [;] [,... N] [;])
Създаване / изтриване на DDL tr CREATE TRIGGER ddl_trig_database НА ВСИЧКИ СЪРВЪР ЗА CREATE_DATABASE КАТО ПЕЧАТ "База данни е създадена." DROP TRIGGER ddl_trig_database НА ВСИЧКИ СЪРВЪР;
DDL - тригер CREATE TRIGGER security ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT "Трябва да деактивирате безопасността на Trigger" за пускане или промяна на таблици! ОТКЛЮЧВАНЕ;
Могат да бъдат създадени множество DDL тригери за един израз на Transact-SQL. DDL тригер и оператор, който го задейства, се изпълняват в една транзакция. Не е възможно да се отменят събитията ALTER DATABASE, които се появяват в рамките на DDL тригер. DDL тригерите се изпълняват само след завършване на оператора Transact-SQL. DDL тригерите не могат да се използват като ВМЕСТО тригери. DDL тригерите не създават вмъкнатите и изтрити таблици.
Влизане - задействане Тригерите за влизане изпълняват съхранени процедури в отговор на събитие LOGON. Това събитие се издига, когато се установи потребителска сесия с екземпляр на SQL Server. При влизане се задейства, след като фазата на удостоверяване при влизане завърши, но преди действително установената потребителска сесия.
Влизане - тригер CREATE TRIGGER trigger_name НА ВСИЧКИ СЪРВЪР (ЗА | СЛЕД) LOGON AS (sql_statement)
Адаптирано от статия на Робърт Марда на sqlservercentral.com: Одит чрез тригери
В тази статия Робърт предоставя примери за код за няколко тригера, инсталирани на таблици, за одит на действията на потребителите с MS SQL Server 7.0 / 2000 записи.
За обяснение как работят тригерите като цяло и как работят в SQL Server 7.0 и SQL Server 2000, можете да се обърнете към следните статии от Брайън Кели:
Първата статия обяснява предназначението на специалните таблици за вмъкване и изтриване (вмъкнати и изтрити таблици).
Примерите по-долу ще работят на SQL Server 2000, но те са тествани само на SQL Server 7.0.
Първо, трябва да създадем таблиците, необходими за по-нататъшна работа. Изпълнете скрипта по-долу в Query Analyzer:
СЪЗДАВАНЕ НА ТАБЛИЦА (
ИДЕНТИЧНОСТ (1, 1) НЕ НУЛА,
НУЛА,
(35) NULL
) НА
ОТИВАМ
СЪЗДАВАНЕ НА ТАБЛИЦА (
НЕ NULL,
(25) NULL,
(25) NULL,
(75) NULL,
(50) NULL,
НУЛА,
(35) NULL,
) НА
ОТИВАМ
Тригер, който следи операциите за изтриване
Ако искате да извършите изтривания от таблица, можете да използвате примерен тригер, който ще вмъкне ред в ComponentsDeleted всеки път, когато ред се изтрие от таблицата.
Компоненти: CREATE TRIGGER, изтрит от ON dbo.Components
ЗА ИЗТРИВАНЕ
КАТО
INSERT INTO ComponentsDeleted (Iden, ComponentName, SerialNumber,
коментари,
Потребителско име, DeletedDate, DeletedBy)
ИЗБЕРЕТЕ Iden, ComponentName, SerialNumber, Comments, UserName, getdate (),
SYSTEM_USER
FROM изтрит
Премахнете един или два реда от таблицата с компоненти. Сега погледнете таблицата ComponentsDeleted и ще видите изтритите от вас редове с датата и часа, когато са били изтрити.
Инсталирането на проста система за одит, която използва тригерите, примери за които са представени в тази статия, може да бъде полезно, когато трябва да знаете кой и кога е извършил действията, наблюдавани от тригери във вашата база данни.
СпусъкТова е специален тип съхранени процедури, които се стартират автоматично при извършване на определени действия с данните от таблицата. Всеки тригер е свързан с определена таблица. Всички модификации на данните, направени от тригера, се считат за една транзакция, която се отменя при откриване на грешка или нарушение на целостта на данните, като по този начин извършването на промени ще бъде забранено.
Има три типа тригери, в зависимост от командите, на които отговарят:
1) Тригери за вмъкване. Задейства се, когато е направен опит за вмъкване на данни с помощта на командата Insert.
2) Задейства за актуализиране. Задейства се при опит за промяна на данни с помощта на командата Update.
3) Тригери за изтриване. Задейства се при опит за изтриване на данни с помощта на командата Delete.
Разрешено е създаването на няколко тригера от един и същи тип за една таблица. Тригерите могат да се използват, например, за създаване на сложни настройки по подразбиране, за осигуряване на нестандартна референтна цялост, която не може да се поддържа с конвенционален Transact SQL.
Тригерите често се използват за извършване на каскадни промени в множество свързани таблици. Обхватът на тригерите не се ограничава до строго очертани рамки. При създаването им е необходимо да се ръководите от изискванията за изпълнение и удобство на извършваните действия. Използването на тригери трябва да се избягва, ако същите действия могат да се извършват с помощта на съхранена процедура или обикновени команди на Transact SQL. Използването на тригери също е нежелателно, тъй като те задържат ключалката, докато спусъкът приключи, предотвратявайки достъпа на други потребители до ресурса. Следната команда Transact SQL се използва за създаване на тригер:
CREATE TRIGGER име на тригера
sql_statement [... n]
Вторият вариант на тази команда:
CREATE TRIGGER име на тригера
(ЗА ([[,])
(АКО АКТУАЛИЗИРАНЕ (колона)
АКТУАЛИЗИРАНЕ (колона)]
sql_statement [... n]
Нека разгледаме действието на първия вариант на командата Create trigger.
Trigger_name – Указва името на тригера, по който ще бъде разпознат от съхранените процедури и Transact SQL команди. Името на тригера трябва да е уникално в базата данни.
TABLE - името на таблицата на базата данни, към която ще бъде свързан тригерът.
С КРИФИРАНЕ - Когато тази опция е посочена, сървърът криптира кода за задействане.
[,] [,] - тази конструкция определя на кои автомати ще реагира тригерът. При създаване на тригер трябва да се посочи поне една от тези ключови думи, разрешено е да се създаде тригер, който отговаря на две или три команди.
С ПРИЛОЖЕНИЕ – Тази ключова дума е необходима за обратна съвместимост със SQL Server.
НЕ ЗА РЕПЛИКАЦИЯ - забранява задействането при актуализиране на таблици чрез репликация.
sql_statement - Дефинира набора от команди, които ще бъдат изпълнени, когато тригерът се задейства.
Помислете за използването на втория метод:
FOR ([[,] - този израз определя коя команда ще стартира тригера.
IF UPDATE (колона) - използването на този параметър ви позволява да изпълните тригер, когато конкретна колона от таблица е променена.
И / ИЛИ АКТУАЛИЗИРАНЕ (колона) - приложението е същото като предишния параметър, ако трябва да стартирате тригер, когато променяте множество колони. Аргументът колона указва името на колоната, при промяна на която тригерът ще се стартира. Ключовата дума AND инструктира задействането на тригера само ако и двете колони, посочени в тази и в предишната конструкция, са били променени. Когато се използва ключовата дума ИЛИ, тригерът ще се изпълни, когато се направят промени в някоя от колоните. Разрешени са множество конструкции И/ИЛИ АКТУАЛИЗИРАНЕ (колона).
За да промените тригера, използвайте командата ALTER TRIGGER:
ALTER TRIGGER име на тригера
(ЗА ([,] [,])
sql_statement [... n]
Преди да изпълните командата ALTER TRIGGER, уверете се, че тригерът съществува в таблицата на таблицата.
За да премахнете задействане, използвайте командата DROP TRIGGER:
DROP TRIGGER (спусък) [..n]
Единственият задействащ аргумент е името на тригера. Множество тригери могат да бъдат отхвърлени с една команда DROP TRIGGER.
Много е възможно да знаете какво е тригер на база данни, поне в общи линии. Има дори шанс да знаете, че MySQL поддържа тригери и е запознат с тях. Но има вероятност повечето от вас, дори въоръжени със знания, да нямат представа какви предимства крият MySQL тригерите. Трябва да имате този инструмент в арсенала си, тъй като тригерите могат напълно да променят начина, по който работите с данни.
Въведение : какво е спусък
„Тъй като приложенията стават все по-сложни, ние можем да абстрахираме приложния слой, за да ги управляваме и да увеличим използваемостта на процеса на разработка.“
За тези, които не знаят, тригерът е правило, което поставяте в таблица и когато изпълнявате DELETE, UPDATE или INSERT, той извършва допълнителни действия. Например, можем да запишем промяна в дневника. Но вместо да пишете две отделни заявки (едната за промяна на данните, другата за запис в регистрационния файл), можете да напишете тригер, който съдържа правилото: „Когато един ред се промени, създайте нов ред в друга таблица, за да отчетете какво има е направено. промени”. Този подход създава известна излишество в основната заявка, но сега няма два различни пакета, преминаващи към вашия сървър на база данни, за да направят две различни неща, което обикновено подобрява производителността.
Тригерите са въведени в MySQL от версия 5.0.2. Синтаксисът на тригера е малко чужд. MySQL използва стандарта ANSI SQL: 2003 за процедури и други функции. Ако работите с езици за програмиране, тогава няма да е трудно да го разберете. Спецификацията не е публично достъпна, така че ще се опитаме да използваме прости структури и да обясним какво се случва в тригера. Ще се използват същите структури като във всеки език за програмиране.
Както бе споменато по-горе, тригерите се изпълняват като процедури при събития UPDATE, DELETE и INSERT. Те могат да се изпълняват преди или след дефинирането на събитието. По този начин можете да дефинирате тригер, който да бъде изпълнен преди DELETE или след DELETE и т.н. Това означава, че можете да имате един тригер, който ще се изпълнява преди INSERT и напълно различен, който ще се изпълнява след INSERT, което е много мощен инструмент.
Първи стъпки: структура на таблицата, инструменти и бележки
В тази статия ще работим с измислена система за пазарска количка, всеки артикул от която ще има цена. Структурата на данните ще бъде възможно най-проста, за да демонстрира процедурите за работа с тригери. Имената на таблици и колони са проектирани така, че да бъдат по-лесни за разбиране, а не за реална работа. TIMESTAMPS се използва и за улесняване на учебния процес. Таблиците са с имена cart, cart_items, cart_log, items, items_cost.
Ще се използват и много прости заявки. Няма връзка между променливите и не се използва въвеждане на данни. Запитванията бяха подготвени да бъдат възможно най-прости и четими.
Particle Tree PHP Quick Profiler беше използван за определяне на времето за изпълнение. Chive беше използван за илюстриране на ефектите върху базата данни. Chive е само за MySQL 5+ и е много подобен на PHPMyAdmin. Той има по-изразителен интерфейс, но съдържа значително повече грешки в момента. Използването на Chive се ръководи от желанието да се представят по-изразителни екранни снимки на заявки.
Може също да се наложи да промените MySQL разделителя, когато създавате тригери. Оригиналният MySQL разделител е; , но тъй като ще използваме разделител за добавените заявки, може да се наложи изрично да посочим разделителя, за да създаваме заявки от командния ред. Когато използвате Chive, няма нужда да сменяте разделителя.
За да промените разделителя, трябва да изпълните командата преди командата за задействане:
DELIMITER $$
И след командата за задействане трябва да въведете:
DELIMITER;
Прост тригер: целостта на данните
Ако искате да извършите дори незначително нормализиране на структурата на базата данни, може да се окаже, че ще трябва да изтриете основния източник на данни, който има фрагменти, участващи в общия поток от данни. Например, може да имате cart_id, който препраща към две или три таблици без външни ключове, особено когато използвате механизма MyISAM, който не ги поддържа.
В такъв случай може да сте извършили следните операции преди:
$ sql = "ИЗТРИВАНЕ ОТ no_trigger_cart_items КЪДЕ cart_id = 1";
$ rs = $ това-> db-> заявка ($ sql);
$ sql = "ИЗТРИВАНЕ ОТ no_trigger_carts КЪДЕ cart_id = 1";
$ rs = $ това-> db-> заявка ($ sql);
Сега, в зависимост от това колко сте организирани, може да имате един API или метод, който изпразва вашите кошчета. Ако това е вашият случай, тогава ще имате изолирана функция, която изпълнява две заявки. Ако самоорганизацията не е вашата силна страна, тогава винаги ще трябва да помните да изпразвате артикулите от кошницата, когато изтривате конкретна кошница. Не е трудно, но ако забравите, ще загубите целостта на данните.
Да се върнем към тригерите. Нека създадем прост тригер, който, когато количката бъде премахната, ще изтрие всички артикули от количката, които имат същия cart_id:
CREATE TRIGGER `урок`. `before_delete_carts`
ПРЕДИ ИЗТРИВАНЕ НА `trigger_carts` ЗА ВСЕКИ РЕД
ЗАПОЧНЕТЕ
DELETE FROM trigger_cart_items WHERE OLD.cart_id = cart_id;
КРАЙ
Много прост синтаксис. Нека разгледаме по-отблизо спусъка.
Първият ред е „CREATE TRIGGER` tutorial`.`before_delete_carts`”. Това е командата на MySQL за създаване на тригер за база данни „урок“, наречен „before_delete_carts“. Ще използваме схемата за именуване за тригерите When_What_Table.
Вторият ред определя за MySQL дефиницията на тригера „ПРЕДИ ИЗТРИВАНЕ ON` trigger_carts` FOR EACH ROW “. Ние казваме на MySQL да направи нещо за всеки ред, преди да изтрие от дадена таблица. Какво да направите е обяснено допълнително между BEGIN и END. "ИЗТРИВАНЕ ОТ trigger_cart_items WHERE OLD.cart_id = cart_id;" За MySQL е посочено, че преди да премахнете от trigger_carts, трябва да вземете OLD.cart_id и също да премахнете от trigger_cart_items. Синтаксисът OLD дефинира променлива. Ще бъде обсъдено в следващия раздел, където се комбинират СТАРО и НОВО.
Предимството на използването на тригер е, че целостта на вашите данни се премества от логическия слой към слоя данни, където трябва да бъде. Има и известно увеличение на производителността на системата.
Две запитвания:
Една заявка за задействане:
Както можете да видите, може да се очаква малко увеличение на производителността. Примерната база данни използва същия сървър като клиента. Ако обаче сървърът на базата данни се намира на различно място, тогава трябва да очаквате по-значителна разлика, тъй като времето за прехвърляне на данни между сървърите ще се добави към времето за изпълнение на заявките. Също така трябва да се отбележи, че първия път спусъкът може да се изпълни значително по-бавно от следващите пъти.
Преместването на логиката на данните към слоя данни е подобно на преместването на стила от слоя за маркиране към слоя за презентация, който е известен в целия свят като CSS.
Прекрасен прост тригер: регистриране и одит
Следващият пример, който ще разгледаме, е свързан с регистрирането на събития. Например, искаме да наблюдаваме всеки артикул, който е поставен в количката. Може да искаме да проследим оценката на покупката на артикули. Може би просто искаме да имаме копие на всеки артикул в количката, не непременно за продажба, а за анализиране на поведението на клиентите. Каквато и да е причината, нека да разгледаме тригера INSERT, който отваря възможности за регистриране или одит на нашите данни.
Преди да използваме спусъка, вероятно направихме нещо подобно:
Вече можем да създадем много прост тригер за процеса на регистриране:
СЪЗДАВАЙТЕ ЗАПУСКВАНЕ `след_поставяне_кошница_артикули`
СЛЕД ВМЪКВАНЕ НА `trigger_cart_items` ЗА ВСЕКИ РЕД
ЗАПОЧНЕТЕ
INSERT INTO trigger_cart_log (cart_id, item_id)
СТОЙНОСТИ (NEW.cart_id, NEW.item_id);
КРАЙ
Първият ред е „CREATE TRIGGER` after_insert_cart_items`“. За MySQL командата е настроена да създаде тригер с име „after_insert_cart_items“. Името може да бъде „Foo“ или „BullWinkle“ или нещо друго, но е по-добре да използвате описаната по-горе схема за именуване на тригери. Това е последвано от „СЛЕД ВМЪКВАНЕ НА `trigger_cart_items` ЗА ВСЕКИ РЕД “. Отново казваме, че след като нещо се вмъкне в trigger_cart_items, за всеки ред трябва да се извършат операции между BEGIN и END.
Ред „INSERT INTO trigger_cart_log (cart_id, item_id) VALUES (NEW.cart_id, NEW.item_id);“ е стандартна заявка, използваща две променливи. Това използва НОВИТЕ стойности, които се вмъкват в таблицата cart_items.
Отново нашата заявка е по-бърза:
За да проверите дали тригерът работи, нека разгледаме стойностите в таблицата:
По-сложен тригер: бизнес логика
Оттук нататък ще спрем да разглеждаме стария начин за използване на множество заявки и да ги сравняваме с техниката на използване на тригери. Нека да разгледаме няколко по-усъвършенствани примера за използване на тригери.
Бизнес логиката е мястото, където се пораждат грешки. Въпреки предпазливостта и вниманието към организацията на процеса, винаги нещо се обърка. Спусъкът за UPDATE може донякъде да смекчи тази ситуация. Имаме възможността в тригера да изчислим СТАРАТА стойност и да зададем НОВАТА стойност въз основа на резултата. Например, ние искаме винаги да задаваме цената на продукт с 30% премия към себестойността. Това води до факта, че когато променим (UPDATE) цената, трябва да променим (UPDATE) цената. Нека използваме спусък.
СЪЗДАЙТЕ ЗАПУСКВАНЕ `after_update_cost`
СЛЕД АКТУАЛИЗИРАНЕ НА `trigger_items_cost` ЗА ВСЕКИ РЕД
ЗАПОЧНЕТЕ
АКТУАЛИЗИРАНЕ на trigger_items
ЦЕНА НА КОМПЛЕКТ = (НОВ. цена * 1,3)
WHERE item_id = NEW.item_id;
КРАЙ
Променяме продуктовата таблица с цени на база НОВО.разходи * 1.3. Ако въведете стойност от $50, тогава цената трябва да бъде $65.
Този спусък работи чудесно.
Нека да разгледаме по-сложен пример. Вече имаме правило, което променя цената на артикул въз основа на цената. Сега искаме да зададем някои диференцирани цени. Ако цената е по-малка от $50, тогава действителната стойност ще бъде $50. Ако цената е повече от $50, но по-малко от $100, тогава действителната стойност ще бъде $100.
За да разрешим проблема, отново ще работим с UPDATE, но този път тригерът ще се изпълни преди да се изпълни заявката. Ще се използва и IF израз.
Ето текста на задействането:
СЪЗДАВАЙТЕ TRIGGER `before_update_cost`
ПРЕДИ АКТУАЛИЗИРАНЕ НА `trigger_items_cost` ЗА ВСЕКИ РЕД
ЗАПОЧНЕТЕ
АКО НОВ.разход< 50 THEN
КОМПЛЕКТ НОВ.разход = 50;
ELSEIF NEW.cost> 50 И NEW.cost< 100 THEN
КОМПЛЕКТ НОВО.разход = 100;
КРАЙ АКО;
КРАЙ
Това не е заявка, а припокриване на стойности. Ако цената е по-малка от 50 долара, задайте я на 50 долара. Ако цената е между $50 и $100, тогава я задайте на $100. Ако е по-високо, тогава просто го оставяме така, както е. Синтаксисът не се различава от другите езици от страна на сървъра. Трябва да затворите оператора IF с END IF.
Нека проверим работата на нашия спусък. Ако въведете стойност от $30, тогава цената трябва да бъде $50:
За стойност от $85:
За да проверите дали тригерът AFTER UPDATE все още работи, цената трябва да бъде $130:
Заключение
В тази статия само леко докоснахме айсберга на тригерите в MySQL. Те ви позволяват да прехвърляте правила за работа с данни от ниво на логиката на приложението към ниво данни.
Може би използването на тригери на едностраничен сайт е караница, която отнема време и усилия. Но сложните уеб приложения могат да бъдат напълно трансформирани чрез използването на тригери.
<Определение_триггера>:: = (CREATE | ALTER) TRIGGER trigger_name ON (име_на_таблица | име_изглед) (((ЗА | СЛЕД | ВМЕСТО) ([ИЗТРИВАНЕ] [,] [ВМЪКВАНЕ] [,] [АКТУАЛИЗИРАНЕ]) [С ПРИЛОЖЕНИЕ] [НЕ ЗА РЕПЛИКАЦИЯ] КАТО sql_оператор [... n]) | ((ЗА | СЛЕД | ВМЕСТО) ([,]) [С ПРИЛОЖЕНИЕ] [НЕ ЗА РЕПЛИКАЦИЯ] КАТО (АКО АКТУАЛИЗИРАНЕ (име на_колона) [(И | ИЛИ) АКТУАЛИЗИРАНЕ ( column_name)] [... n] | IF (COLUMNS_UPDATES () (обработващ_бит_оператор) change_mask_bit) (comparison_bit_operator) mask_bit [... n]) sql_operator [... n]))
Тригер може да бъде създаден само в текущата база данни, но е разрешен достъп до други бази данни вътре в тригера, включително тези, разположени на отдалечен сървър.
Помислете за присвояването на аргументи от CREATE | ПРОМЕНИ ПУСК.
Името на тригера трябва да е уникално в базата данни. По желание можете да посочите името на собственика.
С аргумента WITH ENCRYPTION сървърът криптира кода за задействане, така че никой, включително администраторът, да може да има достъп или да го прочете. Шифроването често се използва за скриване на алгоритми за обработка на данни за авторски права, които са интелектуална собственост на програмиста или търговски тайни.
Типове тригери
Има два параметъра в SQL Server, които определят поведението на тригерите:
- СЛЕД. Тригерът се изпълнява след успешно изпълнение на командите, които го извикаха. Ако командите не могат да завършат успешно по някаква причина, тригерът не се изпълнява. Трябва да се отбележи, че промените в данните в резултат на изпълнение на потребителска заявка и изпълнение на тригера се извършват в тялото на една транзакция: ако тригерът се върне назад, тогава потребителските промени също ще бъдат отхвърлени. Множество тригери AFTER могат да бъдат дефинирани за всяка операция (INSERT, UPDATE, DELETE). Ако дадена таблица има множество задействания AFTER, можете да използвате системната запаметена процедура sp_settriggerorder, за да посочите кое от тях ще бъде изпълнено първо и кое ще бъде последно. По подразбиране всички тригери в SQL Server са AFTER тригери.
- ВМЕСТО. Тригерът се извиква вместо да изпълнява команди. За разлика от тригера AFTER, INSTEAD OF —тригерът може да бъде дефиниран както за таблицата, така и за изгледа. За всяка операция 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 - когато се изпълни, изтритата таблица съдържа стари стойности на редове, които ще бъдат изтрити при успех
