Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)

Пример 35: прозрачное исправление ошибок в данных

MySQL Решение 4.2.3.a (триггеры для таблицы subscribers) (продолжение)

18CREATE TRIGGER `sbsrs_name_lp_upd`

19BEFORE UPDATE

20ON `subscribers`

21FOR EACH ROW

22BEGIN

23IF (SUBSTRING(NEW.`s_name`, -1) <> '.')

24THEN

25SET @new_value = CONCAT(NEW.`s_name`, '.');

26SET @msg = CONCAT('Value [', NEW.`s_name`, '] was automatically

28SET NEW.`s_name` = @new_value;

29SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = @msg, MYSQL_ERRNO = 1000;

30END IF;

31END;

32$$

33

34DELIMITER ;

ВMS SQL Server так же просто и красиво подменить некорректное значение корректным не получится, т.к. в этой СУБД нет триггеров уровня записи.

Потому нам придётся создавать INSTEAD OF триггеры (со всеми вытекаю-

щими отсюда проблемами и ограничениями в виде «ручной» генерации значения автоинкрементируемого первичного ключа на вставке данных и запрета изменения значения первичного ключа на обновлении данных).

Зато в MS SQL Server можно очень легко и удобно передавать сообщения из триггеров. Для демонстрации этих возможностей в представленном ниже коде реализовано два варианта поведения:

•В строках 18 и 68: с помощью конструкции PRINT (которая просто выводит текстовое сообщение в консоль).

•В строках 19 и 69: с помощью функции RAISERROR (которая при таких параметрах (см. документацию17) генерирует сообщение, не приводящее к остановке операции; к тому же мы не «откатываем» транзакцию в теле триггера).

MS SQL Решение 4.2.3.a (триггеры для таблицы subscribers)

1CREATE TRIGGER [sbsrs_name_lp_ins]

2ON [subscribers]

3INSTEAD OF INSERT

4AS

5DECLARE @bad_records NVARCHAR(max);

6DECLARE @msg NVARCHAR(max);

15IF (LEN(@bad_records) > 0)

16BEGIN

17SET @msg = CONCAT('Some values were changed: ', @bad_records);

18PRINT @msg;

19RAISERROR (@msg, 16, 0);

20END;

17 https://msdn.microsoft.com/en-us/library/ms178592.aspx

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 345/545

Пример 35: прозрачное исправление ошибок в данных

MS SQL Решение 4.2.3.a (триггеры для таблицы subscribers) (продолжение)

21SET IDENTITY_INSERT [subscribers] ON;

22INSERT INTO [subscribers]

40SET IDENTITY_INSERT [subscribers] OFF;

41GO

42

43CREATE TRIGGER [sbsrs_name_lp_upd]

44ON [subscribers]

45INSTEAD OF UPDATE

46AS

47DECLARE @bad_records NVARCHAR(max);

48DECLARE @msg NVARCHAR(max);

49

50IF (UPDATE([s_id]))

51BEGIN

52RAISERROR ('Please, do NOT update surrogate PK

54ROLLBACK TRANSACTION;

55RETURN;

56END;

65IF (LEN(@bad_records) > 0)

66BEGIN

67SET @msg = CONCAT('Some values were changed: ', @bad_records);

68PRINT @msg;

69RAISERROR (@msg, 16, 0);

70END;

80JOIN [inserted]

81ON [subscribers].[s_id] = [inserted].[s_id];

82GO

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 346/545

Пример 35: прозрачное исправление ошибок в данных

Решение для Oracle традиционно повторяет логику решения для MySQL с той лишь разницей, что здесь мы можем только вывести текстовое сообщение (сгенерировать предупреждение, не влияющее на выполнение операции, на текущий момент в Oracle нельзя).

Чтобы увидеть выводимое сообщение необходимо предварительно выполнить команду SET SERVEROUTPUT ON, включающую показ таких данных.

1CREATE TRIGGER "sbsrs_name_lp_ins_upd"

2BEFORE INSERT OR UPDATE

3ON "subscribers"

4FOR EACH ROW

5DECLARE

6new_value NVARCHAR2(150);

7BEGIN

8IF (SUBSTR(:new."s_name", -1) <> '.')

9THEN

10new_value := CONCAT(:new."s_name", '.');

11DBMS_OUTPUT.PUT_LINE('Value [' || :new."s_name" ||

12'] was automatically changed to [' || new_value || ']');

13:new."s_name" := new_value;

14END IF;

15END;

На этом решение данной задачи завершено. Убедиться в его корректности вы можете самостоятельно, выполнив запросы к таблице subscribers на вставку и обновление данных — как нарушающие условие задачи, так и не нарушающие.

Решение 4.2.3.b{344}.

Общая логика решения данной задачи повторяет логику решения{344} задачи 4.2.3.a{344}, и достойным отдельного упоминания здесь можно считать только следующее:

•в MySQL и MS SQL Server приходится делать два отдельных триггера (MySQL не умеет «объединять» объявление триггеров для нескольких операций, а в MS SQL Server различается внутреннее поведение INSERT- и UP- DATE-триггера), в то время как в Oracle получается компактный одинаковый код, актуальный для обеих операций;

•в MS SQL Server без доработки модели БД можно создать только INSTEAD OF INSERT триггер, а для создания INSTEAD OF UPDATE триггера придётся отключить каскадное обновление на внешних ключах таблицы subscriptions и реализовать соответствующие операции по обеспечению ссылочной целостности в самом триггере;

•отображаемые триггерами сообщения об автоматической корректировке значения поля sb_finish в представленной ниже реализации могут содержать начальное и конечное значение даты в разных форматах (чтобы этого избежать, нужно явно приводить оба значения к одинаковому формату даты).

Востальном все представленные далее триггеры содержат лишь вариации на тему рассмотренных ранее операций.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 347/545

Пример 35: прозрачное исправление ошибок в данных

MySQL Решение 4.2.3.b (триггеры для таблицы subscriptions)

1 DELIMITER $$

2

3CREATE TRIGGER `sbscs_date_tm_ins`

4BEFORE INSERT

5ON `subscriptions`

6FOR EACH ROW

7BEGIN

8IF (NEW.`sb_finish` < NEW.`sb_start`) OR (NEW.`sb_finish` < CURDATE())

9THEN

10SET @new_value = DATE_ADD(CURDATE(), INTERVAL 2 MONTH);

11SET @msg = CONCAT('Value [', NEW.`sb_finish`, '] was automatically

13SET NEW.`sb_finish` = @new_value;

14SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = @msg, MYSQL_ERRNO = 1000;

15END IF;

16END;

17$$

18

19CREATE TRIGGER `sbscs_date_tm_upd`

20BEFORE UPDATE

21ON `subscriptions`

22FOR EACH ROW

23BEGIN

24IF (NEW.`sb_finish` < NEW.`sb_start`) OR (NEW.`sb_finish` < CURDATE())

25THEN

26SET @new_value = DATE_ADD(CURDATE(), INTERVAL 2 MONTH);

27SET @msg = CONCAT('Value [', NEW.`sb_finish`, '] was automatically

29SET NEW.`sb_finish` = @new_value;

30SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = @msg, MYSQL_ERRNO = 1000;

31END IF;

32END;

33$$

34

35 DELIMITER ;

MS SQL Решение 4.2.3.b (триггеры для таблицы subscriptions)

1CREATE TRIGGER [sbscs_date_tm_ins]

2ON [subscriptions]

3INSTEAD OF INSERT

4AS

5DECLARE @bad_records NVARCHAR(max);

6DECLARE @msg NVARCHAR(max);

7

8SELECT @bad_records =

9STUFF((SELECT ', ' + '[' + CAST([sb_finish] AS NVARCHAR) +

15FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'),

161, 2, '');

17

18IF (LEN(@bad_records) > 0)

19BEGIN

20SET @msg = CONCAT('Some values were changed: ', @bad_records);

21PRINT @msg;

22RAISERROR (@msg, 16, 0);

23END;

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 348/545

Пример 35: прозрачное исправление ошибок в данных

MS SQL Решение 4.2.3.b (триггеры для таблицы subscriptions) (продолжение)

24SET IDENTITY_INSERT [subscriptions] ON;

25INSERT INTO [subscriptions]

40END ) AS [sb_id],

41[sb_subscriber],

49END ) AS [sb_finish],

50[sb_is_active]

51 FROM [inserted];

52SET IDENTITY_INSERT [subscriptions] OFF;

53GO

54

55-- Внимание! Чтобы этот триггер можно было создать, необходимо

56-- отключить каскадное обновление на внешних

57-- ключах таблицы [subscriptions].

58-- Правда, тогда придётся доработать триггер так, чтобы с его

59-- помощью обеспечивать ссылочную целостность.

60CREATE TRIGGER [sbscs_date_tm_upd]

61ON [subscriptions]

62INSTEAD OF UPDATE

63AS

64DECLARE @bad_records NVARCHAR(max);

65DECLARE @msg NVARCHAR(max);

66

67IF (UPDATE([sb_id]))

68BEGIN

69RAISERROR ('Please, do NOT update surrogate PK

71ROLLBACK TRANSACTION;

72RETURN;

73END;

74

75SELECT @bad_records =

76STUFF((SELECT ', ' + '[' + CAST([sb_finish] AS NVARCHAR) +

82FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'),

831, 2, '');

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 349/545