INSTEAD OF Trigger for View update/insert

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 321
CREATE TABLE [dbo].[Table1]
( [ID] [INT] NULL,
[Name] [VARCHAR](20) NULL
)

CREATE TABLE [dbo].[Table2]
( [ID1] [INT] NULL,
[Name1] [VARCHAR](20) NULL
)

CREATE View [dbo].[View1]
AS
SELECT Table1.ID, Table1.Name, Table2.Name1
FROM Table1
INNER JOIN Table2 ON Table2.ID1 = Table1.ID

--Now, if you try to insert to [View1], you will run into above error. To enable INSERTs on [View1], we need to create INSTEAD OF Trigger as below:
INSERT INTO [dbo].[View1] VALUES(1, 'chandu', 'chandu1')

CREATE TRIGGER [dbo].[Trig_Insert_Employee]
ON [dbo].[View1]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Table1
SELECT I.ID, I.Name
FROM INSERTED I

INSERT INTO Table2
SELECT I.ID, I.Name1
FROM INSERTED I
END

--Now, you can execute INSERT statement against the view and it will INSERT the data to the base tables.

INSERT INTO View1 Values (1,'Bandi','Bandi...... ')
SELECT * FROM View1
SELECT * FROM Table1
SELECT * FROM Table2

Comments or Responses

Login to post response