Программирование DML триггеров в среде MS SQL Server

К DML триггерам относят события языка манипулирования данными - INSERT, DELETE или UPDATE.

Общая структура создания DML триггера(основные использующиеся параметры):

CREATE TRIGGER trigger_name 
ON { table | view } 
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
AS { sql_statement  [ ; ] [ ,...n ] }
где
  • trigger_name - имя триггера,соответствующее правилам идентификации(т.е должно быть уникальным)
  • table | view - таблица или представление, в которых выполняется триггер DML. На представление может ссылаться только триггер INSTEAD OF. Триггеры DML не могут быть описаны в локальной или глобальной временных таблицах.
  • FOR | AFTER - тип AFTER указывает, что триггер DML срабатывает только после успешного выполнения всех операций в инструкции SQL, запускаемой триггером. Если единственным заданным ключевым словом является FOR, аргумент AFTER используется по умолчанию. Триггеры AFTER не могут быть определены на представлениях.
  • INSTEAD OF - указывает, что триггер DML срабатывает вместо инструкции SQL, используемой триггером, переопределяя таким образом действия выполняемой инструкции триггера. Триггеры INSTEAD OF не разрешены для обновляемых представлений, использующих параметр WITH CHECK OPTION. SQL Server вызывает ошибку, если триггер INSTEAD OF добавляется к обновляемому представлению с параметром WITH CHECK OPTION. Пользователь должен удалить этот параметр при помощи инструкции ALTER VIEW перед определением триггера INSTEAD OF.
  • { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } - определяет инструкции изменения данных, по которым срабатывает триггер DML, если он применяется к таблице или представлению. Необходимо указать как минимум одну инструкцию. В определении триггера разрешены любые их сочетания в любом порядке. Для триггеров INSTEAD OF параметр DELETE не разрешен в таблицах, имеющих ссылочную связь с указанием каскадного действия ON DELETE. Точно так же параметр UPDATE не разрешен в таблицах, имеющих ссылочную связь с указанием каскадного действия ON UPDATE.
  • sql_statement - условия и действия триггера. Условия триггера указывают дополнительные критерии, определяющие, какие события — DML вызывают срабатывание триггера.

Триггеры DML используют логические таблицы deleted и inserted. По своей структуре они подобны таблице, на которой определен триггер, то есть таблице, к которой применяется действие пользователя. В таблицах deleted и inserted содержатся старые или новые значения строк, которые могут быть изменены действиями пользователя.

Триггер DELETE

Триггеры DELETE используются для предотвращения удаления данных пользователями из БД. При наличии триггера Delete SQLServer переносит удаляемую запись в логическую таблицу deleted, т.е. записи не исчезают полностью, и вы можете ссылаться на них в коде.

Пример 1.
Необходимо при удалении одной записи из таблицы подразделений Subdivision удалять и все должности из таблицы Position, входящие в это подразделение

Исходный код триггера:

USE Contacts
GO
IF (OBJECT_ID('TRG_Delete_Subdivision_Position')IS NOT NULL)
DROP TRIGGER TRG_Delete_Subdivision_Position
GO

CREATE TRIGGER TRG_Delete_Subdivision_Position
ON Subdivision INSTEAD OF DELETE
AS
IF @@ROWCOUNT=1
BEGIN
DECLARE @SubdivisionID INT
	SELECT @SubdivisionID=SubdivisionID
	FROM deleted

DELETE Position
FROM Position
WHERE Position.SubdivisionID=@SubdivisionID
END
GO

--Срабатывание триггера TRG_Delete_Subdivision_Position
--DELETE Subdivision
--FROM Subdivision
--WHERE SubdivisionID=405

Триггер UPDATE

Этот вид триггеров используется для ограничения инструкций обновления данных. Триггер UPDATE использует обе таблицы – inserted и deleted. Это объясняется тем, что триггер UPDATE использует два действия - удаление и вставку.

Пример 2
Необходимо при появлении нового заказа уменьшать количество товара в наличии

Исходный код триггера:

USE OrdersOnPurchase
GO
IF (OBJECT_ID('OnOrders')IS NOT NULL)
DROP TRIGGER OnOrders
GO

CREATE TRIGGER OnOrders ON [dbo].[Order_Details]
INSTEAD OF INSERT
AS
DECLARE @ost AS int,
              @ID AS int
	SELECT @ost=Products.QuantityOnHand-i.QuatityOrdered,
		   @ID=i.ProductNumber
	FROM Products,inserted i
	WHERE Products.ProductNumber=i.ProductNumber
IF @ost>=0
BEGIN
	INSERT INTO Order_Details(OrderNumber,ProductNumber,QuotedPrice,QuatityOrdered)
	SELECT OrderNumber,ProductNumber,QuotedPrice,QuatityOrdered
	FROM inserted
UPDATE Products SET QuantityOnHand=@ost
WHERE Products.ProductNumber=@ID
END
ELSE
BEGIN
RAISERROR('На складе нет нужного количества!',16,1)
END
GO 

--Срабатывание триггера OnOrders
--INSERT INTO Order_Details
--VALUES(16,10,1400,2)

Триггер INSERT

Триггеры INSERT запускаются при каждой попытке создать новую запись в таблице с помощью команды INSERT. При попытке вставить новую запись в таблицу SQLServer копирует эту запись в таблицу триггеров БД и специальную таблицу inserted.
--
автор: Ротенко Татьяна

Last edited Jan 23, 2012 at 6:35 AM by basph, version 5

Comments

No comments yet.