Trigger Specifics - Triggers on Views
(Page 4 of 4 )
Instead-of triggers can be defined on views also. In the following example, a trigger is created on a view that displays fields from two tables:
Create View dbo.vEquipment
AS
Select Equipment.EqId,
Equipment.Make,
Equipment.Model,
EqType.EqType
From dbo.Equipment Equipment
Inner Join dbo.EqType EqType
On Equipment.EqTypeId = EqType.EqTypeId
Go
Create Trigger itr_vEquipment_I
On dbo.vEquipment
instead of Insert
As
-- If the EqType is new, insert it
If exists(select EqType
from inserted
where EqType not in (select EqType
from EqType))
-- we need to insert the new ones
insert into EqType(EqType)
select EqType
from inserted
where EqType not in (select EqType
from EqType)
-- now you can insert new equipment
Insert into Equipment(Make, Model, EqTypeId)
Select inserted.Make, inserted.Model, EqType.EqTypeId
From inserted Inner Join EqType
On inserted.EqType = EqType.EqType
GO
Insert Into vEquipment(EqId, Make, Model, EqType)
Values (-777, 'Microsoft', 'Natural Keyboard', 'keyboard')
The trigger first examines whether the Inserted table contains EqType values that do not exist in EqTable. If they exist, they will be inserted in the EqType table. At the end, values from the Inserted table are added to the Equipment table.
The previous example illustrates one unusual feature in the use of Instead-of triggers on views. Since EqId is referenced by the view, it can (and must) be specified by the modification statement (Insert statement). The trigger can (and will) ignore the specified value since it is inserted automatically (EqId is an identity field in the base table). The reason for this behavior is that the Inserted and Deleted tables have different structures from the base tables on which the view is based. They have the same structure as the Select statement inside the view.
Columns in the view can be nullable or not nullable. The column is nullable if its expression in the Select list of the view satisfies one of the following criteria:
- The view column references a base table column that is nullable.
- The view column expression uses arithmetic operators or functions.
If the column does not allow nulls, an Insert statement must provide a value for it. This is the reason a value for the EqId column was needed in the previous example. An Update statement must provide values for all non-nullable columns referenced by the Set clause in a view with an Instead-of update trigger.
NOTE
You must specify values even for view columns that are mapped to timestamp , Identity, or computed-base table columns.
You can use the AllowNull property of the ColumnProperty() function (table function) to examine which fields are nullable from code.
NOTE
The previous code example is much more important than you might think. It allows you to insert a whole set of records at one time into the view (actually to the set of base tables behind the view). Before Instead-of triggers, you had to do this record by record with a stored procedure. This capability is very useful for loading information into a SQL Server database. For example, you can load information from a denormalized source (such as a flat file) and store it in a set of normalized, linked tables.
Another unusual feature of Instead-of triggers is the fact that they support text, ntext, and image columns in Inserted and Deleted tables. After triggers cannot handle these data types. In base tables, text, ntext, and image columns actually contain pointers to the pages holding data. In Inserted and Deleted tables, text, ntext, and image columns are stored as continuous strings within each row. No pointers are stored in these tables, and therefore the use of the Textptr() and Textvalid() functions and the Readtext, Updatetext, and Writetext statements is not permitted. All other uses are valid, such as references in the Select list or Where clause, or the use of Charindex(), Patindex(), or Substring() functions.
However, you can always use new varchar(max), Nvarchar(max), and varbinary(max) instead of old BLOB fields:
CREATE TRIGGER itrOrder_D ON dbo.OrderHeader
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON
-- collect deleted Orders in OrderDeleted INSERT INTO [dbo].[OrderDeleted]
([OrderId],[OrderDate],[RequestedById]
,[TargetDate],[CompletionDate],[DestinationLocationId]
,[Note],[OrderTypeId],[OrderStatusid]
,[UserName],[ChangeDT])
SELECT [OrderId],[OrderDate],[RequestedById]
,[TargetDate],[CompletionDate],[DestinationLocationId]
,[Note],[OrderTypeId],[OrderStatusid]
, SUSER_SNAME(), GETDATE()
FROM deleted
delete dbo.[OrderHeader]
where OrderId in (select OrderId from deleted)
END
GO
Please check back next week for the continuation of this article.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter nine of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.
|
|
