Lab9-10_SQL (1059137), страница 3
Текст из файла (страница 3)
1. Создадим представление, не отображающее столбец city. Чтобы создать представление, которое отображает данные только клиентов из города Феникс, в SQL Server Management Studio откройте новый запрос SQL Server и выполните следующий код.
USE Sales
GO
CREATE VIEW PHX_Customers AS
SELECT fname, lname, address, state, zip, phone
FROM Customers
WHERE City = 'Phoenix'
2. Дабы убедиться, что это представление отображает только те столбцы, которые вы задали, щелкните на кнопке New Query и выполните следующий код
(рис. 10).
USE Sales
SELECT * FROM PHX_Customers
Рис. 10. Список клиентов из Феникса
3. Попытайтесь вставить через представление данные нового клиента. Для этого используйте команду Query=> New Query и выполните следующий код (рис. 11).
USE Sales
INSERT PHX_Customers
VALUES ('Timothy', 'Calunod', '423 Third', 'CA', '95023', ' 9252221212')
Итак, мы создали представление, не позволяющее вставлять новые записи, поскольку в него не включено поле city, которое должно заполняться в таблице Customers. В следующей последовательности действий мы создадим триггер INSTEAD OF, который будет вставлять отсутствующее значение при выполнении вставки через представление.
Рис. 11. Попытка вставки клиента без указания города привела к ошибке
-
В окне Object Explorer раскройте папку Views=> PHX_Customers для базы данных Sales, щелкните правой кнопкой на папке Triggers и выберите команду New Trigger.
-
В строке CREATE TRIGGER введите имя триггера (Add_City), в строке ON введите имя представления (dbo. PHX_Customers).
-
Строку AFTER замените на INSTEAD OF INSERT.
-
В поле (--Insert statements for trigger here) введите следующий код триггера
DECLARE
@FNAME VARCHAR(20),
@LNAME VARCHAR(20),
@ADDR VARCHAR(50),
@CITY VARCHAR(20),
@STATE STATE,
@ZIP Char(5),
@PHONE CHAR(10)
SET @CITY = 'Phoenix'
SET @FNAME = (SELECT FNAME FROM INSERTED)
SET @LNAME = (SELECT LNAME FROM INSERTED )
SET @ADDR = (SELECT ADDRESS FROM INSERTED)
SET @STATE = (SELECT STATE FROM INSERTED)
SET @ZIP = (SELECT ZIP FROM INSERTED)
SET @PHONE = (SELECT PHONE FROM INSERTED)
INSERT CUSTOMERS
VALUES (@FNAME, @LNAME, @ADDR, @CITY, @STATE, @ZIP, @PHONE)
6. Для создания нового триггера щелкните на кнопке Execute (рис. 12).
7. Для тестирования триггера вновь введите и выполните код:
USE Sales
INSERT PHX_Customers
VALUES ('Timothy', 'Calunod', '123 Third1', 'CA', '94023', '9252221212')
8. Чтобы удостовериться во вставке данных в таблицу Customers с заполнением столбца city, выберите в меню Query=>New Query и выполните следующий
Рис. 12. Создание триггера INSTEAD OF для вставки отсутствующего значения.
запрос (рис. 13).
USE Sales
SELECT * FROM Customers
Рис. 13. С использованием триггера замены вставка клиента была выполнена успешно
9. Закройте окно запросов.
В первой последовательности действий мы создали представление, не отображающее столбец city. Затем мы попытались вставить с помощью представление PHX_Customers новую запись, что нам не удалось, поскольку мы не можем вставлять требуемое значение city в этом представлении. Далее мы создали триггер, который считывает из таблицы inserted все значения, требуемые для вставки, и сохраняет их в переменных памяти, а также создает переменную памяти для хранения отсутствующего значения city. После заполнения переменных памяти мы вставили запись в таблицу Customers, используя значения, хранящиеся в созданных нами переменных.
Триггеры DDL
Как и триггеры DML (Data Manipulation Language), триггеры DDL (Data Definition Language) срабатывают в ответ на событие. Основное различие между триггерами DML и DDL заключается в событии, запускающем их. Триггеры DDL не срабатывают для инструкций INSERT, UPDATE и DELETE, Они предназначены для инструкций CREATE, ALTER, DROP и вообще для любых инструкций, модифицирующих структуру базы данных.
Этот новый тип триггера может оказаться полезным, когда требуется контролировать пользователей, модифицирующих структуру базы данных, их методы модификации, а также отслеживать изменения схемы. Предположим, что вы наняли временных служащих по контракту для работы с базой данных, и вам нужно, чтобы они не могли удалять столбцы, не ставя вас в известность. Для этого вы можете создать триггер DDL. Вы также можете позволить пользователю удалять любые столбцы и использовать триггер DDL для регистрации изменений в таблице.
Создадим триггер DDL, который предотвратит удаление или изменение таблиц в базе данных Sales. Чтобы посмотреть, как он работает, до создания триггера удалите связь между таблицами Orders и Products, которая обеспечивает ссылочную целостность данных и не позволит нам удалить или изменить таблицу Products даже без наличия триггера (при попытке удаления таблицы будет появляться сообщение об ограничении внешнего ключа). Для временного удаления (и последующего восстановления) связи воспользуйтесь диаграммой БД.
Для создания триггера выполните следующие действия.
-
Откройте новый запрос SQL Server и выполните следующий код.
USE Sales
GO
CREATE TRIGGER CantDropCustomers
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "CantDropCustomers" to drop or alter tables! '
ROLLBACK ;
3. Выполните команду Query=>New Query и протестируйте новый триггер путем выполнения кода
DROP TABLE Products
4. Вы должны увидеть сообщение об ошибке. В нем говорится, что вы не можете
изменять таблицы, поскольку они защищены новым триггером (рис. 14).
Рис. 14. Создан триггер на запрет удаления и обновления таблицы
5. Закройте SQL Server Management Studio.
Заключение
Триггеры представляют собой сторожевые устройства данных и срабатывают, когда пользователь пытается выполнить действие INSERT, UPDATE или DELETE. Эти три типа триггеров можно объединять в любую форму, где каждый триггер рассматривается как неявная транзакция, поскольку SQL Server помещает команду BEGIN TRAN в начало транзакции и инструкцию COMMIT в конце. Для отслеживания вставляемых и удаляемых данных триггеры используют логические таблицы inserted и deleted.
Далее мы создали некоторые триггеры, начав с каждого типа по отдельности, а затем комбинировали два типа (DELETE и UPDATE). Затем мы обсудили управление модификацией данных через представление с помощью триггера INSTEAD OF. Этот специальный тип триггера используется для замены действия инструкций INSERT, UPDATE и DELETE, чтобы сохранить данные в таблицах, на которых основано представление.
И наконец, мы рассмотрели новые триггеры DDL. С помощью этих триггеров вы можете защитить схему базы данных от случайных или преднамеренных изменений путем перехвата инструкций языка определения данных.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
