Триггер (trigger) – особый тип хранимой процедуры, автоматически выполняемый при определенных событиях (обычно при изменении данных таблицы).
Типы триггеров:
• триггеры INSERT,
• триггеры DELETE,
• триггеры UPDATE,
• триггеры, создаваемые с учетом одновременного возникновения и совпадения событий.
Создание триггеров:
CREATE TRIGGER имя_триггера
ON имя_таблицы
[ WITH ENCRYPTION ]
FOR [ INSERT ] [, UPDATE ] [, DELETE ]
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS Операторы_SQL
WITH ENCRYPTION – скрытие кода (никто его не сможет вызвать, даже создатель триггера). В случае применения этого параметра нужно сохранить копию кода в другом месте.
WITH APPEND – указывает на то, что этот триггер должен быть введен в действие, даже если на этой таблице уже определен триггер такого же типа (для согласования с предыдущими версиями SQL Server, поскольку в SQL Server 2005 и так можно делать).
NOT FOR REPLICATION – если указана эта опция, то триггер не запускается каждый раз, когда модификация таблицы происходит в связи с выполнением задач, относящихся к репликации (т.е. не применяется к реплицируемой копии таблицы).
В разделе Операторы_SQL определение триггера среди других операторов SQL может включать оператор ROLLBACK TRAN [ SACTION ] (отменить транзакцию).
При работе с триггерами доступны две специальные таблицы – таблица вставок (inserted) и таблица удалений (deleted).
Таблица deleted содержит копии записей, затронутых операторами DELETE или UPDATE. При выполнении этих операторов записи удаляются из таблицы триггера и перемещаются в таблицу удалений. Т.е. обычно в таблице триггера и таблице удалений нет совпадающих записей.
В таблице inserted хранятся копии записей, затронутых операторами INSERT или UPDATE, при выполнении которых записи вставляются новые записи и в таблицу триггера и в таблицу вставок.
Просмотр информации о триггерах осуществляется с помощью хранимых процедур:
sp_helptext имя_триггера – выводит код триггера,
sp_depends имя_триггера – выводит список объектов триггера,
sp_helptrigger имя_таблицы – выводит список триггеров, определенных на указанной таблице.
Удаление триггера аналогично удалению других объектов (с помощью команды DROP)
Применение триггеров
Назначение триггера – предотвращение вставки, изменения или удаления записей, отвечающим некоторым условиям.
Например, если надо запретить вставлять в таблицу Заказчики записи о заказчиках из Москвы, можно применить следующий триггер:
CREATE TRIGGER ЗаказчикиМосквы
ON Заказчики
FOR INSERT, UPDATE
AS
IF EXISTS
( SELECT 'True' FROM Inserted JOIN Заказчики
ON Inserted.КодЗаказчика= Заказчики. КодЗаказчика
WHERE Заказчики.Город=’Москва’ )
ROLLBACK TRAN
То есть проверяется наличие в таблице Inserted записи, удовлетворяющей заданному условию. И в случае, когда такая надпись есть (т.е. ее вставляет пользователь) происходит отмена транзакции.
Задание 1. Составить триггер, запрещающий вставлять в таблицу Сотрудники записи о сотрудниках, старше 65 лет. Проверьте работу триггера.
Задание 2. Составить триггер, запрещающий удалять из таблицы Заказы записи о заказах, дата выполнения которых позднее сегодняшнего дня. Проверьте работу триггера.
Примечание: здесь надо проверять наличие записи только в таблице Deleted, т.е. не надо производить соединение таблиц (как это было для триггера в примере).
В триггерах возможно обращение к данным из связанной таблицы. Кроме того, можно предусмотреть вывод пользовательского сообщения об ошибке. Рассмотрим эти две возможности на примере.
Пример задачи: необходимо создать триггер, который не допускал бы указание заказа с сегодняшней датой выполнения в таблице Сотрудники-Заказы. Пусть триггер также предусматривает вывод соответствующего сообщения при попытке вставить запись о таком заказе.
CREATE TRIGGER ОтменаВставкиЗаказа
ON Сотрудники-Заказы
FOR INSERT, UPDATE
AS
IF EXISTS
( SELECT ‘True’ FROM Inserted JOIN Заказы
ON Inserted.НомерЗаказа=Заказы.НомерЗаказа
WHERE Convert (varchar(20), Заказы.ДатаВыполнения, 104) = Convert (varchar(20), GetDate(), 104)
BEGIN
RAISERROR (‘Заказы с сегодняшним днём исполнения не вставлять’,3,1)
ROLLBACK TRAN
END
Здесь конвертирование дат необходимо, чтобы сравнение их происходило в одинаковом формате, иначе даже одинаковые даты, но записанные в различных форматах, не будут восприниматься как равные.
Оператор RAISERROR предназначен для вывода сообщения об ошибке. Приведем лишь обязательные параметры этого оператора:
RAISERROR ( { номер_сообщения_об_ошибке | ‘строка’ | имя_переменной } ,
уровень_серьезности, номер_состояния )
номер_сообщения_об_ошибке - номер сообщения об ошибке, определенного пользователем, которое сохранено в представлении каталога sys.messages при помощи процедуры sp_addmessage. Номера ошибок для пользовательских сообщений об ошибках должны превышать 50000.
‘строка’ - это текст сообщения об ошибке, который не должен содержать более 2 047 символов.
имя_переменной - переменная любого допустимого типа данных для символов.
уровень_серьезности - определенный пользователем уровень серьезности, связанный с этим сообщением. Уровень серьезности от 0 до 18 может указать любой пользователь. Уровни серьезности от 19 до 25 могут быть указаны только пользователями с более широкими правами.
номер_состояния - произвольное целое число от 1 до 127. Если одна и та же пользовательская ошибка возникает в нескольких местах, то при помощи этого уникального номера состояния для каждого местоположения можно определить, в каком месте кода появилась ошибка.
Задание 3. Составить триггер, запрещающий вставлять в таблицу Заказы запись о заказах, сделанных заказчиками из Москвы. Предусмотрите вывод сообщения при попытке вставить такую запись. Проверьте работу триггера.
Задание 4. Составить триггер, запрещающий удалять из таблицы Сотрудники-Заказы записи, в которых участвуют сотрудники со стажем работы не более 2 лет. Предусмотрите вывод сообщения при попытке удалить такую запись. Проверьте работу триггера.
Задание 5. Просмотрите информацию о триггерах, заданных на таблицах Заказы и Сотрудники.
Посетил аукцион, прикупил несколько отличных вещей очень дешево. Рекомендую.
Вкуснейшие круассаны - пальчики оближешь.
Профессиональные электростанции - энергии хватит, гарантированно.
суббота, 18 апреля 2009 г.
Управление данными
Назначение любой базы данных заключается работа с данными, под которой понимается ввод новых данных, их изменение и удаление, а также выборка данных в соответствии с некоторыми критериями.
Большинство баз данных не хранят статистические данные, которыми приходится непрерывно управлять. SQL-Server предлагает следующие способы модификации данных в таблицах:
• INSERT. Добавление одной или нескольких записей в таблицу базы данных.
• SELECT … INTO. Копирование результата выполнения запроса в таблицу.
• BULK INSERT. Закачка исходных данных, хранящихся в текстовых файлах.
• UPDATE. Обновление записей таблицы базы данных.
• DELETE. Удаление записей таблицы базы данных.
Вставка записей
С помощью команды INSERT осуществляется вставка новых записей в таблицу, но не модифицирование уже существующих.
Эта команда имеет 4 синтаксические формы: в первых двух задаются значения полей одной записи, а две другие позволяют добавить несколько записей, являющихся результатом выборки данных, хранимой процедуры или другой конструкции:
INSERT [INTO] имя_таблицы [ (список_полей) ] VALUES ( { DEFAULT | значение } [,…n] )
INSERT [INTO] имя_таблицы DEFAULT VALUES
INSERT [INTO] имя_таблицы [ (список_полей) ] команда_SELECT
INSERT [INTO] имя_таблицы [ (список_полей) ] конструкция_SQL
Необязательный параметр INTO используется лишь для обозначения того, что следующий параметр имя_таблицы является именем таблицы, в которую будут вставлены данные.
Параметр список_полей указывает имена полей, разделенных запятыми, в которые будет производиться вставка данных. В списке полей должны присутствовать все поля, для которых запрещены Null-значения и не определены значения по умолчанию. Запрещено указание вычисляемых полей. Если аргумент список_полей опущен, то данные будут вставляться последовательно во все поля таблицы, начиная с первого.
Набор вставляемых данных определяется в параметре VALUES, причем количество аргументов и их значения должны соответствовать количеству полей в списке и их типам, а порядок аргументов должен соответствовать порядку полей.
Зарезервированное слово DEFAULT VALUES указывает на то, что запись будет содержать только значения по умолчанию.
Например, в таблицу Students (StudentID, FirstName, LastName, BirthDate), будет добавлена запись с заданными значениями полей, в поле счетчика – сгенерировано автоматически:
INSERT INTO Students (FirstName, LastName, BirthDate)
VALUES ( ‘Иван’, ‘Иванов’, Convert (datetime,'01.01.1988',104) )
Выполнение команды
INSERT INTO Students DEFAULT VALUES
приведет к ошибке, т.к. не для всех полей указаны значения по умолчанию.
При использовании команды SELECT в таблицу вставляется несколько записей, являющихся результатов выборки данных других таблиц. Однако если хотя бы одна из вставляемых записей нарушает ограничения целостности таблицы, то вся команда INSERT отменяется.
Например, следующей командой в таблицу Students вставляются данные из таблицы DimEmployee базы данных AdventureWorksDW:
INSERT INTO Students (FirstName, LastName, BirthDate)
SELECT FirstName, LastName, BirthDate
FROM AdventureWorksDW..DimEmployee
Задание 1. В таблицу Сотрудники Вашей базы данных внесите сведения о двух сотрудниках с помощью первой синтаксической формы INSERT.
Задание 2. В таблицу Заказчики Вашей базы данных внесите сведения о заказчиках из таблицы DimCustomer базы данных AdventureWorksDW.
Закачка данных из файла
Используется для вставки больших объемов данных их текстового файла в существующую таблицу при помощи команды BULK INSERT, которая имеет следующий синтаксис:
BULK INSERT имя_таблицы FROM имя_файла
[ WITH (
[ DATAFILETYPE [=’тип’,] ]
[ FIELDTERMINATOR [=’символ’], ]
[ KEEPIDENTITY, ]
[ ROWTERMINATOR [=’символ’], ]
[ FIRSTROW [=номер], ]
[ LASTROW [=номер], ]
) ]
В данной команде обязательно указывается таблица, в которую вставляются данные, и полное имя файла данных, по отношению к компьютеру на котором функционирует SQL-Server.
Параметр DATAFILETYPE определяет метод представления данных в файле при его создании. Значение ‘char’ указывает на то, что данные в файле представлены в текстовом формате.
В параметре FIELDTERMINATOR задается символ-разделитель полей в файле (применяется для файлов типа ‘char’), по умолчанию – используется символ табуляции (‘\t’).
В параметре ROWTERMINATOR задается символ-разделитель записей в файле, по умолчанию – символ конца строки (‘\n’).
При указании KEEPIDENTITY в таблицу разрешается вставка данных в поля с установленным ограничением IDENTITY, если же этот параметр опущен, то сервером автоматически генерируются необходимые значения.
Параметры FIRSTROW и LASTROW определяют соответственно номера начальной и конечной строк диапазона, который переносится в таблицу. По умолчанию перенос начинается с первой и заканчивается последней строкой.
Задание 3. Заполните таблицу Заказы Вашей базы данных из текстового файла (не менее 4 записей).
Ввод данных с помощью интерфейса пользователя
Конечно, данные в таблицу можно ввести, открыв ее в Обозревателе объектов.
Задание 4. Заполните таблицу Товары Вашей базы данных, открыв эту таблицу в Обозревателе объектов (не менее 4 записей).
Изменение записей
Обновление записей осуществляется с помощью команды UPDATE, которая позволяет выполнять как простое обновление данных в поле, так и сложные модификации данных во множестве записей таблицы.
При выполнении данной команды обновляются записи только одной таблицы базы данных, если же ни одна запись не обновлена, то генерируется сообщение об ошибке.
Данная команда имеет следующий синтаксис:
UPDATE имя_таблицы
SET { имя_поля = выражение | DEFAULT } [,…n]
[ FROM имя_таблицы ]
[ WHERE условие_отбора ]
В результате выполнения команды происходит обновление одного или нескольких полей, указанных в разделе SET.
Значение поля является значением некоторого выражения, содержащего стандартные функции, переменные и поля таблиц, заданных в разделе FROM.
С помощью зарезервированного слова DEFAULT полю присваивается значение, определенное по умолчанию.
Изменению подвергаются все записи таблицы, удовлетворяющие условию отбора, указанному в разделе WHERE, а его правила записи аналогичны правилам в команде SELECT.
Например, чтобы изменить номер телефона у студента по фамилии Иванов необходимо выполнить следующую команду:
UPDATE Students
SET Phone = ‘35-35-35’
WHERE LastName like ‘Иванов’
В качестве выражения при обновлении значения поля записи может быть использован подзапрос. При этом необходимо учитывать, что подзапрос должен возвращать единственное значение для каждой записи.
Например, пусть имеются связанные таблицы Teacher (TeacherID, FirstName, LastName) и Lessons (Group, Subject, TeacherID). Чтобы указать, что занятия по информатике должен проводить только преподаватель по фамилии Петров, необходимо выполнить следующую команду:
UPDATE Lessons
SET TeacherID = (SELECT TeacherID FROM Teacher WHERE (LastName like ‘Петров’) )
WHERE Subject like ‘%[Ии]нформ%’
Задание 5. Измените название города на ‘Киров’ для заказчика с заданным наименованием.
Задание 6. Измените дату выполнения заказа на сегодняшнюю для тех заказов, которые были сделаны не позднее, чем 30 дней назад.
Задание 7. Составьте команду, изменяющую данные таблицы Заказы таким образом, чтобы для всех заказов с заданным наименование (например, ‘ручная работа’) был указан конкретный товар (например, ‘плетеная мебель’).
Удаление записей
Удаление данных из таблицы осуществляется построчно, при этом можно выполнить удаление как одной записи, так и нескольких, удовлетворяющих некоторому условию.
Синтаксис данной команды:
DELETE [ FROM ] имя_таблицы
[ WHERE условие_отбора ]
Параметр условие_отбора определяет набор записей, которые необходимо удалять. Правила записи данного раздела аналогичны правилам этого раздела команды SELECT.
Для удаления всех записей некоторой таблицы используется команда:
TRUNCATE TABLE ИмяТаблицы.
При этом удаляются все записи, однако структура таблицы сохраняется, как и связанные с ней объекты. Данная команда выполняет быстрее команды DELETE, т.к. сервером регистрируется только освобождение страниц памяти, а не удаление каждой записи со страницы.
Задание 8. Удалите из таблицы Сотрудники тех, кому в прошлом году исполнилось более 60 лет.
Приобрел мягкое и удобное одеяло, не нарадуюсь теперь. Спишь, как младенец, и не холодно, и не жарко.
Самые свежие и актуальные Новости Балаково: все, что интересно узнать!
Черный Город - бесплатная он-лайн игра
Большинство баз данных не хранят статистические данные, которыми приходится непрерывно управлять. SQL-Server предлагает следующие способы модификации данных в таблицах:
• INSERT. Добавление одной или нескольких записей в таблицу базы данных.
• SELECT … INTO. Копирование результата выполнения запроса в таблицу.
• BULK INSERT. Закачка исходных данных, хранящихся в текстовых файлах.
• UPDATE. Обновление записей таблицы базы данных.
• DELETE. Удаление записей таблицы базы данных.
Вставка записей
С помощью команды INSERT осуществляется вставка новых записей в таблицу, но не модифицирование уже существующих.
Эта команда имеет 4 синтаксические формы: в первых двух задаются значения полей одной записи, а две другие позволяют добавить несколько записей, являющихся результатом выборки данных, хранимой процедуры или другой конструкции:
INSERT [INTO] имя_таблицы [ (список_полей) ] VALUES ( { DEFAULT | значение } [,…n] )
INSERT [INTO] имя_таблицы DEFAULT VALUES
INSERT [INTO] имя_таблицы [ (список_полей) ] команда_SELECT
INSERT [INTO] имя_таблицы [ (список_полей) ] конструкция_SQL
Необязательный параметр INTO используется лишь для обозначения того, что следующий параметр имя_таблицы является именем таблицы, в которую будут вставлены данные.
Параметр список_полей указывает имена полей, разделенных запятыми, в которые будет производиться вставка данных. В списке полей должны присутствовать все поля, для которых запрещены Null-значения и не определены значения по умолчанию. Запрещено указание вычисляемых полей. Если аргумент список_полей опущен, то данные будут вставляться последовательно во все поля таблицы, начиная с первого.
Набор вставляемых данных определяется в параметре VALUES, причем количество аргументов и их значения должны соответствовать количеству полей в списке и их типам, а порядок аргументов должен соответствовать порядку полей.
Зарезервированное слово DEFAULT VALUES указывает на то, что запись будет содержать только значения по умолчанию.
Например, в таблицу Students (StudentID, FirstName, LastName, BirthDate), будет добавлена запись с заданными значениями полей, в поле счетчика – сгенерировано автоматически:
INSERT INTO Students (FirstName, LastName, BirthDate)
VALUES ( ‘Иван’, ‘Иванов’, Convert (datetime,'01.01.1988',104) )
Выполнение команды
INSERT INTO Students DEFAULT VALUES
приведет к ошибке, т.к. не для всех полей указаны значения по умолчанию.
При использовании команды SELECT в таблицу вставляется несколько записей, являющихся результатов выборки данных других таблиц. Однако если хотя бы одна из вставляемых записей нарушает ограничения целостности таблицы, то вся команда INSERT отменяется.
Например, следующей командой в таблицу Students вставляются данные из таблицы DimEmployee базы данных AdventureWorksDW:
INSERT INTO Students (FirstName, LastName, BirthDate)
SELECT FirstName, LastName, BirthDate
FROM AdventureWorksDW..DimEmployee
Задание 1. В таблицу Сотрудники Вашей базы данных внесите сведения о двух сотрудниках с помощью первой синтаксической формы INSERT.
Задание 2. В таблицу Заказчики Вашей базы данных внесите сведения о заказчиках из таблицы DimCustomer базы данных AdventureWorksDW.
Закачка данных из файла
Используется для вставки больших объемов данных их текстового файла в существующую таблицу при помощи команды BULK INSERT, которая имеет следующий синтаксис:
BULK INSERT имя_таблицы FROM имя_файла
[ WITH (
[ DATAFILETYPE [=’тип’,] ]
[ FIELDTERMINATOR [=’символ’], ]
[ KEEPIDENTITY, ]
[ ROWTERMINATOR [=’символ’], ]
[ FIRSTROW [=номер], ]
[ LASTROW [=номер], ]
) ]
В данной команде обязательно указывается таблица, в которую вставляются данные, и полное имя файла данных, по отношению к компьютеру на котором функционирует SQL-Server.
Параметр DATAFILETYPE определяет метод представления данных в файле при его создании. Значение ‘char’ указывает на то, что данные в файле представлены в текстовом формате.
В параметре FIELDTERMINATOR задается символ-разделитель полей в файле (применяется для файлов типа ‘char’), по умолчанию – используется символ табуляции (‘\t’).
В параметре ROWTERMINATOR задается символ-разделитель записей в файле, по умолчанию – символ конца строки (‘\n’).
При указании KEEPIDENTITY в таблицу разрешается вставка данных в поля с установленным ограничением IDENTITY, если же этот параметр опущен, то сервером автоматически генерируются необходимые значения.
Параметры FIRSTROW и LASTROW определяют соответственно номера начальной и конечной строк диапазона, который переносится в таблицу. По умолчанию перенос начинается с первой и заканчивается последней строкой.
Задание 3. Заполните таблицу Заказы Вашей базы данных из текстового файла (не менее 4 записей).
Ввод данных с помощью интерфейса пользователя
Конечно, данные в таблицу можно ввести, открыв ее в Обозревателе объектов.
Задание 4. Заполните таблицу Товары Вашей базы данных, открыв эту таблицу в Обозревателе объектов (не менее 4 записей).
Изменение записей
Обновление записей осуществляется с помощью команды UPDATE, которая позволяет выполнять как простое обновление данных в поле, так и сложные модификации данных во множестве записей таблицы.
При выполнении данной команды обновляются записи только одной таблицы базы данных, если же ни одна запись не обновлена, то генерируется сообщение об ошибке.
Данная команда имеет следующий синтаксис:
UPDATE имя_таблицы
SET { имя_поля = выражение | DEFAULT } [,…n]
[ FROM имя_таблицы ]
[ WHERE условие_отбора ]
В результате выполнения команды происходит обновление одного или нескольких полей, указанных в разделе SET.
Значение поля является значением некоторого выражения, содержащего стандартные функции, переменные и поля таблиц, заданных в разделе FROM.
С помощью зарезервированного слова DEFAULT полю присваивается значение, определенное по умолчанию.
Изменению подвергаются все записи таблицы, удовлетворяющие условию отбора, указанному в разделе WHERE, а его правила записи аналогичны правилам в команде SELECT.
Например, чтобы изменить номер телефона у студента по фамилии Иванов необходимо выполнить следующую команду:
UPDATE Students
SET Phone = ‘35-35-35’
WHERE LastName like ‘Иванов’
В качестве выражения при обновлении значения поля записи может быть использован подзапрос. При этом необходимо учитывать, что подзапрос должен возвращать единственное значение для каждой записи.
Например, пусть имеются связанные таблицы Teacher (TeacherID, FirstName, LastName) и Lessons (Group, Subject, TeacherID). Чтобы указать, что занятия по информатике должен проводить только преподаватель по фамилии Петров, необходимо выполнить следующую команду:
UPDATE Lessons
SET TeacherID = (SELECT TeacherID FROM Teacher WHERE (LastName like ‘Петров’) )
WHERE Subject like ‘%[Ии]нформ%’
Задание 5. Измените название города на ‘Киров’ для заказчика с заданным наименованием.
Задание 6. Измените дату выполнения заказа на сегодняшнюю для тех заказов, которые были сделаны не позднее, чем 30 дней назад.
Задание 7. Составьте команду, изменяющую данные таблицы Заказы таким образом, чтобы для всех заказов с заданным наименование (например, ‘ручная работа’) был указан конкретный товар (например, ‘плетеная мебель’).
Удаление записей
Удаление данных из таблицы осуществляется построчно, при этом можно выполнить удаление как одной записи, так и нескольких, удовлетворяющих некоторому условию.
Синтаксис данной команды:
DELETE [ FROM ] имя_таблицы
[ WHERE условие_отбора ]
Параметр условие_отбора определяет набор записей, которые необходимо удалять. Правила записи данного раздела аналогичны правилам этого раздела команды SELECT.
Для удаления всех записей некоторой таблицы используется команда:
TRUNCATE TABLE ИмяТаблицы.
При этом удаляются все записи, однако структура таблицы сохраняется, как и связанные с ней объекты. Данная команда выполняет быстрее команды DELETE, т.к. сервером регистрируется только освобождение страниц памяти, а не удаление каждой записи со страницы.
Задание 8. Удалите из таблицы Сотрудники тех, кому в прошлом году исполнилось более 60 лет.
Приобрел мягкое и удобное одеяло, не нарадуюсь теперь. Спишь, как младенец, и не холодно, и не жарко.
Самые свежие и актуальные Новости Балаково: все, что интересно узнать!
Черный Город - бесплатная он-лайн игра
Подписаться на:
Сообщения (Atom)