Learn Sql Server   Learn Sql Theory   Learn Sql

 
Chapter 1
Chapter 2
Chapter 3
Chapter 4

Sql Server Indexes

Create Table Index

Sql Server Drop indexes

Sql Server FREETEXT

Sql Server Contains

Sql Server FREETEXTTABLE

Sql Server Views

Sql Server Return

Sql Server View Encryption

Sql Server Views With Order By

Sql Server WITH CHECK OPTION

Create Indexed view WITH SCHEMABINDING

Sql Server Stored Procedure

Parameterized Stored Procedure

Stored Procedure with Output Parameter

Fetch Stored Procedure

Update Stored Procedure

Delete Stored Procedure

Sql Server Cursor

Sql Server DML Triggers

Sql Server INSTEAD OF Trigger

Sql Server DDL Triggers

Alter Or Modifying Triggers

sql-server-disable-triggers-drop-triggers

Enable triggers in sql server

create index with drop existing

Functions Upper() and Lower()

Chapter 5
Chapter 6
Chapter 7
Chapter 8
Chapter 9
Chapter 10
Chapter 11
Chapter 12
Chapter 13
Chapter 14
Chapter 15
Chapter 16

INSTEAD OF Trigger in Sql Server

INSTEAD OF DML triggers execute instead of the original data modification that fired the trigger and are allowed for both tables and views. INSTEAD OF triggers are often used to handle data modifications to views that do not allow for data modifications.

DML triggers use the following syntax:

Sponsored links

CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH [ ...,n ] ]
INSTEAD OF
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ NOT FOR REPLICATION ]
AS { sql_statement [ ...n ] }

In this recipe, you’ll create a new table that will hold “pending approval” rows for the HumanResources.Department table. These are new departments that require manager approval before being added to the actual table. A view will be created to display all “approved” and “pending approval” departments from the two tables, and an INSTEAD OF trigger will be created on the view for inserts, causing inserts to be routed to the new approval table, instead of the actual HumanResources.Department table:

Create Department "Approval" table


CREATE TABLE HumanResources.DepartmentApproval
(Name nvarchar(50) NOT NULL UNIQUE,GroupName nvarchar(50) NOT NULL,
ModifiedDate datetime NOT NULL DEFAULT GETDATE()) GO

-- Create view to see both approved and pending approval departments

CREATE VIEW HumanResources.vw_Department
AS
SELECT Name, GroupName, ModifiedDate, 'Approved' Status
FROM HumanResources.Department
UNION
SELECT Name, GroupName, ModifiedDate, 'Pending Approval' Status
FROM HumanResources.DepartmentApproval

-- Create an INSTEAD OF trigger on the new view

CREATE TRIGGER HumanResources.trg_vw_Department
ON HumanResources.vw_Department
INSTEAD OF
INSERT
AS SET NOCOUNT ON
INSERT HumanResources.DepartmentApproval (Name, GroupName)
SELECT i.Name, i.GroupName FROM inserted i
WHERE i.Name NOT IN (SELECT Name FROM HumanResources.DepartmentApproval)

Insert into the new view, even though view is a UNION of two different tables

INSERT HumanResources.vw_Department (Name, GroupName)
VALUES ('Print Production', 'Manufacturing')

Check the view's contents

SELECT Status, Name FROM HumanResources.vw_Department
WHERE GroupName = 'Manufacturing'

Status Name
Approved Production
Approved Production Control
Pending Approval Print Production

How INSTEAD OF Trigger Example Works

The recipe began by creating a separate table to hold “pending approval” department rows:
CREATE TABLE HumanResources.DepartmentApproval (Name nvarchar(50) NOT NULL UNIQUE,
GroupName nvarchar(50) NOT NULL,
ModifiedDate datetime NOT NULL DEFAULT GETDATE())
Next, a view was created to display both “approved” and “pending approval” departments:
CREATE VIEW HumanResources.vw_Department
AS
SELECT Name, GroupName, ModifiedDate, 'Approved' Status
FROM HumanResources.Department
UNION
SELECT Name, GroupName, ModifiedDate, 'Pending Approval' Status
FROM HumanResources.DepartmentApproval
The UNION in the CREATE VIEW prevents this view from being updateable, as any inserts against it will be ambiguous. INSTEAD OF triggers allow you to enable data modifications against non-updateable views.
A trigger was created to react to INSERTs, routing them to the approval table so long as the department name was unique:
CREATE TRIGGER HumanResources.trg_vw_Department
ON HumanResources.vw_Department
INSTEAD OF
INSERT
AS
SET NOCOUNT ON
INSERT HumanResources.DepartmentApproval (Name, GroupName)
SELECT i.Name, i.GroupName FROM inserted i
WHERE i.Name NOT IN (SELECT Name FROM HumanResources.DepartmentApproval)>
A new INSERT was tested against the view, to see if it would be inserted in the approval table:
INSERT HumanResources.vw_Department (Name, GroupName)
VALUES ('Print Production', 'Manufacturing')
The view was then queried, showing that the row was inserted, and displayed a “pending approval status.”

Sponsored Links