If update (Phone)
PRINT ‘Изменение столбца phone’
IF ((CURRENT_USER = ‘dbo’) AND
(COLUMNS_UPDATED()&1)! = 0 -- 1-ый стлбец;
BEGIN
PRINT ‘Пользователь dbo не может изменять’ + ‘идентификационный номер автора’
ROLLBACK TRANSACTION
END
Для тестирования триггера можно попытаться выполнить команды модификации строк из таблицы и проверить полученные результаты.
UPDATED authsmall SET phone =‘415 986 - 7020’, au_fname = ‘John’
WHERE au_lname = ‘Green’
UPDATED authsmall SET phone =‘913 843 - 7302’, au_id = ‘748-126859’
WHERE au_lname = ‘Smith’
Задание 1.7.3. Создать триггер для команд INSERT и UPDATE, запрещающий производить изменения для автора Billy Geitsi.
Создание и программирование триггера можно выполнить следующим образом:
CREATE TRIGGER auth_ ins_upd ON authsmall
FOR INSERT, UPDATE
AS
IF EXISTS (SELECT * FROM authsmall
WHERE au_lname = ‘Geitsi’ AND au_fname = ‘Billy’)
BEGIN
PRINT ‘Недопустимо написание кнги’ + ‘автором Billy Geitsi’
ROLLBACK TRANSACTION
END
Для тестирования триггера можно попытаться выполнить команды модификации строк из таблицы и проверить полученные результаты.
UPDATE authsmall SET au_lname = ‘Geitsi’,
au_fname = ‘Billy’ WHERE au_lname = ‘Smith’.
1.8. Создание транзакций
Задание 1.8.1. Создать вложенные транзакции. Для этого можно выполнить следующие команды:
CREATE TABLE #aaa (cola int) -- 0-й уровень
BEGIN TRAN -- 1-й уровень
Insert into #aaa values (111)
BEGIN TRAN -- 2-й уровень
Insert into #aaa values (222)
BEGIN TRAN -- 3-й уровень
Insert into #aaa values (333)
SELECT * FROM #aaa
SELECT ‘Вложенность транзакций’, @@TRANCOUNT
ROLLBACK TRAN
SELECT * FROM #aaa -- откат на 0-й уровень
SELECT ‘Вложенность транзакций’, @@TRANCOUNT
1.9. Создание пользовательских функций
Задание 1.9.1. Создание функции Func1 типа Scalar, которая будет иметь три входных параметра. Первые два параметра будут иметь тип данных bigint, третий параметр — тип данных char(1) и значение по умолчанию "*". Функция будет возвращать значение типа данных bigint и выполнять четыре основных арифметических операции: сложение, вычитание, умножение и деление двух значений, подаваемых на вход функции. Операция, которую необходимо выполнить, будет задаваться с помощью третьего параметра, который может принимать соответственно значения " + ", " -", "*" и "/".
CREATE FUNCTION Func1(
@Varl bigint,
@Var2 bigint,
@Oper chard) = "*")
RETURNS bigint
AS
BEGIN
DECLARE @Var3 bigint
SET @Var3 =
CASE @Oper
WHEN "+" THEN @Varl + @Var2
WHEN "-" THEN @Varl - @Var2
WHEN "*" THEN @Varl * @Var2
WHEN "/" THEN @Varl / @Var2
ELSE
0
END
RETURN @Var3
END
Вызвать созданную функцию можно следующим образом
SELECT dbo.Funcl(4, 5, '+'),
dbo.Funcl(3, 7, '*') - dbo.Funcl(64, 4, '.')*2
Будет возвращен следующий результат:
9 -11
(1 row(s) affected)
Задание 1.9.2. Создание функции Func2 типа Inline, которая возвращает список идентификационных номеров авторов, (столбец au_id), их фамилий (столбец au_lname) и имен (столбец au_fname), живущих в определенном штате.
CREATE FUNCTION Func2
(@State char(2))
RETURNS TABLE
AS
RETURN SELECT au_id, au_lname. au_fname
FROM authors
WHERE state = @State
Протестируем функцию для получения списка авторов, проживающих в штате Калифорния:
SELECT * FROM Func2("CA") ORDER BY au_lname, au_fname
Будет получен следующий результат:
au_id аu_lname аu_fname
-------------------------- ---------------------- ------------------
409-56-7008 Bennet Abraham
238-95-7766 Carson Cheryl
427-17-2319 Dull Ann
213-46-8915 Green Marjorie
472-27-2349 Gringlesby Burt
846-92-7186 Hunter Sheryl
756-3-7391 Karsen Livia
486-29-1786 Locksley Charlene
724-80-9391 MacFeather Stearns
893-72-1158 McBadden Heather
267-41-2394 O'Leary Michael
274-80-9391 Straight Dean
724-08-9931 Stringer Dirk
172-32-1176 White Johnson
672-71-3249 Yokomoto Akiko (15 row(s) affected)
Задание 1.9.3. Создание функции Func3 типа Multi-Statement, которая будет принимать в качестве входного параметра строку, содержащую множество слов, разделенных пробелами, и которая будет разбивать всю строку на отдельные слова и каждое из них помещать в отдельную строку.
CREATE FUNCTION Func3
(@String nvarchar(500))
RETURNS @tabl TABLE
(Number int IDENTITY (1,1) NOT NULL,
Value nvarchar(30))
AS
BEGIN
DECLARE @Strl nvarchar(500),
@Pos int
SET (@Strl = @String
WHILE l > 0
BEGIN
SET @Pos = CHARINDEX(" ", @Strl)
IF @Pos>0
BEGIN
INSERT INTO @tabl
VALUES (SUBSTRING((@Strl, 1, @Pos))
SET @Strl = SUBSTRING(@Strl, @Pos+l. 500)
END
ELSE
BEGIN
INSERT INTO @tabl VALUES (@Strl)
BREAK
END
END
RETURN
END
Можно использовать эту функцию следующим образом:
DECLARE @Text nvarchar(4000)
SELECT @Text = notes FROM titles
WHERE title = "Is Anger the Enemy?"
SELECT * FROM Func3(@Text)
Будет получен следующий результат:
Number Value
Задание 1.9.4. В качестве примера рассмотрим использование процедуры sp_helptext для получения кода функции Func3, созданной в предыдущем разделе.
Вызов процедуры будет выглядеть следующим образом
EXEC sp_helptext "Func3"
Будет возвращен весь текст, приведенный в примере в предыдущем разделе. Если в теле функции присутствовали комментарии, то они также будут выведены. Код, используемый для создания объектов базы данных (в частности, функций), хранится в столбце text таблицы syscomments. Для получения кода функции Func3 также можно использовать следующий запрос:
SELECT text FROM syscomments WHERE ID = OBJECT_ID("Func3")
В запросе используется функция OBJECT_ID(), с помощью которой можно получить идентификационный номер объекта базы данных (например, функции) по его имени. Дело в том, что в таблице syscomments объекты идентифицируются не по имени, а по идентификационному номеру (столбец ID).