Создание баз данных. Основы Transact SQL. Обработка ошибок. Управление транзакциями. Триггеры
Контрольная работа - Компьютеры, программирование
Другие контрольные работы по предмету Компьютеры, программирование
е в некоторой дополнительной архивной таблице для последующего аудита. Это можно осуществить, создав триггер на удаление в первой таблице. В момент удаления записи этот триггер будет вызван на выполнение. В это время он еще будет иметь доступ ко всем удаляемым данным и сможет их скопировать в какое-либо другое место.
Триггеры также используют в более сложной и гибкой форме ограничений. Ограничения лимитированы только пределами одной таблицы, в то время как триггеры потенциально имеют доступ ко всей базе данных. Предположим, что необходимо разрешить принимать заказы только от тех клиентов, которые не имеют задолженностей по уже имеющимся счетам. В этом случае можно создать триггер на вставку, который проверяет данное правило и в случае необходимости отменяет исходную операцию вставки (откатывает транзакцию) с выбросом соответствующего сообщения об ошибке.
Общий синтаксис запроса на создания триггера выглядит следующим образом:TRIGGER имя_триггера ON имя_таблицы AFTER { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { инструкции T-SQL }
При создании триггера указывается его имя, имя таблицы, для которой он определяется, и события, при возникновении которых он должен выполняться (перечисление после ключевого слова AFTER). Эти события соответствуют трем операциям модификации данных: вставка, обновление и удаление. При этом триггер запускается один раз на каждую операцию модификации данных вне зависимости от количества затронутых этой операцией записей.
Определим в таблице City триггер, отслеживающий все изменения в данной таблице. Для хранения информации обо всех операциях модификации, выполняемых над данными в этой таблице, создадим специальную таблицу sysCityAudit со следующими столбцами: IdOperation (уникальный идентификатор операции), TypeOp (тип операции: вставка, обновление или удаление), IdCity, CityName, DateAndTime (дата и время выполнения операции), UserName (имя пользователя, изменившего данные). Запрос на создание данной таблицы приведен ниже:TABLE [dbo].[sysCityAudit](
[IdOperation] [int] IDENTITY(1,1) NOT NULL,
[TypeOp] [varchar](50) NOT NULL,
[IdCity] [int] NOT NULL,
[CityName] [nvarchar](20) NULL,
[DateAndTime] [datetime] NOT NULL CONSTRAINT [DF_sysCityAudit_DateAndTime] DEFAULT (getdate()),
[UserName] [nvarchar](256) NOT NULL CONSTRAINT [DF_sysCityAudit_UserName] DEFAULT (user_name()),[PK_sysCityAudit] PRIMARY KEY CLUSTERED
(
[IdOperation] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Запрос на создание триггера, отслеживающего все изменения в таблице City:
CREATE TRIGGER [dbo].[tr_CityAudit] ON [dbo].[City]INSERT,DELETE,UPDATENOCOUNT ON;EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)sysCityAudit(TypeOp,IdCity,CityName)Обновление, IdCity, CityNameinsertedIF EXISTS(SELECT * FROM inserted) --sysCityAudit(TypeOp,IdCity,CityName)Вставка, IdCity, CityNameinsertedsysCityAudit(TypeOp,IdCity,CityName)Удаление, IdCity, CityNamedeleted
END
Проверьте работоспособность вновь созданного триггера. Для этого произведите в таблице City различные изменения и убедитесь, что подробная информация о них была записана в таблицу sysCityAudit.
Для демонстрации применения триггера, в качестве инструмента ограничивающего возможные операции с данными в таблице в соответствии с определенными бизнес-правилами, создадим триггер, запрещающий оформлять заказы от клиентов из Москвы (к базе данных Sales сложно сформулировать осмысленное бизнес-правило, требующее использование триггера, поэтому пример весьма искусственный).
CREATE TRIGGER [dbo].[tr_OrderConstraint] ON [dbo].[Order]INSERT,UPDATENOCOUNT ON;EXISTS(SELECT *inserted AS i INNER JOINAS cust ON i.IdCust = cust.IdCust INNER JOINAS c ON cust.IdCity = c.IdCityc.CityName = NМосква)TRANSACTION
RAISERROR(Оформление заказов для клиентов из Москвы запрещено,16,1)
Поскольку триггер выполняется в рамках транзакции соответствующей ей операции модификации данных для отмены самой операции достаточно выполнить команду ROLLBACK TRANSACTION. Кроме того в случае попытки нарушения заданного бизнес-правила желательно с помощью команды RAISEERROR отправить пользователю сообщение об ошибке c ее подробным описание. Задание для самостоятельной работы: Создайте триггер, запрещающий добавлять в заказ товары, отсутствующие на складе.
Лабораторная работа №11. Система безопасности SQL Server
Система безопасности SQL Server основана на концепции защищаемых объектов (securables), т.е. объектов, на которые можно назначать разрешения, и принципалов (principles), т.е. объектов, которым можно назначать разрешения. Принципалами могут быть логины на уровне сервера, пользователи и роли на уровне базы данных. Роли назначаются пользователям. Разрешения на доступ к объектам могут предоставляться как непосредственно пользователям, так и через роли. Каждый объект имеет своего владельца, и права собственности также влияют на разрешения.
Общая схема системы безопасности SQL Server
Рис. 11.1
Server использует двухэтапную схему аутентификации. На уровне сервера пользователь распознается по своему идентификатору (LoginID), который может быть либо именем входа SQL Server, либо группой или учетной записью Windows. После входа на сервер пользователь получает те права, которые были назначены ему администратором на уровне сервера, в частности с помощью фиксированных серверных ролей. Если пользователь принадлежит роли sysadmin, то он имеет полный доступ ко всем функциям сервера, а также ко всем базам данных и объектам на нем.
Для получения доступа к базе данных логин пользователя должен быть сопоставлен с соответствующим ему идентификатором пользователя (UserID), который специфичен для каждой базы данных. Вполне возможна ситуация, когда пользователь был распознан в SQL Server, но у него нет доступа ни к одной из баз данных. Также возможно и обратное: пользователю открыт доступ к базам данных, но он не был распознан серве?/p>