لطفا منتظر باشید ...
INSTEAD OF Triggers
As its name suggests, an INSTEAD OF trigger executes instead of a DML operation. This is in contrast to AFTER triggers, which run after an operation has completed, but before the transaction has been committed. INSTEAD OF triggers are handy for updates against views and tables that would otherwise be too complex to handle with anything but a stored procedure.
Here's a simple INSTEAD OF trigger example (Listing 8-8):
Listing 8-8 A simple INSTEAD OF trigger at work.
USE tempdb
GO
CREATE TABLE AussieArtists
(ArtistId int Identity,
LastName varchar(30),
FirstName varchar(30)
)
GO
INSERT AussieArtists VALUES ('Gibb', 'Barry')
INSERT AussieArtists VALUES ('Gibb', 'Maurice')
INSERT AussieArtists VALUES ('Gibb', 'Robin')
INSERT AussieArtists VALUES ('Gibb', 'Andy')
INSERT AussieArtists VALUES ('Newton-John', 'Olivia')
INSERT AussieArtists VALUES ('Crowe', 'Russell')
INSERT AussieArtists VALUES ('Hogan', 'Paul')
INSERT AussieArtists VALUES ('Kidman', 'Nicole')
INSERT AussieArtists VALUES ('Bozinov', 'Zarko')
INSERT AussieArtists VALUES ('Hay', 'Colin')
GO
CREATE VIEW VAussieArtists AS
SELECT FirstName+' '+LastName AS Name FROM AussieArtists
GO
CREATE TRIGGER VAussieArtists_INSERT ON VAussieArtists INSTEAD OF INSERT
AS
INSERT AussieArtists (FirstName, LastName)
SELECT LEFT(Name,ISNULL(NULLIF(CHARINDEX(' ',Name),0),255)-1),
SUBSTRING(Name,NULLIF(CHARINDEX(' ',Name),0)+1,255)
FROM inserted
GO
INSERT VAussieArtists (Name) VALUES ('Greg Ham')
GO
SELECT * FROM AussieArtists
GO
DROP TABLE AussieArtists
DROP VIEW VAussieArtists
GO
(Results)
ArtistId LastName FirstName
----------- ------------------------------ ------------------------------
1 Gibb Barry
2 Gibb Maurice
3 Gibb Robin
4 Gibb Andy
5 Newton-John Olivia
6 Crowe Russell
7 Hogan Paul
8 Kidman Nicole
9 Bozinov Zarko
10 Hay Colin
11 Ham Greg
As you can see, the simple insert against the view is translated into a slightly more complex insert against the underlying table. Because we want to process the data before it goes into the underlying table, we use an INSTEAD OF trigger that parses the input and performs the insert itself.
Although you can have only one INSTEAD OF trigger for each DML operation (INSERT, UPDATE, or DELETE) on a table, you can work around this limitation by creating additional views on top of the table or view in question, each with their own INSTEAD OF triggers. Listing 8-9 presents an example:
Listing 8-9 You can set up multiple INSTEAD OF triggers using views as placeholders.
USE tempdb
GO
CREATE TABLE AussieArtists
(ArtistId int Identity,
LastName varchar(30),
FirstName varchar(30)
)
GO
INSERT AussieArtists VALUES ('Gibb', 'Barry')
INSERT AussieArtists VALUES ('Gibb', 'Maurice')
INSERT AussieArtists VALUES ('Gibb', 'Robin')
INSERT AussieArtists VALUES ('Gibb', 'Andy')
INSERT AussieArtists VALUES ('Newton-John', 'Olivia')
INSERT AussieArtists VALUES ('Crowe', 'Russell')
INSERT AussieArtists VALUES ('Hogan', 'Paul')
INSERT AussieArtists VALUES ('Kidman', 'Nicole')
INSERT AussieArtists VALUES ('Bozinov', 'Zarko')
INSERT AussieArtists VALUES ('Hay', 'Colin')
GO
CREATE VIEW VAussieArtists AS
SELECT FirstName+' '+LastName AS Name FROM AussieArtists
GO
CREATE TRIGGER VAussieArtists_INSERT ON VAussieArtists INSTEAD OF INSERT
AS
INSERT AussieArtists (FirstName, LastName)
SELECT LEFT(Name,ISNULL(NULLIF(CHARINDEX(' ',Name),0),255)-1),
SUBSTRING(Name,NULLIF(CHARINDEX(' ',Name),0)+1,255)
FROM inserted
GO
CREATE VIEW VAussies AS
SELECT Name FROM VAussieArtists
GO
CREATE TRIGGER VAussies_INSERT ON VAussies INSTEAD OF INSERT
AS
INSERT VAussieArtists (Name)
SELECT UPPER(Name)
FROM inserted
GO
INSERT VAussies (Name) VALUES ('Greg Ham')
GO
SELECT * FROM AussieArtists
ArtistId LastName FirstName
----------- ------------------------------ ------------------------------
1 Gibb Barry
2 Gibb Maurice
3 Gibb Robin
4 Gibb Andy
5 Newton-John Olivia
6 Crowe Russell
7 Hogan Paul
8 Kidman Nicole
9 Bozinov Zarko
10 Hay Colin
11 HAM GREG
As you can see, the first INSTEAD OF trigger splits the name into two fields, as it did in the last example. The second INSTEAD OF trigger uppercases the name before inserting it into the first view. Note that it doesn't insert directly into the tableonly the first view does that. Instead, it inserts into the first view so that we can be sure that the name splitting occurs. You can use this techniquethat of layering INSTEAD OF views on top of one anotherto set up some fairly sophisticated processing without needing to resort to stored procedures.
