Ћекции.ќрг


ѕоиск:




 атегории:

јстрономи€
Ѕиологи€
√еографи€
ƒругие €зыки
»нтернет
»нформатика
»стори€
 ультура
Ћитература
Ћогика
ћатематика
ћедицина
ћеханика
ќхрана труда
ѕедагогика
ѕолитика
ѕраво
ѕсихологи€
–елиги€
–иторика
—оциологи€
—порт
—троительство
“ехнологи€
“ранспорт
‘изика
‘илософи€
‘инансы
’ими€
Ёкологи€
Ёкономика
Ёлектроника

 

 

 

 


ѕрограммирование триггеров




 

»нструкции триггеров используют две особые таблицы: deleted (удаленные значени€) и inserted (вставленные значени€). ѕо своей структуре они подобны таблице, на которой определен триггер, то есть таблице, к которой примен€етс€ действие пользовател€. SQL Server автоматически создает и управл€ет ими. Ёти временные таблицы, наход€щиес€ в оперативной пам€ти, используютс€ дл€ проверки результатов изменений данных и дл€ установки условий срабатывани€ триггеров. »змен€ть данные в этих таблицах или выполн€ть над ними операции €зыка DDL, например инструкцию CREATE INDEX, нельз€.

¬ триггерах DML таблицы inserted и deleted в основном используютс€ дл€ выполнени€ следующих операций.

  • –асширение ссылочной целостности между таблицами.
  • ¬ставка или обновление данных в базовых таблицах соответствующего представлени€.
  • ѕроверка на ошибки и прин€тие соответствующих мер в св€зи с по€влением ошибок.
  • ѕоиск различий между состо€ни€ми таблицы до и после изменени€ данных и прин€тие соответствующих мер в зависимости от наличи€ или отсутстви€ различий.

¬ таблице deleted наход€тс€ копии строк, с которыми работали инструкции DELETE или UPDATE. ѕри выполнении инструкции DELETE или UPDATE происходит удаление строк из таблицы триггера и их перенос в таблицу deleted. ” таблицы deleted обычно нет общих строк с таблицей триггера.

¬ таблице inserted наход€тс€ копии строк, с которыми работали инструкции INSERT или UPDATE. ѕри выполнении транзакции вставки или обновлени€ происходит одновременное добавление строк в таблицу триггера и в таблицу inserted. —троки таблицы inserted €вл€ютс€ копи€ми новых строк таблицы триггера.


“ранзакци€ обновлени€ аналогична выполнению операции удалени€ с последующим выполнением операции вставки; сначала старые строки копируютс€ в таблицу deleted, а затем новые строки копируютс€ в таблицу триггера и в таблицу inserted.

—ледующие правила относ€тс€ к инструкци€м INSERT или UPDATE, ссылающимс€ на таблицу с триггером INSTEAD OF, которые должны предоставл€ть такие значени€ дл€ столбцов, как если бы в таблице не было триггера INSTEAD OF.

  • Ќе могут быть заданы значени€ дл€ вычисл€емых столбцов и дл€ столбцов с типом данных timestamp.
  • ≈сли параметр IDENTITY_INSERT дл€ этой таблицы не равен ON, то значени€ дл€ столбцов со свойством IDENTITY не могут быть заданы.  огда значение параметра IDENTITY_INSERT равно ON, инструкции INSERT должны сами задавать это значение.
  • »нструкции INSERT должны определ€ть значени€ дл€ всех столбцов со свойством NOT NULL, не имеющих ограничений DEFAULT.
  • ƒл€ любого столбца, кроме вычисл€емых столбцов, IDENTITY и столбов типа timestamp, определение значений €вл€етс€ необ€зательным, если разрешены NULL значени€ или если какой-либо столбец со свойством NOT NULL имеет ограничение DEFAULT.

≈сли инструкци€ INSERT, UPDATE или DELETE ссылаетс€ на представление, дл€ которого определен триггер INSTEAD OF, компонент Database Engine вызывает триггер вместо того, чтобы предприн€ть какое-либо пр€мое действие по отношению к таблице. “риггер использует сведени€, представленные в таблицах inserted и deleted, дл€ создани€ инструкций, необходимых дл€ выполнени€ требуемых действий в базовых таблицах, даже в том случае, если формат данных в таблицах inserted и deleted, созданных дл€ представлени€, отличаетс€ от формата данных базовой таблицы.

‘ормат таблиц inserted и deleted триггера INSTEAD OF, заданного дл€ представлени€, совпадает со списком выборки инструкции SELECT, заданной дл€ представлени€.

Ќапример:

CREATE VIEW EmployeeNames (EmployeeID, LName, FName)

AS

SELECT e.EmployeeID, c.LastName, c.FirstName

FROM AdventureWorks.HumanResources.Employee e

JOIN AdventureWorks.Person.Contact c

ON e.ContactID = c.ContactID

 

–езультирующий набор дл€ данного представлени€ состоит из трех столбцов: одного столбца типа int и двух столбцов типа nvarchar. “аблицы inserted и deleted триггера INSTEAD OF, заданного дл€ представлени€, также содержат столбец типа int с именем EmployeeID, столбец типа nvarchar с именем LName и столбец типа nvarchar с именем FName.

—писок выборки представлени€ также может содержать выражени€, которые не сопоставлены напр€мую с каким-либо одним столбцом базовой таблицы. Ќекоторые выражени€ представлени€, такие как вызов функции или константы, могут не ссылатьс€ на столбцы и просто пропускатьс€. —ложные выражени€ могут ссылатьс€ на несколько столбцов, однако таблицы inserted и deleted содержат только по одному значению дл€ каждой вставл€емой строки. “акие же проблемы по€вл€ютс€ и в простых выражени€х представлени€, если они ссылаютс€ на вычисл€емый столбец со сложным выражением.

 

≈сли действи€ триггера завис€т от количества строк, данные в которых были изменены, можно воспользоватьс€ проверками системных переменных (например, проверкой @@ROWCOUNT).

 

—оздание триггеров

 

“риггер DML нельз€ создать дл€ временной или системной таблицы, хот€ он может ссылатьс€ на временные таблицы. —оздание триггеров в базе данных осуществл€етс€ командой CREATE TRIGGER, данна€ команда должна быть первой инструкцией пакета; все остальные инструкции пакета интерпретируютс€ как часть определени€ инструкции CREATE TRIGGER.

 

Ќесмотр€ на то, что инструкци€ TRUNCATE TABLE похожа на инструкцию DELETE без предложени€ WHERE (котора€ удал€ет все строки), она не приводит к срабатыванию триггеров DELETE, потому что инструкци€ TRUNCATE TABLE не регистрируетс€.

 

“риггеры INSTEAD OF DELETE и INSTEAD OF UPDATE нельз€ определить дл€ таблицы, котора€ имеет внешний ключ, определенный с действием DELETE или UPDATE.

 

CREATE TRIGGER trigger_name

ON { table | view }

[ WITH [ ENCRYPTION ] [ EXECUTE AS Clause ] ]

{ FOR | AFTER | INSTEAD OF }

{ [ INSERT ] [, ] [ UPDATE ] [, ] [ DELETE ] }

AS { sql_statement [; ] [,...n ] }

 

 

Trigger_name

»м€ триггера.

table | view

“аблица или представление, в которых выполн€етс€ триггер DML. Ќа представление может ссылатьс€ только триггер INSTEAD OF.

WITH ENCRYPTION

«атемн€ет текст инструкции CREATE TRIGGER хран€щийс€ в базе данных. Ќепривилегированные пользователи не смогут получить исходный текст триггера.

EXECUTE AS

”казывает контекст безопасности, в котором выполн€етс€ триггер. ѕозвол€ет управл€ть учетной записью пользовател€, используемой экземпл€ром SQL Server дл€ проверки разрешений на любые объекты базы данных, используемые триггером.

FOR | AFTER

“ип AFTER указывает, что триггер срабатывает только после успешного выполнени€ всех операций в инструкции SQL, запускаемой триггером. ¬се каскадные действи€ и проверки ограничений, на которые имеетс€ ссылка, должны быть успешно завершены, прежде чем триггер сработает.

≈сли единственным заданным ключевым словом €вл€етс€ FOR, аргумент AFTER используетс€ по умолчанию.

“риггеры AFTER не могут быть определены на представлени€х.

INSTEAD OF

”казывает, что триггер DML срабатывает вместо инструкции SQL, используемой триггером, переопредел€€ таким образом действи€ выполн€емой инструкции триггера.

Ќа каждую инструкцию INSERT, UPDATE или DELETE в таблице или представлении может быть определено не более одного триггера INSTEAD OF.

{ [ DELETE ] [, ] [ INSERT ] [, ] [ UPDATE ] }

ќпредел€ет инструкции изменени€ данных, по которым срабатывает триггер DML, если он примен€етс€ к таблице или представлению. Ќеобходимо указать как минимум одну инструкцию. ¬ определении триггера разрешены любые их сочетани€ в любом пор€дке.

ƒл€ триггеров INSTEAD OF параметр DELETE не разрешен в таблицах, имеющих ограничение целостности внешнего ключа с указанием каскадного действи€ ON DELETE. “очно так же параметр UPDATE не разрешен в таблицах, имеющих ограничение целостности внешнего ключа с указанием каскадного действи€ ON UPDATE.

Sql_statement

ƒействи€ триггера, указанные в инструкци€х €зыка Transact-SQL.

ѕример:

¬ следующем примере создаетс€ триггер. Ётот триггер провер€ет уровень кредитоспособности поставщика при попытке добавить новый заказ на покупку в таблицу PurchaseOrderHeader. „тобы получить оценку кредитоспособности поставщика, св€занного с заказом на покупку, который только что был добавлен, таблица inserted должна ссылатьс€ на таблицу Vendor и быть св€зана с ней. ¬ случае слишком низкой кредитоспособности выводитс€ соответствующее сообщение и вставка не производитс€.

 

CREATE TRIGGER LowCredit ON PurchaseOrderHeader

AFTER INSERT

AS

DECLARE @creditrating tinyint,

@vendorid int

 

SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID

FROM Purchasing.PurchaseOrderHeader AS p

INNER JOIN inserted AS i

ON p.PurchaseOrderID = i.PurchaseOrderID

JOIN Purchasing.Vendor AS v

ON v.VendorID = i.VendorID

 

IF @creditrating = 5

BEGIN

RAISERROR ('Ќизка€ кредитоспособность покупател€.', 16, 1)

ROLLBACK TRANSACTION

END

GO

»зменение триггеров

 

»зменение существующих триггеров в базе данных осуществл€етс€ командой ALTER TRIGGER.

 

ALTER TRIGGER trigger_name

ON { table | view }

[ WITH [ ENCRYPTION ] [ EXECUTE AS Clause ] ]

{ FOR | AFTER | INSTEAD OF }

{ [ INSERT ] [, ] [ UPDATE ] [, ] [ DELETE ] }

AS { sql_statement [; ] [,...n ] }

 

ѕараметры команды ALTER TRIGGER идентичны параметрам команды CREATE TRIGGER.

 

≈сли триггер был создан с опцией шифровани€ текста инструкции CREATE TRIGGER (WITH ENCRYPTION), при изменении триггера, если требуетс€ сохранить текст инструкции в зашифрованном состо€нии, должна быть также указана опци€ WITH ENCRYPTION.

¬ следующем примере измен€етс€ триггер, созданный ранее:

 

ALTER TRIGGER LowCredit ON PurchaseOrderHeader

AFTER INSERT

AS

DECLARE @creditrating tinyint,

@vendorid int

 

SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID

FROM Purchasing.PurchaseOrderHeader AS p

INNER JOIN inserted AS i

ON p.PurchaseOrderID = i.PurchaseOrderID

JOIN Purchasing.Vendor AS v

ON v.VendorID = i.VendorID

 

IF @creditrating = 10

BEGIN

RAISERROR ('Ќизка€ кредитоспособность покупател€.', 16, 1)

ROLLBACK TRANSACTION

END

GO

”даление триггеров

”даление триггеров осуществл€етс€ командой DROP TRIGGER.

DROP TRIGGER trigger_name

Trigger_name

»м€ триггера.

¬ следующем примере удал€етс€ триггер, созданный ранее:

 

DROP TRIGGER LowCredit

Ћекци€ є 16

ѕредставлени€

„то такое представление?

ѕредставление можно считать виртуальной таблицей или хранимым запросом. ≈сли представление не индексировано, его данные не хран€тс€ в базе данных в виде отдельного объекта. ¬ базе данных хранитс€ только инструкци€ SELECT. –езультирующий набор инструкции SELECT формирует виртуальную таблицу, возвращаемую представлением.

 ак и насто€ща€ таблица, представление состоит из совокупности именованных столбцов и строк данных. ѕока представление не будет проиндексировано, оно не существует в базе данных как хранима€ совокупность значений. —троки и столбцы данных извлекаютс€ из таблиц, указанных в определ€ющем представление запросе и динамически создаваемых при обращени€х к представлению.

ѕредставление выполн€ет функцию фильтра базовых таблиц, на которые оно ссылаетс€. ќпредел€ющий представление запрос может быть инициирован в одной или нескольких таблицах или в других представлени€х текущей или других баз данных.  роме того, дл€ определени€ представлений с данными из нескольких разнородных источников можно использовать распределенные запросы. Ёто полезно, например, если нужно объединить структурированные подобным образом данные, относ€щиес€ к разным серверам, каждый из которых хранит данные конкретного отдела организации.

Ќа запросы данных посредством представлений не налагаютс€ никакие ограничени€; есть только несколько ограничений на изменение данных при помощи представлений.

Ќа следующем рисунке показано представление, созданное на основе двух таблиц.

ѕри выполнении запросов через представление компонент Database Engine провер€ет, существуют ли все указанные в инструкции объекты базы данных, верны ли они в контексте инструкции и соответствуют ли инструкции модификации данных правилам обеспечени€ целостности данных. ≈сли проверка завершаетс€ ошибкой, возвращаетс€ сообщение об ошибке. ѕри успешной проверке операци€ преобразуетс€ в операцию над базовой таблицей или таблицами.

≈сли представление зависит от удаленной таблицы или представлени€, компонент Database Engine в ответ на попытку использовани€ представлени€ возвращает сообщение об ошибке. ≈сли создана нова€ таблица или представление, а структура таблицы не изменилась по сравнению с предыдущей базовой таблицей дл€ замены удаленной, то представление можно использовать снова. ≈сли из базовой таблицы были удалены столбцы, используемые в представлении, представление необходимо удалить и создать заново.





ѕоделитьс€ с друзь€ми:


ƒата добавлени€: 2016-11-22; ћы поможем в написании ваших работ!; просмотров: 894 | Ќарушение авторских прав


ѕоиск на сайте:

Ћучшие изречени€:

—тудент может не знать в двух случа€х: не знал, или забыл. © Ќеизвестно
==> читать все изречени€...

1843 - | 1444 -


© 2015-2024 lektsii.org -  онтакты - ѕоследнее добавление

√ен: 0.028 с.