INSTEAD OF Triggers

for RuBoard

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.

for RuBoard