inserting data into views

Question

Post reply

inserting data into views

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you have to define the column names for the inserts:

create trigger sampletrigger

on sample view

instead of insert

as

insert into basetable1(name,id) --right names?

select name,id from inserted

insert into basetable2(result) --right column name?

select result from inserted

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pramany SSC Eights! Points: 945 More actions · Answer 2

with the help of that i did execute the trigger.but i could not be able to see the inserted row in view.can you help me in that

thank you

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Make sure that the inserted row qualifies for the view based on the definition of the view.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 4

pramany (10/4/2011)
with the help of that i did execute the trigger.but i could not be able to see the inserted row in view.can you help me in that
thank you

you have to do your part in order to get help.

Lowell (10/2/2011)
...
if you post the actual table definitions and the view definition, we might be able to help further.
post the actual CREATE TABLE and CREATE VIEW definitions for definitive assistance.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pramany SSC Eights! Points: 945 More actions · Answer 5

Here is the actual table definition,view definition

create table table1

(id int,name varchar(10))

create table table2

(id int,marks int,grade varchar(2))

create view sampleview

as

select t1.name,t2.marks,t2.grade

from table1 as t1

join table2 as t2

on t1.id=t2.id

create trigger samptrigger

on sampleview

instead of insert

as

insert into table1(name)

select name from inserted

insert into table2(marks,grade)

select marks,grade from inserted

with the help of instead of insert trigger i can insert row into view but i can't see the row.

can anyone help me in that

thank you

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

You can't see the row because you're not inserting the ID columns hence there's no way the new rows will join to each other. Since they don't join, they won't appear in the view (which shows the joined rows)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 7

ok, based on your specific example now,

you can see you are not providing the Table1.ID, which joins Table@ and Table1 together.

so you end up inserting NULL,[name] from INSERTED, so theres nothing to join table2 agaisnt

IF Table1's ID columns was defined with an IDENTITY and Primary key or Unique Constraint, you could use the OUTPUT of the isnert to get the ID's, and insert them into Table2.

based on what you tried to show, this is a working example:

CREATE TABLE [dbo].[TABLE1] (

[ID] INT IDENTITY(1,1) NOT NULL,

[NAME] VARCHAR(10) NULL,

CONSTRAINT [PK__table1__368A326D] PRIMARY KEY CLUSTERED (id))

CREATE TABLE [dbo].[TABLE2] (

[ID] INT NULL,

[MARKS] INT NULL,

[GRADE] VARCHAR(2) NULL,

CONSTRAINT [FK__table2__id__3D372FFC] FOREIGN KEY (id) REFERENCES table1(id))

GO

CREATE VIEW SAMPLEVIEW

AS

SELECT t1.name,

t2.marks,

t2.grade

FROM TABLE1 AS t1

INNER JOIN TABLE2 AS t2

ON t1.id = t2.id

GO

CREATE TRIGGER TR_SampleTrigger

ON SAMPLEVIEW

INSTEAD OF INSERT

AS

BEGIN

DECLARE @MyKeys TABLE(id int ,name VARCHAR(10))

--insert into the Prime table

INSERT INTO table1(name)

OUTPUT

INSERTED.id, --this is the INSERTED table from TABLE1, Not the VIEWS's [INSERTED]

INSERTED.name

INTO @MyKeys

SELECT name FROM INSERTED

--insert into the related table

INSERT INTO table2(id,marks,grade)

SELECT myAlias.id,

INSERTED.marks,

INSERTED.grade

FROM INSERTED

INNER JOIN @MyKeys myAlias

ON myAlias.name = INSERTED.name

END --TRIGGER

GO

--testing:

INSERT INTO SAMPLEVIEW (name,marks,grade)

SELECT 'Lowell',92,'A' UNION ALL

SELECT 'Bob',84,'B' UNION ALL

SELECT 'Tom',79,'C+'

SELECT * FROM SAMPLEVIEW

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pramany SSC Eights! Points: 945 More actions · Answer 8

pramany

SSC Eights!

Points: 945

October 5, 2011 at 9:10 am

#1390747

sorry i couldn't get that.