16-19. Триггеры, процедуры и функции

1.

Триггеры, процедуры
и функции
Программирование на Transact-SQL: триггеры, которые срабатывают автоматически
при изменении данных, переменные, условия и циклы, транзакции с обработкой
ошибок через TRY…CATCH, а также хранимые процедуры и пользовательские
функции. Продолжение работы с базой данных колледжа CollegeDB.

2.

Где мы сейчас
ПРОЙДЕНО В ПРЕДЫДУЩИХ ПРЕЗЕНТАЦИЯХ
13
Операторы в подзапросах
14
Объединение результатов
15
Объединения JOIN
EXISTS проверял наличие строк, ANY и ALL сравнивали набор с подзапросом
UNION складывал наборы строк вертикально и убирал дубликаты, UNION ALL сохранял всё
INNER, LEFT, RIGHT и FULL соединяли таблицы по ключам; правило N−1 и трюк IS NULL
◆ автоматически, транзакции защищают целостность, а процедуры и функции упаковывают логику в переиспользуемые объекты
До сих пор мы отдавали серверу команды по одной. Сегодня база данных начнёт работать сама: триггеры реагируют на изменения
БД

3.

Что такое триггер
ОПРЕДЕЛЕНИЕ
Триггер — это специализированная процедура, которая автоматически вызывается SQL Server при
возникновении событий в базе данных
✎ DML-триггеры
DML
Срабатывают при событиях манипулирования данными:
INSERT, UPDATE, DELETE. Всегда привязаны к конкретной
таблице или представлению и перехватывают данные только её
INSERT
UPDATE
DELETE
Не имеют параметров
DDL-триггеры
DDL
Срабатывают при событиях определения данных: CREATE,
ALTER, DROP. Отдельная подгруппа — триггеры входа
LOGON. Используются для администрирования: аудит,
управление доступом
CREATE
ALTER
выполняются явно — EXEC
⊘ Не
невозможен
DROP
LOGON
на T-SQL или в CLR✦ Создаются
сборке .NET
Триггера BEFORE, который есть во многих СУБД, в MS SQL Server не существует. По умолчанию все триггеры активные и выполняются
ПОСЛЕ действия — то есть AFTER

4.

Синтаксис CREATE TRIGGER
ОБЩАЯ ФОРМА
ПРАВИЛА И ОГРАНИЧЕНИЯ
СИНТАКСИС
CREATE TRIGGER [схема.] имя_триггера
ON { таблица | представление } -- для кого создаётся
[ WITH ENCRYPTION
-- шифрование кода
[, EXECUTE AS условие ] ] -- контекст выполнения
{ FOR | AFTER | INSTEAD OF } -- режим запуска
{ [ INSERT ] [,] [ UPDATE ] [,] [ DELETE ] } -- на
какое действие
[ NOT FOR REPLICATION ] -- не выполняется при
репликации
AS
тело_триггера
⊘ В теле триггера НЕЛЬЗЯ
CREATE / ALTER / DROP TRUNCATE TABLE
SELECT INTO
UPDATE STATISTICS
◔ Временные таблицы
создавать триггеры для них нельзя, но обращаться к ним
— можно
✦ Вложенность
до 32 уровней (NESTED TRIGGERS); рекурсия — только
при RECURSIVE_TRIGGERS ON
▦ Наборы результатов
→ FOR = AFTER по умолчанию · AFTER — только для таблиц · INSTEAD
OF — для таблиц и представлений
GRANT · REVOKE
триггер не возвращает результирующие наборы: SELECT
в теле — только с IF EXISTS

5.

Л о г и ч е с к ие т а б л и ц ы I N S E R T E D и D E L E T E D
Внутри DML-триггера доступны две логические таблицы в оперативной памяти. Они имеют ту же структуру, что и таблица триггера.
⊕ INS E R T
→ новые строки сначала попадают в inserted, затем в базовую таблицу
⊖ D E LE TE
→ удаляемая строка записывается в deleted, а затем удаляется из базовой таблицы
↻ UP DATE
→ старые значения уходят в deleted, новые появляются в inserted — обновление = удаление + вставка
deleted
таблица даёт старое значение
inserted
таблица даёт новое значение
⇄ Аналогичные механизмы в других СУБД: в Oracle и InterBase / Firebird — контекстные переменные OLD и NEW

6.

D M L - т р и г г ер ы н а C o l l e g e D B
ПРИМЕР 1 · ЖУРНАЛ @@ROWCOUNT
CREATE TRIGGER trg_Grades_Log
ON Grades
FOR INSERT, UPDATE
AS
RAISERROR('%d оценок добавлено или изменено',
0, 1, @@ROWCOUNT)
RETURN
INSERT INTO Grades(StudentID, SubjectID, Grade) VALUES (3,
2, 5) → сообщение: 1 оценок добавлено или изменено
ПРИМЕР 2 · КОНТРОЛЬ ЗНАЧЕНИЯ
CREATE TRIGGER trg_Grades_Check
ON Grades
FOR INSERT, UPDATE
AS BEGIN DECLARE @g INT
SELECT @g = Grade FROM inserted
IF (@g < 2 OR @g > 5)
BEGIN
RAISERROR('Оценка должна быть от 2 до 5', 0, 1)
ROLLBACK TRANSACTION
END
ELSE
PRINT('Оценка добавлена успешно')
END
попытка вставить 6 → ошибка и ROLLBACK: строка не попадёт в
таблицу
ⓘ Глобальная переменная @@ROWCOUNT хранит количество строк, изменённых последней командой

7.

З а щ и та д а н н ы х : I N S T E A D O F и D D L - тр и г г е р ы
Два сценария защиты: запрет удаления студента с оценками и запрет модификации таблиц базы.
ПРИМЕР 3 · INSTEAD OF DELETE
CREATE TRIGGER trg_Students_NoDelete
ON Students
INSTEAD OF DELETE
AS BEGIN
IF EXISTS (SELECT * FROM deleted d
WHERE EXISTS
(SELECT * FROM Grades g
WHERE g.StudentID = d.StudentID))
RAISERROR('У студента есть оценки — удаление запрещено!', 0, 1)
ELSE
DELETE FROM Students
WHERE StudentID IN (SELECT StudentID FROM deleted)
END
ⓘ стандартный DELETE отменяется, триггер сам решает: студент
с оценками защищён, студент без оценок удаляется вручную
П Р И МЕ Р 4 · DDL -Т Р ИГ ГЕР У Р О В Н Я БАЗЫ
CREATE TRIGGER trg_NoDropTables
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS BEGIN
PRINT 'Модификация и удаление таблиц
запрещены. Обратитесь к администратору.'
ROLLBACK
END
⛨ даже пользователь с правами не сможет удалить или изменить таблицу
— операция откатывается
▦ Область действия DDL-триггера: ON DATABASE — события текущей базы; ON ALL SERVER — весь сервер (например,
CREATE_DATABASE). Группа DDL_TABLE_EVENTS = CREATE_TABLE + ALTER_TABLE + DROP_TABLE. Триггеры входа:
ON ALL SERVER, FOR LOGON — срабатывают при установлении сеанса

8.

П е р е м е н ны е и в ы в о д
Объявляем переменные, присваиваем значения и выводим сообщения из пакета T-SQL.
Л О К А Л Ь Н Ы Е П Е Р Е МЕ Н Н Ы Е
-- объявление с инициализацией
DECLARE @find VARCHAR(10) = 'Ив% '
-- присваивание через SELECT
DECLARE @cnt INT
SELECT @cnt = COUNT(*) FROM Students
-- присваивание через SET
SET @cnt = @cnt + 1
-- вывод значения
PRINT 'Студентов: ' + CONVERT(VARCHAR(10), @cnt)
→ одна инструкция DECLARE — одна переменная; присвоить
значение можно двумя способами: SET или SELECT
@имя
— локальная переменная, создаётся программистом
Т А Б Л И Ч Н АЯ П Е Р Е МЕ Н Н АЯ
DECLARE @Top TABLE
(Id INT, Grade INT)
INSERT @Top
SELECT TOP (5) StudentID, Grade
FROM Grades
SELECT Id, Grade
FROM @Top
→ переменная-таблица живёт в пределах пакета — удобно для
промежуточных результатов
@@имя
— глобальная переменная сервера: @@ROWCOUNT,
@@ERROR, @@TRANCOUNT

9.

RAISERROR и ветвление IF…ELSE
Сообщения и ошибки сервера — плюс классическое ветвление с EXISTS.
I F … E L S E + E XI S T S
R A I S E R RO R
-- сообщение, важность, состояние, аргументы
RAISERROR('Студентов в группе: %d', 0, 1, 24)
-- %d — целое число, %s — строка
RAISERROR('Ошибка в теме %s', 10, 1, 'Индексы')
ВАЖНОСТЬ ОШИБКИ
Важность
Смысл
0–18
задают обычные пользователи
19–25
критические, только члены роли sysadmin (при 20–25
соединение разрывается)
IF (SELECT AVG(Grade)
FROM Grades) >= 4
PRINT 'Средний балл высокий'
ELSE
PRINT 'Есть над чем работать'
IF EXISTS (SELECT * FROM Grades
WHERE Grade < 3)
PRINT 'Есть неудовлетворительные'
ELSE
PRINT 'Двоек нет'
1
SELECT в условии IF — обязательно в скобках
2
EXISTS возвращает TRUE, если подзапрос вернул хотя бы
одну строку
3
несколько операторов в ветке — внутри BEGIN…END

10.

О п е р а т о р C A S E : д в е фо р м ы
Возвращается значение первой истинной ветки WHEN; не подошло ничего — сработает ELSE.
Ф О Р М А С П О И СК О М · WH E N У СЛ О В И Е
SELECT s.LastName,
CASE
WHEN AVG(g.Grade) >= 4.5
THEN 'Отличник'
WHEN AVG(g.Grade) >= 3.5
THEN 'Хорошист'
ELSE 'Троечник'
END AS Category
FROM Students s
JOIN Grades g ON g.StudentID = s.StudentID
GROUP BY s.LastName;
✓ каждому студенту — категория по среднему баллу;
возвращается значение первой истинной ветки WHEN
П Р О СТ АЯ Ф О Р МА · CASE В Ы Р АЖЕ НИ Е
SELECT
SubjectName,
CASE SubjectName
WHEN 'Математика' THEN 'Тех'
WHEN 'История' THEN 'Гум'
ELSE 'Другое'
END AS Category
FROM Subjects;
→ простая форма сравнивает выражение с константой на равенство;
ELSE выполняется, когда все WHEN ложны
◆ CASE — это функция, а не команда: работает только внутри SELECT или UPDATE, в отличие от IF. Полезные функции:
COALESCE(a, b) — первое значение, не равное NULL; NULLIF(x, 0) — превращает 0 в NULL (например, COUNT(NULLIF(Grade,
0)) посчитает только ненулевые оценки)

11.

Цикл WHILE и общие табличные выражения
Единственный цикл T-SQL — и виртуальное представление, которое живёт внутри пакета.
CT E — В И Р Т У АЛЬНО Е П Р Е Д СТАВЛ ЕН ИЕ П АК Е ТА
WH I L E — Е Д И Н СТ ВЕ НН ЫЙ Ц И К Л T -SQ L
DECLARE @i INT = 1
WHILE @i <= 5
BEGIN
PRINT @i
SET @i = @i + 1
IF @i > 3
BREAK -- досрочный выход
END
→ BREAK выходит из цикла, CONTINUE начинает итерацию заново
— обычно внутри IF. Оператор GOTO с меткой существует, но
считается плохим стилем — лучше TRY…CATCH
WITH AvgGrades(Id, Name, AvgG) AS (
SELECT s.StudentID, s.LastName + ' ' + s.FirstName,
AVG(g.Grade)
FROM Students s
JOIN Grades g ON g.StudentID = s.StudentID
GROUP BY s.StudentID,
s.LastName + ' ' + s.FirstName)
SELECT Name, AvgG
FROM AvgGrades
WHERE AvgG > (SELECT AVG(Grade)
FROM Grades);
→ CTE = именованный набор данных внутри пакета; результат тот
же, что у вложенного запроса, но код читается легче и
переиспользуется
В подзапросе CTE нельзя: ORDER BY (кроме TOP), INTO, COMPUTE, FOR XML. Рекурсивные CTE: начальная выборка +
ⓘ UNION ALL + шаг рекурсии с INNER JOIN на сам CTE — удобны для иерархий

12.

Я в н ы е т р а н з а к ци и
Группа операций, выполняющаяся как одно целое: BEGIN, COMMIT, ROLLBACK и точки сохранения.
Транзакция — группа последовательных операций, которые логически выполняются как одно целое. До COMMIT изменения видны только в
вашем соединении.
ЯВ Н АЯ Т Р АН ЗАКЦИ Я
BEGIN TRANSACTION
INSERT INTO Groups(GroupName)
VALUES ('ИТ-24')
UPDATE Students
SET GroupId = 4
WHERE StudentID = 12
COMMIT TRANSACTION
-- ROLLBACK TRANSACTION отменил бы всё
→ ошибка в любом операторе — основание для полного откката
✦ СЧЁТЧИКИ СЕРВЕРА
Т О Ч К А СО ХР АН Е Н И Я
BEGIN TRANSACTION
SAVE TRANSACTION pt1
-- ... часть операций ...
ROLLBACK TRANSACTION pt1
-- откат только до точки pt1,
-- более ранние операции сохранены
→ в одной транзакции может быть несколько точек сохранения; при
одинаковых именах действует последняя
ЗАПРЕЩЕНО В ЯВНЫХ ТРАНЗАКЦИЯХ
@@ERROR — 0 при успехе, иначе код ошибки
CREATE / ALTER / DROP DATABASE
@@TRANCOUNT — счётчик транзакций: BEGIN +1, ROLLBACK без имени → 0,
UPDATE STATISTICS
ROLLBACK с именем → −1, SAVE не меняет
BACKUP и RESTORE LOG
RECONFIGURE

13.

T R Y … C A T C H : с т р у к ту р н а я о б р а б о т к а о ш и б о к
BEGIN TRY … BEGIN CATCH — перехват ошибки вместо аварийного обрыва пакета.
Т Р АН ЗАКЦИ Я С ЗАЩИ Т О Й
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO Grades(StudentID, SubjectID, Grade)
VALUES (999, 1, 5) -- студента 999 нет!
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'Ошибка № ' + CONVERT(VARCHAR(10),
ERROR_NUMBER())
PRINT ERROR_MESSAGE()
END CATCH
КАК ЭТО РАБОТАЕТ
Ошибка в TRY немедленно прерывает выполнение:
оставшиеся инструкции TRY игнорируются,
управление передаётся в CATCH
✓ Ф У Н К Ц ИИ В C A T C H
Функция
Что возвращает
ERROR_NUMBER
номер ошибки
ERROR_MESSAGE
текст сообщения
ERROR_LINE
строка с ошибкой
ERROR_SEVERITY
важность
ERROR_STATE
состояние
ⓘ ERROR_*-функции работают только внутри блока CATCH
⊗ нарушение внешнего ключа → управление переходит в CATCH,
транзакция откатывается, ошибка выводится аккуратно — без сырого
сообщения сервера

14.

Х р а н и м ы е п р о ц е ду р ы
Компилируемый объект базы данных, который вызываетcя одной командой EXEC.
П Р О Ц Е Д У РА С П АР АМЕ ТРО М
CREATE PROCEDURE sp_GroupList
@group INT
AS
SELECT s.LastName, s.FirstName,
AVG(g.Grade) AS AvgGrade
FROM Students s
JOIN Grades g ON g.StudentID = s.StudentID
WHERE s.GroupId = @group
GROUP BY s.LastName, s.FirstName;
GO
EXEC sp_GroupList @group = 1
♛ ПОЧЕМУ ПРОЦЕДУРЫ
▦ Компилируются при первом запуске — план
сохраняется в процедурном кэше и переиспользуется
⛨ Изолируют код базы данных и гарантируют
безопасность между пользователями и таблицами
▦ Основной интерфейс для прикладных приложений
при обращении к данным
▤ ПРАВИЛА
⊘ нельзя внутри: CREATE
✿ до 1024 параметров ·
✎ ALTER PROCEDURE —
▤ sp_helptext — код процедуры,
PROC / TRIGGER / VIEW /
RULE / DEFAULT, USE база
изменить, DROP
PROCEDURE — удалить
✓ одна команда EXEC — список студентов группы со средним баллом; код хранится в БД, а не в приложении
вложенность до 32 уровней
sp_depends — связанные
объекты

15.

В о з в р а т з н а ч е н ий : O U T P U T и R E T U R N
Два способа вернуть результат из процедуры: выходной параметр и код возврата.
OUTPUT — ВЫХОДНОЙ ПАРАМЕТР
CREATE PROCEDURE sp_GroupStats
@group INT,
@avg FLOAT OUTPUT
AS
SELECT @avg = AVG(g.Grade)
FROM Grades g
JOIN Students s
ON g.StudentID = s.StudentID
WHERE s.GroupId = @group
-- вызов
DECLARE @a FLOAT
EXEC sp_GroupStats 1, @a OUTPUT
SELECT 'Средний балл:', @a
→ выходной параметр помечается OUTPUT и при объявлении, и при
вызове
RETURN — КОД ВОЗВРАТА
CREATE PROCEDURE sp_CountLow
@group INT
AS BEGIN
DECLARE @c INT
SELECT @c = COUNT(*)
FROM Grades g
JOIN Students s
ON g.StudentID = s.StudentID
WHERE s.GroupId = @group
AND g.Grade < 3
RETURN @c
END
-- вызов
DECLARE @r INT
EXEC @r = sp_CountLow 1
SELECT 'Оценок ниже 3:', @r
→ RETURN возвращает одно целое значение; передача параметров — по
позиции или по имени: @a = 5, @b = 25
↻ WITH RECOMPILE — план пересобирается при каждом запуске (полезно после добавления индекса); можно указать и в CREATE PROC, и в EXEC

16.

П о л ь з о в а т е л ь с ки е фу н к ц и и : т р и т и п а
Скалярная, встроенная табличная и многооператорная — три способа упаковать логику в объект БД.
✦
◉
СКАЛЯРНАЯ
fn_GradeLabel
CREATE FUNCTION fn_GradeLabel(@g
INT)
RETURNS NVARCHAR(15) AS
BEGIN
DECLARE @l NVARCHAR(15)
SET @l = CASE @g
WHEN 5 THEN 'отлично'
WHEN 4 THEN 'хорошо'
WHEN 3 THEN 'удовл.'
ELSE 'неудовл.' END
RETURN @l
END
-- вызов
SELECT dbo.fn_GradeLabel(Grade)
FROM Grades
→ возвращает одно значение; вызов в
SELECT или EXEC
ВСТРОЕННАЯ ТАБЛИЧНАЯ
✦
МНОГООПЕРАТОРНАЯ
fn_StudentGrades
CREATE FUNCTION
fn_StudentGrades(@sid INT)
RETURNS TABLE
AS
RETURN (SELECT subj.SubjectName AS
Subject, g.Grade
FROM Grades AS g
JOIN Subjects AS subj
ON subj.SubjectID = g.SubjectID
WHERE g.StudentID = @sid)
-- вызов
SELECT * FROM fn_StudentGrades(1);
→ один RETURN (SELECT) —
параметризованное представление:
представления не принимают параметры,
процедуры нельзя использовать в FROM
fn_Best
CREATE FUNCTION fn_Best()
RETURNS @res TABLE
(Name NVARCHAR(30), AvgG
FLOAT)
AS BEGIN
-- промежуточные расчёты,
-- INSERT в @res ...
RETURN -- без аргумента!
END
→ сама объявляет структуру таблицы,
допускает много операторов
? Детерминизм: GETDATE() недетерминирована — при одном входе возвращает разные значения, DATEADD() детерминирована.
Недетерминированные функции нельзя использовать в индексах и вычисляемых полях

17.

Практическое задание
СЕГОДНЯ РАЗОБРАЛИ
1
2
3
4
Триггеры
▤ ПРАКТИЧЕСКОЕ ЗАДАНИЕ
1
Триггер-журнал: на INSERT / UPDATE вашей
таблицы фактов — RAISERROR с
@@ROWCOUNT
2
Триггер-защита: INSTEAD OF DELETE,
запрещающий удалять запись, на которую ссылаются
(EXISTS + deleted)
3
Процедура sp_<Тема>_Stats: входной параметр +
OUTPUT со средним значением показателя
Скалярная функция fn_<Тема>_Label и табличная
fn_<Тема>_Rows
DML: AFTER и INSTEAD OF, inserted / deleted, DDL-триггеры ON DATABASE
и LOGON
T-SQL
переменные @ / @@, PRINT, RAISERROR, IF…ELSE, CASE, WHILE, CTE
Транзакции
BEGIN / COMMIT / ROLLBACK, SAVE TRANSACTION, @@ERROR,
TRY…CATCH
Процедуры и функции
4
5
Сценарий из двух связанных INSERT внутри
транзакции с TRY…CATCH и ROLLBACK при
ошибке
CREATE PROCEDURE, OUTPUT и RETURN, три типа функций, детерминизм
✓ Проверка: EXEC и SELECT * FROM sys.triggers / sys.procedures — объекты должны появиться в списке