DML команды
Данные в реляционных базах данных управляются с помощью DML (Data Manipulation Language) комманд. Эти команды это INSERT, UPDATE, DELETE и (в последних версиях SQL) MERGE. В этой главе обсуждается что происходит в памяти и на диске когда выполняются команды DML – момент когда новые данные записываются на в блоки сегмента таблицы или индекса и старые данные записываются в блоки сегментов отмены изменений. В основе этих команд лежит ACID тест, которые должна проходить любая реляционная БД. Управление транзакциями с помощью команд COMMIT и ROLLBACK которые ассоциируются с DML командами также будет рассмотрено в этой главе. А также в этой главе описывается параллельный доступ к данным и уровни блокировки.
Строго говоря существует пять DML команд
На практике профессионалы в области баз данных SELECT обычно не рассматривают как часть DML. Обычно SELECT рассматривается отдельно и это становится понятно когда вы увидите что следующие пять глав выделены для описания только команды SELECT. Команда MERGE тоже часто не рассматривается, не потому что это не чистая команды управления данными, а потому что результат выполнения этой команды можно достичь используя другие команды. MERGE можно рассматривать как ярлык для вызова команд INSERT и DELETE или UPDATE в зависмости от каких-либо условий. Команда часто рассматриваемая вместе с DML это команды TRUNCATE. На самом деле это DDL команда, но так как эффект для пользователей такой же как и от команды DELETE (несмотря на то что реализация абсолютно разная) то команда TRUNCATE удовлетворяет параметрам DML команд.
Команда INSERT
Oracle хранит данные в виде строк в таблицах. Таблица наполняется строками (так же как страна наполнена людьми) несколькими способами, но самый частый используемый метод это команда INSERT. SQL — это язык ориантированные на работу с наборами данных, и таким образом одна комманда может вилять на одну строку либо на набор строк. Отсюда следует что команда INSERT может добавить одну строку в одну таблицу или много строк в много таблиц. Базовая версия запроса добавляет всего одну строку, но сложные запросы могут добавлять несколько строк в несколько таблиц.
There are much faster techniques than INSERT for populating a table with large numbers of rows. These are the SQL*Loader utility, which can upload data from files produced by an external feeder system, and Data Pump, which can transfer data in bulk from one Oracle database to another, either via disk files or through a network link.
An INSERT command can insert one row, with column values specified in the command, or a set of rows created by a SELECT statement.
Простейшая форма команды INSERT добавляет одну строку в таблицу используя значения указанные в команде. Синтаксис такого запроса
INSERT INTO table [(column [,column…])] VALUES (value [,value…]);
insert into hr.regions values (10,’Great Britain’);
insert into hr.regions (region_name, region_id) values (‘Australasia’,11);
insert into hr.regions (region_id) values (12);
insert into hr.regions values (13,null);
Первая из команд указывает значения для обоих столбцов таблицы REGIONS. Если у таблицы есть третий столбец то запрос выполнится неуспешно так как команда использует позиционное обозначение (positional notation). В команде не указывается в какой столбец необходимо вставить конкретное значение, запрос рассматривает позицию значений, их порядок в команде. Когда БД получает запрос использущий позиционное обозначение она будет сопостовлять порядок значений со порядком определения столбцов при создании. Запрос выполнится неуспешно если порядок будет неверный: БД попробует вставить данные, но типы данных столбцов разные.
Второй запрос указывает и столбцы и значения которые использовать для столбцов. Обратите внимание что теперь порядок определения столбцов в таблице не важен – важен порядок столбцов и значений в запросе.
Третий пример указывает один столбец и одно значение. Для всех остальных столцов будет использоваться значение NULL. Запрос выполнится неуспешно если столбец REGION_NAME обязательный (not null). Четвертый пример приведёт к такому же результату как и третий, но так как не были указаны столбцы в запросе – необходимо указать значения (даже NULL) явно для всех столбцов.
It is often considered good practice not to rely on positional notation and instead always to list the columns. This is more work but makes the code self-documenting (always a good idea!) and also makes the code more resilient against table structure changes. For instance, if a column is added to a table, all the INSERT statements that rely on positional notation will fail until they are rewritten to include a NULL for the new column. INSERT code that names the columns will continue to run.
Для вставки нескольких строк одним запросом значения для строк должны возвращаться запросом. Синтаксис такой команды
INSERT INTO table [column [, column…] ] subquery;INSERT INTO table [column [, column…] ] subquery;
Обратите внимание что такой синтаксис не использует ключевое слово VALUES. Если список столбцов пропущен то подзапрос должен возвращаться значения для каждого столбца таблицы. Для копирование всех строк из одной таблицы в другую если у таблиц одинаковые столбцы то команда для такой операции будет вида
insert into regions_copy select * from regions;
Такой запрос предполагает что таблица regions_copy уже существует. Подзапрос SELECT считывает все строки из таблицы-источника (REGIONS) и команда INSERT записывает все строки в таблицу-цель (REGIONS_COPY)
Any SELECT statement, specified as a subquery, can be used as the source of rows passed to an INSERT. This enables insertion of many rows. Alternatively, using the VALUES clause will insert one row. The values can be literals or prompted for as substitution variables.
В завершение рассмотрения команды INSERT необходимо упомянуть что возможно вставить строки в несколько таблиц одним запросом. Такие запросы не входят в базовый курс но для полноты картины рассмотрим пример
into emp_no_name (department_id,job_id,salary,commission_pct,hire_date)
when department_id <> 80 then
into emp_non_sales (employee_id,department_id,salary,hire_date)
when department_id = 80 then
into emp_sales (employee_id,salary,commission_pct,hire_date)
from employees where hire_date > sysdate — 30;
Чтобы понять этот запрос, начинаем читать с конца. Подзапрос считывает строки из таблица EMPLOYEES где дата приёма на работу не раньше чем 30 дней назад (сотрудники нанятые за последние 30 дней). Затем возвращаемся наверх. Ключевое слово ALL обозначает что каждая строка из подзапроса рассматривается для доавления во все таблицы, не только в первую где выполняется условие. Первое условие 1=1, которое всегда возвращает значение TRUE, т.е. все строки запишутся в таблицу emp_no_name. Это копия таблицы EMPLOYOEES в которой нет столбцов для персональных данных. Затем рассматривается условие DEPARTMENT_ID<>80, т.е. создадутся строки в таблице EMP_NON_SALES для каждой строки из подзапроса где DEPARTAMENT_ID<>80; для этой таблицы нет столбца COMMISION_PCT. И третье условие создаст строки в таблице EMP_SALES для всех сотрудников у которых DEPARTAMENT_ID=80; в этой таблице не нужен столбец DEPARTMENT_ID так как у всех записей этой таблицы предполагается одно значение DEPARTAMENT_ID.
Это простой пример мультитабличной вставки, но главное понимать что с помощью такого запроса будет выполнен только один подзапрос и будет только один цикл прохода по строкам, но можно вставить данные во многие таблицы-цели. Таким образом можно существенно снижать нагрузку с БД.
Команда UPDATE
Команда UPDATE используется для изменения строк которые уже существуют – строки которые были созданы с помощью команды INSERT или возможно другими инструментами такими как Data Pump. Как и другие SQL команды, команда UPDATE может влиять на одну строку или набор строк. Размер набора данных обновляемым командой UPDATE определяется условием WHERE, точно таким же образом как и набор строк получаемый командой SELECT. Синтаксис идентичныйю Все обновляемые строки будут находиться в одной таблице; невозможно одной командой UPDATE обновлить данные в нескольких таблицах.
Когда обновляются данные команда UPDATE указывает какие столбцы набора строк обновлять. Необязательно обновлять все столбцы строки. Если обновляемые столбец уже хранит значение, оно будет заменено на новое указанное в команде UPDATE. Если в столбец не было значения – т.е. было значение NULL – то столбец будет обновлен на новое значение.
Обычное использование UPDATE это получение одной строки и обновление одного или нескольких столбцов в этой строке. Строка получается используя условие WHERE по первичному ключу, уникальному идентификатору которые гарантирует что только одна строка будет получена. Затем обновляются столбцы которые не являются столбцами первичного ключа. Обычно значение первичного ключа не изменяется. Жизненный цикл строки начинается когда она добавляется, затем может происходить несколько изменений до тех пор пока строка не удаляется и жизненный цикл не заканчивается. Во время жизни строки обычно первичный ключ не изменяется.
Для обновления набора строк используется менее строгое условие WHERE. Для обновления всех строк в таблице просто не указывается условие. Такое поведение команды немного смущает когда команда без условия WHERE выполняется по ошибке. Если вы выбираете строки не по строгому равенству значения первичного ключа то вы можете обновить несколько строк а не одну. Если же вы полностью убираете условие WHERE то будет обновлена вся таблица – возможно миллионы строк всего лишь выполнением одного запроса – когда вы хотели обновить всего одну строку.
One UPDATE statement can change rows in only one table, but it can change any number of rows in that table.
Команда UPDATE должна соблюдать все ограничения наложенные на таблицу, так же как и команда INSERT. Например невозможно обновить значение столбца с ограничением обязательности (not null) на значение NULL или обновить первичный ключ на неуникальное значение. Базовый синтаксис команды UPDATE
UPDATE table SET column=value [,column=value…] [WHERE condition];
Более сложная форма команды может использовать подразпросы для значений столбцов и для условия WHERE. На рисунке 8-1 показаны различные запросы выполненные в SQL *Plus.
Первый пример самый простой. Значение одного столбца одной строки устанавливается в значение-литерал. Так как в условии WHERE используется значение первичного ключа, то это гарантирует что обновится максимум одна строка. Если не будут найдены строка удовлетворяющая этому значению ключа то не обновится ни одна строка.
Второй пример показывает использование арифметической операции и существующего столбца для калькуляции нового значения и выборка строк для обновления происходит не по первичному ключу. Если выборка осуществляется ен по значению первичного ключа, или используются не предикаты равенства (такие как BETWEEN) то количество строк которые будут обновлены может быть больше чем один. Если условие WHERE полность пропущно – то обновление применится ко всем строкам таблицы.
Третий пример на рисунке 8-1 использует подзапрос для определения набора данных для обновления и запрос на ввода значения переменных используемых в подзапросе. В данном примере подзапрос (строки 3 и 4) вернёт всех сотрудников название департамента которых содержит подстроку IT и увеличит их зарплату на 10% (к сожалению такое редко случается в реальном мире).
Также возможно использовать подзапрос для определения значения используемого для обновления столбца, как показано в четвертом примере. В нашем примере один сотрудник (условие равенства первичного ключа в строке 5) переводится в департамент с идентификатором 80, и затем подзапрос (строки 3-4) устанавливает процент комиссии на минимальное значение для этого отдела.
Синтаксис для команды UPDATE использующей подзапросы
SET column=[subquery] [,column=subquery…]
WHERE column = (subquery) [AND column=subquery…] ;
Существует жёсткое ограничение на результат возвращаемый подзапросом для определения значения: подзапрос должен возвращать скалярное значение. Скалярное значение это одно значение (одна строка, один столбец) необходимого типа данных. Если подзапрос вернёт не скалярное значение запрос не выполнится. Рассмотрим два примера
set salary=(select salary from employees where employee_id=206);
set salary=(select salary from employees where last_name=’Abel’);
Первые пример использует предикат равенства по первичному ключу и запрос всегда выполнится успешно. Даже если запрос не вернёт ни одну строку (если нет сотрудника с номером 206) запрос вернёт скалярное значение: NULL. В этом случае у всех сотрудников зарплата станет NULL – что может быть не совсем верно с точки зрения качества данных, но с точки зрения SQL здесь нет ошибки. Второй запрос использует предикат равенства для поля LAST_NAME, что не гарантирует уникальность. Запрос выполнится успешно если существует только один сотрудник с таким именем, но если в таблице больше чем одна строка с таким значение запрос вернёт ошибку “ORA-01427: single-row subquery returns more than one row.” Для надёжного кода неважно состоянии данных, важно убедиться что подзапросы всегда возвращают скалярное значение.
A common fix for making sure that queries are scalar is to use MAX or MIN. This version of the statement will always succeed:
update employees set salary=(select max(salary) from employees where last_name=’Abel’);
However, just because it will work, doesn’t necessarily mean that it does what is wanted.
Подзапросы в условии WHERE тоже должны возвращать скалярное значение если используется предикат равенства или предикат отношения > или <. Если используется предикат вхождения (IN) то подзапрос может возвращать набор строк. К примерму
where department_id in (select department_id from departments where department_name like ‘%IT%’);
Результатом этого запроса будет обновление значения зарплаты всем сотрудникам название отдела которых содержит подстроку IT. Но несмотря на то что подзапрос может возвращать в таких случаях несколько строк – он всё равно должен возвращать один столбец.
The subqueries used to SET column values must be scalar subqueries. The subqueries used to select the rows must also be scalar, unless they use the IN predicate.
Команда DELETE
Ранее добавленные строки можно удалить из таблицы используя команду DELETE. Эта команда удалит одну или несколько строк из таблицы в зависимости от условия в секции WHERE. Если условие WHERE пропущено то все строки будут удалены из таблицы.
There are no “warning” prompts for any SQL commands. If you instruct the database to delete a million rows, it will do so. Immediately. There is none of that “Are you sure?” business that some environments offer.
Удаление столбцов происходит по принципу либо всё либо ничего. Нельзя указать столбец в команде DELETE. Когда строка добавляется в таблицу вы можете указать столбцы для заполнения. Когда строка обновляется вы можете выбрать столбцы для обновления. Но удаление происходит для всей строки – единственным выбором является какие строки удалять. Это делает команду DELETE легче чем другие команды с точки зрения синтаксиса. Синтаксис команды DELETE
DELETE FROM table [WHERE condition];
Это простейшая команда DML, особенно если условие WHERE будет пропущено. В этом случае все строки будут удалены. Единственным усложнением команды может быть добавление условия. К примеру условия равенства/подобия литералу
delete from employees where employee_id=206;
delete from employees where last_name like ‘S%’;
delete from employees where department_id=&Which_department;
delete from employees where department_id is null;
Первый запрос идентифицирует строку по первичному ключу. Одна строка будет удалена – или одна или ни одной, если заданное значение ключа не найдено в таблице. Второй запрос использует предикат подобия что может привести к удалению многих строк: будут удалены все сотрудники фамилия которых начинается с буквы S. Третий запрос запросит вводи значения для переменной в запросе и все сотрудники департамента будут удалены. Последний запрос удалит всех сотрудников у которых не назначен департамент (значение департамента NULL).
Условием может быть также подзапрос
delete from employees where department_id in
(select department_id from departments where location_id in
(select location_id from locations where country_id in
(select country_id from countries where region_id in
(select region_id from regions where region_name=’Europe’)
Этот пример испоьлзует подзапрос для выбора строк который использует географическое дерево (другие подзапросы) для удаления всех сотрудников департаменты которых базируются в Европе. Ограничение на количество строк возвращаемых подзапросом такое же как и для команды UPDATE: если условие базируется на предикате равенства, результат подзапроса должен быть скарялным значением, если используется IN то запрос может возвращать несколько строк.
Если команда DELETE не удаляет ни одной строки – это не рассматривается как ошибка. Команда вернёт сообщение “0 rows deleted’ вместо сообщения об ошибки посколько команды выполнена успешна – просто не были найдены строки для удаления.
Для удаления все строк из таблицы существует два варианта: использовать команду DELETE или команду TRUNCATE. DELETE менее кардинальная посколько удаление можно отменить когда очистку (TRUNCATE) нельзя. Также команда DELETE более управляемая так как можно использовать условие WHERE а команда TRUNCATE всегда удаляет все строки из таблицы. Но команда DELETE выполняется гораздо более медленно и загружает БД. Команда TRUNCATE выполняется практически мгновенно и без нагрузки на БД.
Команда TRUNCATE
Команда TRUNCATE это не команда DML – это команда DDL. Разница огромная. Когда DML команды работают с данными, они добавляют, изменяют или удаляют данные как часть транзакции. Рассмотрим транзакции чуть позже, пока же скажем что транзакции можно котролировать в том смысле, что изменения можно подветрждать и отменять. Это очень полезное свойство работы с данными, но его реализация заставляет БД делать много дополнительной работы которая не видна пользователю. DDL команды не управляются пользовательскими транзакциями (несмотря на то что в БД они выполняются как транзакции – но разработчик не может управлять ими), и у вас нет выбора подтвердить изменения или отменить их. Когда команда выполнена – изменения вступили в силу. Но по сравнению с DML командами – DDL команды очень быстрые.
Transactions, consisting of INSERT, UPDATE, and DELETE (or even MERGE) commands, can be made permanent (with a COMMIT) or reversed (with a ROLLBACK). A TRUNCATE command, like any other DDL command, is immediately permanent: it can never be reversed.
С точки зрения пользователя, TRUNCATE таблицы тоже самое что и DELETE всех строк. Но удаление может занять какое-то время (возможно несколько часов если достаточно много строк в таблице), а TRUNCATE отработает мгновенно вне зависимости от количества строк в таблице.
DDL commands, such as TRUNCATE, will fail if there is any DML command active on the table. A transaction will block the DDL command until the DML command is terminated with a COMMIT or a ROLLBACK.
TRUNCATE completely empties the table. There is no concept of row selection, as there is with a DELETE.
Одной из частей определения таблицы, которое хранится в словаре данных, является физическое местоположение. Когда таблица создаётся, выделяется место, фиксированного размера в файлах данных БД. Это место известное нам как экстент, выделяется и свободно для записи. Затем, когда строки добавляются в таблицу, экстент заполняется. Когда первый экстент заполнен, другие экстенты будут выделяться для таблицы автоматически. Таким образом таблица состоит из одного или нескольких экстентов в которых хранятся строки. Словарь данных отслеживает как выделенные экстенты так и как много выделенного для таблицы пространства использовано. Вводится понятие верхней границы (high water mark). Верхняя граница это последняя позиция в последнем экстенте которая когда либо использовалась для хранения данных. Все пространство до верхней границы когда либо использовалось для хранения данных, а всё пространство после никогда не использовалось для хранения данных. Обратите внимание что вполне возможно что будет много свободного места до верхней границы в текущий момент; это возможно из-за удаления строк командой DELETE. Добавление строк в таблицу поднимает верхную границу. Удаление строк оставляет верхнюю границу на той же позиции – но пространство используемое удаляемыми строками становится доступным для записи новых строк.
Команда TRUNCATE обнуляет верхнюю границу. В словаре данных позиция верхней границы смещается на начало первого экстента. Так как Oracle считает что строки после верхней границы не могут существовать то эффект от этого равнозначен удалению всех строк из таблицы. Таблица очищается и остается пустой пока последующие добавления строк не обновят значение верхней границы. Таким способом одна DDL команда которая фактически делает простое обновление в словаре данных может уничтожить миллионы строк в таблице.
Синтаксис команды TRUNCATE
TRUNCATE TABLE table;
На рисунке 8-2 показано как выбрать команду TRUNCATE в SQL Developer, но также эту команду можно выполнить и из SQL* Plus и из другого инструмента.
Команда MERGE
Часто возникает ситуация когда вам необходимо взять набор данных (источник) и интегрировать его в существующую таблицу (цель). Если строка источника уже существует в таблице-цели, вы можете хотеть обновить строку в таблице-цели, или удалить старую строку и вставить новую или вы хотите вообще не трогать такие строки. Если строка источника не существует в таблице-цели, вы хотите добавить такую строку. Команда MERGE позволяет сделать это. MERGE работает с наборами данных, для каждой строки источника пытается найти уже существующую строку в таблице-цели. Если совпадение не найдено – строка будет добавлена; если строка найдена то она может быть обновлена. Начиная с версии 10g найденная строка в таблице-цели может быть даже удалена после нахождения совпадения и обновления.
Команда MERGE не делает ничего такого что было бы невозможно сделать командами DELETE, INSERT и UPDATE – но только она может сделать это за один проход данных. Альтернативой команде MERGE будет три прохода данных, по одному для каждой команды.
Источником для команды MERGE может быть таблица или запрос. Условие для нахождения совпадения аналогично условию WHERE. Секция которая отвечает за обновление или добавление данных аналогично соответствующей команде INSERT или UPDATE. Получается что команда MERGE самая сложная из DML команд (сложно не согласиться) и самая мощная (что можно оспорить). Использование команды MERGE не входит в данный курс но для полноты картины рассмотрим простой пример
merge into employees e
using new_employees n on (e.employee_id = n.employee_id)
when matched then
update set e.salary=n.salary
when not matched then
Данный запрос использует таблицу NEW_EMPLOYEES для обновления или добавления данных в таблицу EMPLOYEES. Команда пройдёт по данным таблицы NEW_EMPLOYEES и для каждой строки этой таблицы попробует найти строку в таблице EMPLOYEES сооветствующую заданному условию. Если строка найдена значение поля SALARY будет обновлено на новое значение из таблицы NEW_EMPLOYEES. Если строка не найдена одна новая строка будет добавлена.
Неуспешное выполнение DML команд
Запрос может выполниться неуспешно по многим причинам, включая следующие
Ошибка в синтаксисе
Ссылка на несуществующий объект или столбец
Проблемы с доступным местом
На рисунке 8-3 отображены несколько попыток выполнения запросов в SQL *Plus. Пользователь подключается к аккаунту SUE используя пароль sue (хороший пример плохой безопасности БД) и выполняет запросы к таблице employees. Первый запрос выполняется с ошибкой из-за обычной опечатки корректно указанной SQL *Plus. Обратите внимание что SQL *Plus никогда не будет исправлять ошибки такого плана, даже если достоверно известно что именно вы хотели написать. Возможно другие инструменты могут автоматически изменять ошибки.
Второй запрос был выполнен неуспешно с ошибкой о том что объект не существует. Это произошло потому что таблица находится не в схеме SUE, а в схеме HR. Исправив это третий запрос был выполнен успешно – но только он. Значение переданное в условии WHERE это строка ’21-APR-00’, но поле HIREDATE определено как дата, а не строка. Для выполнения запроса БД попробовала преобразовать строку в дату. В последнем запросе такое преобразование не смогло отработать, так как строка-параметр была европейского формата данных, а база настроена на американский формат: преобразовать 21 в месяц не получилось. Запрос был бы выполнен успешно если бы строка была ‘04/21/2000’.
Даже если команда синтаксически верна и объекты используемые в запросе существуют, запрос все равно может быть выполнен неуспешно из-за нехватки прав. Если пользователь попробует выполнить запрос к объектам на которые у него нет соотвествующих прав, то БД вернёт ошибку аналогичной той, которая быда бы возвращена если бы объект не существовал. Для пользователя у которого нет прав к объекту – объект не существует.
Ошибки вызванные нехваткой прав являются случаем когда команды SELECT и DML могут возвращать разные результаты: для пользователя возможно установить права просмотра данных, но не изменения (добавления, удаления). Такое распределение достаточно часто распространено. А иногда бывают случаи когда у пользователя есть права добавления строк, которые он не может просматривать, и что хуже всего даже удалять строки которые он не может ни видеть ни изменять.
Нарушение ограничений также может привести к выполнению DML команды с ошибкой. Например команда INSERT может добавлять несколько строк в таблицу и для каждой строки БД будет проверять существует ли уже такое значение первичного ключа. Это проверяется для каждой строки. И может быть что первые несколько строк (или первые несколько миллионов строк) уже добавлены без проблем, а затем команда доходит до строки со значением-дубликатом. В этот момент будет возвращена ошибка и запрос будет выполнен неуспешно. Ошибка запустит отмену добавления всех уже добавленных строк. Такое поведение стандартно для всех команд SQL: либо запрос выполнен успешно целиком, либо не выполнен. Отмена выполненных изменение это rollback.
Если запрос выполнен неуспешно из-за проблем со свободным место – результат такой же. Часть запроса может быть выполнена успешно до того как БД стало не хватать места и эта часть будет автоматически отменена в момент возникновения ошибки. Отмена частично выполненного запроса нагружает БД. Отмена запроса принуждает БД сделать много работы и обычно занимает не меньше времени чем само выполнение запроса до ошибки (а иногда и дольше).
- Создание простой таблицы
- Создание и использование временных таблиц
- Представления
- Сиквенсы (Sequences)
- Ограничения
Команды определения структуры данных (Data Definition Language – ddl)
Команды манипулирования данными (Data Manipulation Language – dml)
DML-группа содержит команды, позволяющие вносить, изменять, удалять и извлекать данные из таблиц. Примеры DML-команд:
| Команда | Описание |
| SELECT | Извлечь данные из таблицы |
| INSERT | Добавить новую строку данных в таблицу |
| DELETE | Удалить строки из таблицы |
| UPDATE | Изменить информацию в строках таблицы |
Команды управления транзакциями (Transaction Control Language — tcl)
TCL-команды используются для управления изменениями данных, производимыми DML-командами. С их помощью несколько DML-команд могут быть объединены в единое логическое целое, называемое транзакцией. При этом все команды на изменение данных в рамках одной транзакции либо завершаются успешно, либо все могут быть отменены в случае возникновения каких-либо проблем с выполнением любой из них. Транзакции есть одно из средств поддержания целостности и непротиворечивости данных и являются одной из важнейших функций современных СУБД. TCL-команды:
| Команда | Описание |
| COMMIT | Завершить транзакцию и зафиксировать все изменения в БД |
| ROLLBACK | Отменить транзакцию и отменить все изменения в БД |
| SET TRANSACTION | Установить некоторые условия выполнения транзакции |
Команды управления доступом (Data Control Language – dcl)
DCL-команды управляют доступом пользователей к БД и отдельным объектам:
| Команда | Описание |
| GRANT | Разрешить доступ |
| REVOKE | Отменить доступ |
Работа с командами sql Извлечение данных, команда select
Быстрое извлечение данных, хранящихся в таблицах – одна из основных задач СУБД. Для выборки данных используется команда SELECT. В общем виде синтаксис этой команды выглядит следующим образом: SELECT [DISTINCT] FROM [JOIN ON ] [WHERE ] [GROPUP BY [HAVING ] ] [ORDER BY ] В квадратных скобках указаны необязательные элементы команды. Ключевые слова SELECT и FROM должны присутствовать всегда. Ниже рассмотрены возможные варианты написания этой команды подробнее. Список столбцов содержит перечень имен столбцов таблицы, которые должны быть включены в результат. Имена, если их несколько, отделяются друг от друга запятой: SELECT TabNum FROM Employees SELECT TabNum, Name FROM Employees Звездочка (*) на месте списка столбцов обозначает все столбцы таблицы: SELECT * FROM Employees При выборке столбцов с одинаковыми именами из нескольких таблиц перед именем каждого столбца надо указать через точку имя таблицы: SELECT Employees.Name, Departments.Name FROM …
CREATE TRIGGER (Transact-SQL)
Создает триггер языка обработки данных, DDL или входа. Триггер — это особая разновидность хранимой процедуры, которая автоматически выполняется при возникновении события на сервере базы данных. Триггеры DML выполняются, когда пользователь пытается изменить данные с помощью событий языка обработки данных (DML). Событиями DML являются процедуры INSERT, UPDATE или DELETE, применяемые к таблице или представлению. Эти триггеры срабатывают при запуске любого допустимого события независимо от наличия и числа затронутых строк таблицы. Дополнительные сведения см. в разделе DML Triggers.
Триггеры DDL активируются в ответ на разные события языка описания данных (DDL). Эти события прежде всего соответствуют инструкциям Transact-SQL CREATE, ALTER, DROP и некоторым системным хранимым процедурам, которые выполняют схожие с DDL операции.
Триггеры входа могут срабатывать в ответ на событие LOGON, которое возникает при создании пользовательского сеанса. Вы можете создавать триггеры непосредственно из инструкций Transact-SQL или методов сборок, созданных в среде CLR платформы Microsoft .NET Framework и переданных в экземпляр SQL Server. SQL Server позволяет создавать несколько триггеров для любой конкретной инструкции.
Вредоносный программный код внутри триггеров может быть запущен с расширенными правами доступа. Дополнительные сведения о том, как уменьшить эту угрозу, см. в статье Управление безопасностью триггеров.
В этой статье рассматривается интеграция среды CLR .NET Framework с SQL Server. Интеграция со средой CLR не применяется к базе данных SQL Azure.
Синтаксис SQL Server
-- SQL Server Syntax -- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON < table | view >[ WITH [ . n ] ] < FOR | AFTER | INSTEAD OF > < [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] >[ WITH APPEND ] [ NOT FOR REPLICATION ] AS < sql_statement [ ; ] [ . n ] | EXTERNAL NAME > ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] ::= assembly_name.class_name.method_name -- SQL Server Syntax -- Trigger on an INSERT, UPDATE, or DELETE statement to a -- table (DML Trigger on memory-optimized tables) CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON < table >[ WITH [ . n ] ] < FOR | AFTER > < [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] >AS < sql_statement [ ; ] [ . n ] > ::= [ NATIVE_COMPILATION ] [ SCHEMABINDING ] [ EXECUTE AS Clause ] -- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, -- REVOKE or UPDATE statement (DDL Trigger) CREATE [ OR ALTER ] TRIGGER trigger_name ON < ALL SERVER | DATABASE >[ WITH [ . n ] ] < FOR | AFTER > < event_type | event_group >[ . n ] AS < sql_statement [ ; ] [ . n ] | EXTERNAL NAME < method specifier >[ ; ] > ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] -- Trigger on a LOGON event (Logon Trigger) CREATE [ OR ALTER ] TRIGGER trigger_name ON ALL SERVER [ WITH [ . n ] ] < FOR| AFTER >LOGON AS < sql_statement [ ; ] [ . n ] | EXTERNAL NAME < method specifier >[ ; ] > ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] Синтаксис базы данных SQL Azure
-- Azure SQL Database Syntax -- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON < table | view >[ WITH [ . n ] ] < FOR | AFTER | INSTEAD OF > < [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] >AS < sql_statement [ ; ] [ . n ] [ ; ] >> ::= [ EXECUTE AS Clause ] -- Azure SQL Database Syntax -- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, -- REVOKE, or UPDATE STATISTICS statement (DDL Trigger) CREATE [ OR ALTER ] TRIGGER trigger_name ON < DATABASE >[ WITH [ . n ] ] < FOR | AFTER > < event_type | event_group >[ . n ] AS < sql_statement [ ; ] [ . n ] [ ; ] > ::= [ EXECUTE AS Clause ] Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
OR ALTER
Применимо к: База данных SQL Azure, SQL Server (начиная с SQL Server 2016 (13.x) с пакетом обновления 1 (SP1).
Условно изменяет триггер только в том случае, если он уже существует.
schema_name
Имя схемы, которой принадлежит триггер DML. Действие триггеров DML ограничивается схемой той таблицы или того представления, для которых они созданы. Аргумент schema_name не может указываться для триггеров DDL или триггеров входа.
trigger_name
Имя триггера. Аргумент trigger_name должен соответствовать правилам для идентификаторов с одним дополнительным ограничением: trigger_name не может начинаться с символов # или ##.
table | view
Таблица или представление, в котором выполняется триггер DML. Эту таблицу или представление иногда называют таблицей триггера или представлением триггера соответственно. Указание уточненного имени таблицы или представления не является обязательным. Ссылку на представление можно использовать только в триггере INSTEAD OF. Нельзя определить триггеры DML для локальной или глобальной временных таблиц.
DATABASE
Применяет область действия триггера DDL к текущей базе данных. Если этот аргумент определен, триггер срабатывает всякий раз при возникновении в базе данных события типа event_type или event_group.
ALL SERVER
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.
Применяет область действия триггера DDL или триггера входа к текущему серверу. Если этот аргумент определен, триггер срабатывает всякий раз при возникновении на текущем сервере события типа event_type или event_group.
WITH ENCRYPTION
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.
Маскирует текст инструкции CREATE TRIGGER. Использование WITH ENCRYPTION предотвращает публикацию триггера в рамках реплика sql Server. Параметр WITH ENCRYPTION нельзя указать для триггеров CLR.
EXECUTE AS
Указывает контекст безопасности, в котором выполняется триггер. Позволяет контролировать, какую учетную запись пользователя использует экземпляр SQL Server для проверки разрешений на любые объекты базы данных, на которые ссылается триггер.
Этот параметр является обязательным для триггеров в таблицах, оптимизированных для памяти.
Дополнительные сведения см. в разделе EXECUTE AS (Transact-SQL).
NATIVE_COMPILATION
Указывает, что триггер компилируется в собственном коде.
Этот параметр является обязательным для триггеров в таблицах, оптимизированных для памяти.
SCHEMABINDING
Гарантирует, что используемые триггером таблицы ну будут удалены или изменены.
Этот параметр является обязательным для триггеров в таблицах, оптимизированных для памяти, и не поддерживается для триггеров в обычных таблицах.
FOR | AFTER
Значение FOR или AFTER указывает, что триггер DML срабатывает только после успешного запуска всех операций в инструкции SQL, по которой срабатывает триггер. Кроме того, до запуска триггера должны успешно завершиться все каскадные действия и проверки ограничений, на которые есть ссылки.
Нельзя определить триггеры AFTER для представлений.
INSTEAD OF
Указывает, что триггер DML выполняется вместо инструкции SQL, по которой он срабатывает, то есть переопределяет действия запускающих инструкций. Аргумент INSTEAD OF нельзя использовать для триггеров DDL или триггеров входа.
Для каждой инструкции INSERT, UPDATE или DELETE в таблице или представлении можно определить не более одного триггера INSTEAD OF. Также вы можете определить представления представлений, указав для каждого их уровня собственный триггер INSTEAD OF.
Триггеры INSTEAD OF нельзя определять для обновляемых представлений, которые используют параметр WITH CHECK OPTION. Такое действие вызовет ошибку, если триггер INSTEAD OF добавляется к обновляемому представлению с параметром WITH CHECK OPTION. Чтобы удалить этот параметр, выполните инструкцию ALTER VIEW перед определением триггера INSTEAD OF.
< [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] >
Определяет инструкции изменения данных, при применении которых к таблице или представлению срабатывает триггер DML. Укажите хотя бы один вариант. В определении триггера разрешены любые сочетания вариантов в любом порядке.
Для триггеров INSTEAD OF нельзя использовать параметр DELETE в таблицах со ссылочной связью, которая определяет каскадное действие ON DELETE. Аналогично параметр UPDATE недопустим в таблицах, у которых есть ссылочная связь с каскадным действием ON UPDATE.
WITH APPEND
Применимо: SQL Server 2008 (10.0.x) до SQL Server 2008 R2 (10.50.x).
Указывает, что требуется добавить триггер существующего типа. Аргумент WITH APPEND нельзя использовать для триггеров INSTEAD OF и в тех случаях, когда явно указан триггер AFTER. Для сохранения обратной совместимости аргумент WITH APPEND следует использовать только при указании параметра FOR без INSTEAD OF или AFTER. Нельзя указать WITH APPEND, если используется EXTERNAL NAME (то есть триггер является триггером CLR).
event_type
Имя языкового события Transact-SQL, запуск которого вызывает срабатывание триггера DDL. Список событий, которые могут быть использованы в триггерах DDL, приведен в разделе DDL-события.
event_group
Имя предварительно определенной группы относящихся к языку событий Transact-SQL. Триггер DDL срабатывает после запуска любого языкового события Transact-SQL, которое относится к группе event_group. Список групп событий, которые могут быть использованы в триггерах DDL, приведен в разделе Группы DDL-событий.
После завершения инструкции CREATE TRIGGER параметр event_group работает в режиме макроса, добавляя охватываемые им типы события в представление каталога sys.trigger_events.
NOT FOR REPLICATION
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.
Указывает, что триггер не должен выполняться, когда агент репликации изменяет настроенную для триггера таблицу.
sql_statement
Условия и действия триггера. Условия триггера указывают дополнительные критерии, определяющие, какие события — DML, DDL или событие входа — вызывают выполнение триггера.
Действия триггера, указанные в инструкциях языка Transact-SQL, вступают в силу после попытки использования операции.
Триггеры могут содержать любое количество инструкций языка Transact-SQL любого типа, за некоторыми исключениями. Дополнительные сведения см. в подразделе «Примечания». Триггеры предназначены для проверки или изменения данных при выполнении инструкций модификации или определения данных. Не следует возвращать из них данные пользователю. Инструкции языка Transact-SQL в составе триггера часто содержат выражения языка управления потоком.
Триггеры DML используют логические (концептуальные) таблицы deleted и inserted. По своей структуре они подобны таблице, для которой определен триггер, то есть таблице, к которой применяется действие пользователя. В таблицах deleted и inserted содержатся старые или новые значения строк, которые могут быть изменены действиями пользователя. Например, для запроса всех значений таблицы deleted можно использовать инструкцию:
SELECT * FROM deleted; Триггеры DDL и триггеры входа собирают сведения о запускающих событиях с помощью функции EVENTDATA (Transact-SQL). Дополнительные сведения см. в разделе Использование функции EVENTDATA.
SQL Server позволяет обновлять столбцы текста, ntext или изображения с помощью триггера INSTEAD OF в таблицах или представлениях.
Типы данных ntext, text и image будут удалены в следующей версии Microsoft SQL Server. Следует избегать использования этих типов данных при новой разработке и запланировать изменение приложений, использующих их в настоящий момент. Вместо них следует использовать типы данных nvarchar(max), varchar(max)и varbinary(max) . Как триггеры AFTER, так и триггеры INSTEAD OF поддерживают данные типов varchar(MAX), nvarchar(MAX) и varbinary(MAX) в таблицах inserted и deleted.
Для триггеров в таблицах, оптимизированных для памяти, единственной инструкцией sql_statement, разрешенной на верхнем уровне, является блок ATOMIC. В блоке ATOMIC допускается только T-SQL, разрешенный в процедурах, компилируемых в собственном коде.
<>method_specifier применимо к SQL Server 2008 (10.0.x) и более поздним версиям.
Указывает метод сборки для связывания с CLR-триггером. Этот метод не должен принимать аргументы и возвращать значения void. class_name должен быть допустимым идентификатором SQL Server и должен существовать как класс в сборке с видимостью сборки. Если класс имеет имя, содержащее точки (.) для разделения частей пространства имен, имя класса должно быть заключено в квадратные скобки ([ ]) или двойные кавычки (» «). Класс не может быть вложенным.
По умолчанию возможность выполнения кода СРЕДЫ CLR в SQL Server отключена. Можно создавать, изменять и удалять объекты базы данных, ссылающиеся на модули управляемого кода, но эти ссылки не выполняются в экземпляре SQL Server, если параметр clr не включен с помощью sp_configure.
Примечания о триггерах DML
Триггеры DML часто используются для применения бизнес-правил и обеспечения целостности данных. SQL Server предоставляет декларативную целостность ссылок (DRI) с помощью инструкций ALTER TABLE и CREATE TABLE. Но декларативное ограничение ссылочной целостности не обеспечивает ссылочную целостность между базами данных. Ограничение ссылочной целостности подразумевает выполнение правил связи между первичными и внешними ключами таблиц. Для обеспечения ограничений ссылочной целостности используйте в инструкциях ALTER TABLE и CREATE TABLE ограничения PRIMARY KEY и FOREIGN KEY. Если ограничения распространяются на таблицу триггера, они проверяются после выполнения триггера INSTEAD OF, но до выполнения триггера AFTER. Если будет обнаружено нарушение ограничений, для триггера INSTEAD OF выполняется откат, а триггер AFTER не срабатывает.
Вы можете указать, какой триггер AFTER будет выполняться для таблицы первым, а какой последним, с помощью sp_settriggerorder. Для таблицы можно определить только один первый и один последний триггер для каждой из операций INSERT, UPDATE и DELETE. Если для таблицы определены другие триггеры AFTER, они выполняются в случайном порядке.
Если инструкция ALTER TRIGGER изменяет первый или последний триггер, для него удаляется метка первого или последнего триггера и порядок сортировки нужно установить заново с помощью sp_settriggerorder.
Триггер AFTER выполняется только после того, как вызывающая срабатывание триггера инструкция SQL успешно выполняется. Успешное выполнение также подразумевает завершение всех ссылочных каскадных действий и проверки ограничений, связанных с измененными или удаленными объектами. Триггер AFTER не вызывает рекурсивное срабатывание триггера INSTEAD OF для той же таблицы.
Если определенный для таблицы триггер INSTEAD OF выполняет в этой таблице какую-либо инструкцию, которая обычно приводит к срабатыванию триггера INSTEAD OF, этот триггер не вызывается рекурсивно. Вместо этого инструкция обрабатывается так, как если бы у таблицы отсутствовал триггер INSTEAD OF и начинается последовательность применения ограничений и выполнения триггеров AFTER. Для примера предположим, что для таблицы определен триггер INSTEAD OF INSERT. Этот триггер выполняет инструкцию INSERT в той же таблице, и в этом случае выполненная в триггере INSTEAD OF инструкция INSERT не приводит к новому срабатыванию триггера. Выполняемая триггером команда INSERT начинает процесс применения ограничений и срабатывания всех триггеров AFTER INSERT, определенных для этой таблицы.
Если определенный для представления триггер INSTEAD OF выполняет по отношению к этому представлению какую-либо инструкцию, которая обычно приводит к срабатыванию триггера INSTEAD OF, триггер рекурсивно не вызывается. Вместо этого инструкция выполняет изменение базовых таблиц, на которых основано представление. В данном случае определение представления должно удовлетворять всем ограничениям, установленным для обновляемых представлений. Определение обновляемых представлений см. в разделе Изменение данных через представление.
Для примера предположим, что для представления определен триггер INSTEAD OF UPDATE. Этот триггер выполняет инструкцию UPDATE в том же представлении, и в этом случае выполненная в триггере INSTEAD OF инструкция UPDATE не приводит к новому срабатыванию триггера. Выполняемая в триггере инструкция UPDATE обрабатывает представление так, как если бы у него не было триггера INSTEAD OF. Столбцы, измененные с помощью инструкции UPDATE, должны принадлежать одной базовой таблице. Каждая модификация базовой таблицы вызывает применение последовательности ограничений и взвод триггеров AFTER, определенных для данной таблицы.
Проверка действий инструкций UPDATE или INSERT на указанные столбцы
Триггер Transact-SQL можно настроить для выполнения некоторых действий при изменении определенных столбцов в инструкциях UPDATE или INSERT. Используйте для этих целей в теле триггера конструкции UPDATE() или COLUMNS_UPDATED. Конструкция UPDATE() проверяет действие инструкций UPDATE или INSERT на одном столбце. COLUMNS_UPDATED проверяет выполнение операций UPDATE или INSERT над множеством столбцов. Эта функция возвращает битовый шаблон с информацией о том, какие столбцы были вставлены или обновлены.
Ограничения триггеров
Инструкция CREATE TRIGGER должна быть первой инструкцией в пакете и может применяться только к одной таблице.
Триггер создается только в текущей базе данных, но может, тем не менее, содержать ссылки на объекты за пределами текущей базы данных.
Если для уточнения триггера указано имя схемы, имя таблицы необходимо уточнить таким же образом.
Одно и то же действие триггера может быть определено более чем для одного действия пользователя (например, INSERT и UPDATE) в одной и той же инструкции CREATE TRIGGER.
Триггеры INSTEAD OF DELETE и INSTEAD OF UPDATE нельзя определить для таблицы, у которой есть внешний ключ с каскадным действием для операции DELETE или UPDATE.
Внутри триггера может быть использована любая инструкция SET. Выбранный параметр SET остается в силе во время выполнения триггера, после чего настройки возвращаются в предыдущее состояние.
Во время срабатывания триггера результаты возвращаются вызывающему приложению так же, как и в случае с хранимыми процедурами. Чтобы при срабатывании триггера в приложение не возвращались результаты, не включайте в триггер инструкции SELECT, которые возвращают результаты или инструкции присвоения переменных. Если триггер содержит инструкции SELECT, которые возвращают результаты пользователю, либо инструкции присвоения значения переменным, для него требуется особый подход. Возвращаемые результаты нужно будет передать в каждое приложение, которому разрешено изменять таблицу триггера. Если в триггере происходит присвоение переменной, следует использовать инструкцию SET NOCOUNT в начале триггера, чтобы предотвратить возвращение каких-либо результирующих наборов.
Хотя инструкция TRUNCATE TABLE по сути аналогичная инструкции DELETE, она не активирует триггер, так как не заносит в журнал удаление отдельных строк. Но беспокоиться о случайном обходе триггера DELETE таким образом нужно только пользователям с разрешениями на выполнение инструкции TRUNCATE TABLE.
Инструкция WRITETEXT (с ведением журнала и без него) не запускает триггеры.
Следующие инструкции языка Transact-SQL не разрешены в триггерах DML:
- ALTER DATABASE
- СОЗДАТЬ БАЗУ ДАННЫХ
- DROP DATABASE
- RESTORE DATABASE
- RESTORE LOG
- RECONFIGURE
Кроме того, не допускается использование перечисленных ниже инструкций Transact-SQL в тексте триггера DML, если он применяется к таблице или представлению, которые являются целью действий триггера.
- CREATE INDEX (в т.ч CREATE SPATIAL INDEX и CREATE XML INDEX)
- ALTER INDEX
- DROP INDEX
- DROP TABLE
- DBCC DBREINDEX
- ALTER PARTITION FUNCTION
- ALTER TABLE при использовании для выполнения следующих действий:
- Добавление, изменение или удаление столбцов.
- Переключение секций.
- Добавление или удаление ограничений PRIMARY KEY и UNIQUE.
Так как SQL Server не поддерживает определяемые пользователем триггеры в системных таблицах, рекомендуется не создавать определяемые пользователем триггеры в системных таблицах.
Оптимизация триггеров DML
Триггеры работают в транзакциях (в том числе неявных) и блокируют ресурсы на весь период, в течение которого транзакция открыта. Такая блокировка действует, пока транзакция не будет зафиксирована (COMMIT) или отклонена (ROLLBACK). Чем дольше выполняется триггер, тем выше вероятность блокирования другого процесса. Старайтесь создавать такие триггеры, которые выполняются максимально быстро. Один из способов сократить время выполнения — освободить триггер, если инструкция DML изменяет 0 строк.
Чтобы освободить триггер для команды, которая не изменяет ни одной строки, используйте системную переменную ROWCOUNT_BIG.
В следующем фрагменте кода T-SQL триггер освобождается для команды, которая не изменяет ни одной строки. Этот код нужно добавить в начале каждого триггера DML:
IF (ROWCOUNT_BIG() = 0) RETURN;Примечания о триггерах DDL
Триггеры DDL, как и стандартные триггеры, запускают хранимые процедуры в ответ на какое-либо событие. В отличие от стандартных триггеров, они не срабатывают при выполнении инструкций UPDATE, INSERT или DELETE для таблицы или представления. Вместо этого они обычно срабатывают в ответ на инструкции языка определения данных (DDL). К ним относятся инструкции CREATE, ALTER, DROP, GRANT, DENY, REVOKE и UPDATE STATISTICS. Системные хранимые процедуры, выполняющие операции, подобные операциям DDL, также могут запускать триггеры DDL.
Протестируйте триггеры DDL, чтобы получить ответ на выполнение системных хранимых процедур. Например, инструкция CREATE TYPE и хранимые процедуры sp_addtype и sp_rename вызовут срабатывание триггера DDL, созданного для события CREATE_TYPE.
Дополнительные сведения о триггерах DDL см. в разделе Триггеры DDL.
Триггеры DDL не срабатывают в ответ на события, влияющие на локальные или глобальные временные таблицы и хранимые процедуры.
В отличие от триггеров DML, триггеры DDL не ограничены областью схемы. Это означает, что для запроса метаданных о триггерах DDL нельзя воспользоваться такими функциями, как OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY и OBJECTPROPERTYEX. Используйте вместо них представления каталога. Дополнительные сведения см. в статье Получение сведений о триггерах DDL.
Триггеры DDL сервера появляются в обозревателе объектов среды SQL Server Management Studio в папке Triggers . Эта папка находится под папкой Объекты сервера . Триггеры DDL, доступные в области базы данных, находятся в папке Триггеры базы данных. Эта папка находится в папке Программирование соответствующей базы данных.
Триггеры входа
Триггеры входа выполняют хранимые процедуры в ответ на событие LOGON. Это событие происходит при установке сеанса пользователя с экземпляром SQL Server. Триггеры входа срабатывают после проверки подлинности при входе, но перед тем, как устанавливается пользовательский сеанс. Таким образом, все сообщения, поступающие внутри триггера, которые обычно будут обращаться к пользователю, например сообщения об ошибках и сообщениях из инструкции PRINT, перенаправляются в журнал ошибок SQL Server. Дополнительные сведения см. в разделе Триггеры входа.
Если проверка подлинности завершается сбоем, триггеры входа не срабатывают.
Распределенные транзакции не поддерживаются в триггерах входа. Если триггер содержит распределенную транзакцию, при его срабатывании возвращается ошибка 3969.
Отключение триггера входа
Триггер входа может эффективно предотвратить успешные подключения к ядро СУБД для всех пользователей, включая членов предопределенных ролей сервера sysadmin. Если триггер входа запрещает подключения, члены предопределенной роли сервера sysadmin могут подключаться с помощью выделенного подключения администратора или запуска ядро СУБД в минимальном режиме конфигурации (-f). Дополнительные сведения см. в разделе Параметры запуска службы Database Engine.
Общие соглашения о триггерах
Возвращаемые результаты
Возможность возвращать результаты из триггеров будет исключена из следующей версии SQL Server. Триггеры, которые возвращают результирующие наборы, могут привести к непредвиденному поведению в приложениях, не предназначенных для работы с ними. Старайтесь не возвращать результирующие наборы из триггеров во всех новых проектах и постепенно исправляйте такое поведение в существующих приложениях. Чтобы триггеры не возвращали результирующие наборы, для параметра disallow results from triggers необходимо установить значение 1.
Триггеры входа всегда запрещают возврат результирующих наборов, и это нельзя изменить. Если триггер входа формирует результирующий набор, его не удастся запустить и любая попытка входа, при которой срабатывает такой триггер, будет запрещена.
Несколько триггеров
SQL Server позволяет создавать несколько триггеров для каждого события DML, DDL или LOGON. Например, если CREATE TRIGGER FOR UPDATE выполняется для таблицы, которая уже имеет триггер UPDATE, будет создан дополнительный триггер для обновлений. В более ранних версиях SQL Server для каждого события изменения данных INSERT, UPDATE или DELETE допускается только один триггер для каждой таблицы.
Рекурсивные триггеры
SQL Server также поддерживает рекурсивное вызов триггеров, если параметр RECURSIVE_TRIGGERS включен с помощью ALTER DATABASE.
В рекурсивных триггерах могут возникать следующие типы рекурсии:
- Косвенная рекурсия При косвенной рекурсии приложение обновляет таблицу T1. Это событие вызывает срабатывание триггера TR1, обновляющего таблицу T2. Затем срабатывает триггер T2, который обновляет таблицу T1.
- Прямая рекурсия При прямой рекурсии приложение обновляет таблицу T1. Это событие вызывает срабатывание триггера TR1, обновляющего таблицу T1. Поскольку таблица T1 уже была обновлена, триггер TR1 срабатывает снова и т. д.
В следующем примере используются оба типа рекурсий: прямая и косвенная. Допустим, для таблицы T1 определены два триггера: TR1 и TR2. Триггер TR1 рекурсивно обновляет таблицу T1. Инструкция UPDATE выполняет TR1 и TR2 по одному разу. Кроме того, запуск TR1 вызывает выполнение триггеров TR1 (рекурсивно) и TR2. В таблицах inserted и deleted триггера содержатся строки, которые относятся только к инструкции UPDATE, вызвавшей срабатывание триггера.
Описанная ситуация имеет место только в том случае, если настройка RECURSIVE_TRIGGERS включена с помощью инструкции ALTER DATABASE. Не существует определенного порядка для выполнения нескольких триггеров, определенных для одного события. Каждый триггер должен быть самодостаточным.
Отключение настройки RECURSIVE_TRIGGERS предотвращает выполнение только прямых рекурсий. Чтобы отключить косвенную рекурсию, с помощью хранимой процедуры sp_configure присвойте параметру сервера nested triggers значение 0.
Если один из триггеров (независимо от уровня вложенности) выполняет инструкцию ROLLBACK TRANSACTION, никакие другие триггеры не выполняются.
Вложенные триггеры
Для триггеров допускается не более 32 уровней вложенности. Если триггер изменяет таблицу, для которой определен другой триггер, активируется этот второй триггер. Он может, в свою очередь, вызвать третий триггер и так далее. Если любой из триггеров в цепочке отключает бесконечный цикл, то уровень вложенности превышает допустимый предел, и срабатывание триггера отменяется. Когда триггер Transact-SQL запускает управляемый код ссылкой на подпрограмму CLR, тип, или статистическое выражение, такая ссылка считается одним из 32 допустимых уровней вложенности. Это ограничение не распространяется на методы, вызываемые из управляемого кода.
Чтобы отменить вложенные триггеры, присвойте значение 0 параметру nested triggers хранимой процедуры sp_configure. Конфигурация по умолчанию поддерживает вложенные триггеры. Если вложенные триггеры отключены, отключаются и рекурсивные триггеры, независимо от значения RECURSIVE_TRIGGERS, которое установлено с помощью инструкции ALTER DATABASE.
Первый триггер AFTER, вложенный в триггер INSTEAD OF, срабатывает даже в том случае, если для сервера настроен нулевой уровень вложенных триггеров. Но в таком случае остальные триггеры AFTER не сработают. Проверьте все приложения на наличие вложенных триггеров, чтобы определить соблюдение бизнес-правил, прежде чем устанавливать значение 0 для параметра nested triggers (вложенные триггеры). Если правила не соблюдаются, внесите соответствующие изменения.
Отложенная интерпретация имен
SQL Server позволяет добавлять в хранимые процедуры, триггеры и пакеты Transact-SQL ссылки на таблицы, которые не существуют во время компиляции. Такая возможность называется отложенной интерпретацией имен.
Разрешения
Чтобы создать триггер DML, ему нужно разрешение ALTER для таблицы или представления, для которых создается этот триггер.
Чтобы создать триггер DDL в области сервера (ON ALL SERVER) или триггера входа, требуется разрешение CONTROL SERVER для этого сервера. Чтобы создать триггер DDL в области базы данных (ON DATABASE), требуется разрешение ALTER ANY DATABASE DDL TRIGGER для текущей базы данных.
Примеры
А. Использование триггера DML с предупреждающим сообщением
Следующий триггер DML выводит сообщение клиенту, когда любой пользователь пытается добавить или изменить данные в таблице в Customer базе данных AdventureWorks2022.
CREATE TRIGGER reminder1 ON Sales.Customer AFTER INSERT, UPDATE AS RAISERROR ('Notify Customer Relations', 16, 10); GOB. Использование триггера DML с предупреждающим сообщением, отправляемым по электронной почте
В следующем примере указанному пользователю ( MaryM ) по электронной почте отправляется сообщение при изменении таблицы Customer .
CREATE TRIGGER reminder2 ON Sales.Customer AFTER INSERT, UPDATE, DELETE AS EXEC msdb.dbo.sp_send_dbmail @profile_name = 'AdventureWorks2022 Administrator', @recipients = 'danw@Adventure-Works.com', @body = 'Don''t forget to print a report for the sales force.', @subject = 'Reminder'; GOC. Использование триггера DML AFTER для принудительного применения бизнес-правил между таблицами PurchaseOrderHeader и Vendor
Так ограничения CHECK ссылаются только на столбцы, для которых определено ограничение на уровне таблицы или столбца, все межтабличные ограничения (в нашем примере это бизнес-правила) следует определять как триггеры.
В следующем примере создается триггер DML в AdventureWorks2022 базе данных. Этот триггер проверяет оценку кредитоспособности для поставщика (оценка не равна 5) при попытке добавить новый заказ на покупку в таблицу PurchaseOrderHeader . Чтобы получить оценку кредитоспособности поставщика, требуется ссылка на таблицу Vendor . Если рейтинг кредитоспособности слишком низок, поступает сообщение об этом и вставка не выполняется.
USE AdventureWorks2022; GO IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL DROP TRIGGER Purchasing.LowCredit; GO -- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table -- when the credit rating of the specified vendor is set to 5 (below average). CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader AFTER INSERT AS IF (ROWCOUNT_BIG() = 0) RETURN; IF EXISTS (SELECT 1 FROM inserted AS i JOIN Purchasing.Vendor AS v ON v.BusinessEntityID = i.VendorID WHERE v.CreditRating = 5 ) BEGIN RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1); ROLLBACK TRANSACTION; RETURN END; GO -- This statement attempts to insert a row into the PurchaseOrderHeader table -- for a vendor that has a below average credit rating. -- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back. INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID, VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight) VALUES ( 2 ,3 ,261 ,1652 ,4 ,GETDATE() ,GETDATE() ,44594.55 ,3567.564 ,1114.8638 ); GOD. Использование триггера DDL уровня базы данных
В следующем примере триггер DDL используется для предотвращения удаления синонимов в базе данных.
CREATE TRIGGER safety ON DATABASE FOR DROP_SYNONYM AS IF (@@ROWCOUNT = 0) RETURN; RAISERROR ('You must disable Trigger "safety" to remove synonyms!', 10, 1) ROLLBACK GO DROP TRIGGER safety ON DATABASE; GOД. Использование триггера DDL уровня сервера
В следующем примере триггер DDL используется для вывода сообщения при возникновении на данном экземпляре сервера любого из событий CREATE DATABASE, а функция EVENTDATA используется для получения текста соответствующей инструкции на языке Transact-SQL. Примеры использования функции EVENTDATA в триггерах DDL см. в разделе Использование функции EVENTDATA.
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.
CREATE TRIGGER ddl_trig_database ON ALL SERVER FOR CREATE_DATABASE AS PRINT 'Database Created.' SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') GO DROP TRIGGER ddl_trig_database ON ALL SERVER; GOЕ. Использование триггера входа
В следующем примере триггера входа запрещается попытка войти в SQL Server в качестве члена имени входа login_test , если под этим именем входа уже три сеанса пользователя.
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.
USE master; GO CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE, CHECK_EXPIRATION = ON; GO GRANT VIEW SERVER STATE TO login_test; GO CREATE TRIGGER connection_limit_trigger ON ALL SERVER WITH EXECUTE AS 'login_test' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'login_test' AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'login_test') > 3 ROLLBACK; END;G. Просмотр событий, вызвавших срабатывание триггера
В следующем примере выполняются запросы к представлениям каталога sys.triggers и sys.trigger_events с целью определения, какие события языка Transact-SQL вызывали срабатывание триггера safety . Триггер safety , созданный в примере Г, приведен выше.
SELECT TE.* FROM sys.trigger_events AS TE JOIN sys.triggers AS T ON T.object_id = TE.object_id WHERE T.parent_class = 0 AND T.name = 'safety'; GOУпражнения по SQL
SELECT (обучающий этап) задачи по SQL запросам 120 штук, DML 10 шт. Дистанционное обучение языку баз данных SQL. Интерактивные упражнения и тестирование по операторам SELECT,INSERT,UPDATE,DELETE языка SQL. SQL remote education. SQL statements exercises. Подзапросы, Соединение таблиц, Функции SQL, Введение в SQL, Скачать книги по SQL. Команды SQL,CREATE SEQUENCE,CREATE SYNONYM,CREATE USER,CREATE VIEW,Create Table,DROP,GRANT,INSERT,REVOKE,SET ROLE,SET TRANSACTION,SQL ALTER TABLE,SQL команды.
суббота, 12 января 2019 г.
Команды DML
Язык SQL. Формирование запросов к базе данных
SQL — этом мощный и в то же время не сложный язык для управления базами данных. Он поддерживается практически всеми современными базами данных. SQL подразделятся на два подмножества команд: DDL (Data Definition Language — язык определения данных) и DML (Data Manipulation Language — язык обработки данных). Команды DDL используются для создания новых баз данных, таблиц и столбцов, а команды DML — для чтения, записи, сортировки, фильтрования, удаления данных.Structured Query Language (Язык Структурированных Запросов) разработан корпораций IBM в начале 1970-х годов. В 1986 году SQL был впервые стандартизирован организаций ANSI.
Здесь будут рассмотрены подробно лишь команды DML, поскольку их приходится использовать гораздо чаще, чем команды DDL, то есть дается просто понятие о SQL.
О командах DDL
CREATE — используется для создания новых таблиц, столбцов и индексов.
DROP — используется для удаления столбцов и индексов.
ALTER — используется для добавления в таблицы новых столбцов и изменения определенных столбцов.Команды DML
SELECT — наиболее часто используемая команда, применяется для получения набора данных из таблицы базы данных. Команда SELECT имеет следующий синтаксис:
SELECT список_полей1 FROM имя_таблицы [WHERE критерий ORDER BY список_полей2 [ASC | DESC]]
Операторы, находящие внутри квадратных скобок не обязательны, а вертикальная черта означает, что должна присутствовать одна из указанных фраз, но не обе.
Для примера создадим простейший запрос на получение данных из полей «name» и «phone» таблицы «friends»:
SELECT name, phone FROM friends
Если необходимо получить все поля таблицы, то не обязательно их перечислять, достаточно поставить звездочку (*):
SELECT * FROM friends
Для исключения из выводимого списка повторяющихся записей, используется ключевое слово DISTINCT:
SELECT DISTINCT name FROM friendsЕсли необходимо получить отдельную запись, то используется оператор WHERE. Например, нам надо получить из таблицы «friends» номер телефона «Сергей Иванов»:
SELECT * FROM friends WHERE name = ‘ Сергей Иванов’
или наоборот, нам надо узнать кому принадлежит телефон 293-89-13:
SELECT * FROM friends WHERE phone = 293-89-13′Помимо этого можно использовать подстановочные символы, таким образом, создавая шаблоны поиска. Для этого используется оператор LIKE. Оператор LIKE имеет следующие операторы подстановки:
* — соответствует строке состоящей из одного или более символов;
_ — соответствует одному любому символу;
[] — соответствует одному символу из определенного набора;Например, для получения записей из поля «name» содержащих слово «Сергей», запрос будит выглядеть следующим образом:
SELECT * FROM friends WHERE name LIKE ‘*Сергей*’
Для определения порядка, в котором возвращаются данные, используется оператор ORDER BY. Без этого оператора порядок возвращаемых данных невозможно предсказать. Ключевые слова ASC и DESC позволяют определить направление сортировки. ASC — упорядочивает по возрастанию, а DESC — по убыванию.
Например, запрос на получение списка записей из поля «name» в алфавитном порядке будет выглядеть следующим образом:
SELECT * FROM friends ORDER BY name
Обратим внимание на то, что ключевое слово ASC указывать не обязательно, поскольку оно используется по умолчанию.
INSERT — данная команда служит для добавления новой записи в таблицу. Записывается она следующим образом:
INSERT INTO имя_таблицы VALUES (список_значений)
Обратим внимание на то, что типы значений в списке значений должны соответствовать типам значений полей таблицы, например:
INSERT INTO friends VALUES (‘Анна Осипова’, ‘495-09-81’)
В данном примере в таблицу friends добавляется новая запись с указанными значениями.UPDATE — эта команда применяется для обновления данных в таблице и чаще всего используется совместно с оператором WHERE. Команда UPDATE имеет следующий синтаксис:
UPDATE имя_таблицы SET имя_поля = значение [WHERE критерий]
Если опустить оператор WHERE, то будут обновлены данные во всех определенных полях таблицы. Для примера, поменяем номер телефона Сергея Иванова:
UPDATE friends SET phone = ‘255-55-55’ WHERE name = ‘Сергей Иванов’
DELETE — как вы уже наверное поняли, эта команда служит для удаления записей из таблицы. Как и UPDATE, команда DELETE обычно используется с оператором WHERE, если этот оператор пропустить, то будут удалены все данные из указанной таблицы. Синтаксис команды DELETE выглядит следующим образом: