CODEWITHSUNDEEP
sqlsql-server
john
john

Reputation: 9

SQL instead of insert trigger confusion

So i was watching a youtube tutorial Instead of insert trigger Part 45 (if someone is interested) about instead of triggers.

The guy there was trying to insert values into a view, which is impossible of course. So he made an instead of trigger and he used the inserted table, to insert the values into the tables the view is made of, long story.

My point is, how did he use the inserted table when there wasn't actually anything inserted?

I mean you get an error than you can't insert into the view. Doesn't the inserted table contain data that has only actually been inserted somewhere?

Upvotes: 0

Views: 84

Answers (1)

Martin Smith
Martin Smith

Reputation: 453648

The guy there was trying to insert values into a view... which is impossible of course.

This isn't true. A view can be inserted into when it meets the rules for updatable views and the values can be unambiguously mapped to inserts on a single table.

Doesn't the inserted table contain data that has only actually been inserted somewhere?

For an AFTER INSERT trigger this is basically true. The rows in the INSERTED pseudo table reflect those that were inserted to the table. For an INSTEAD OF INSERT trigger a work table is created independently to hold the data.

Looking at an example 

CREATE VIEW dbo.Foo
AS
SELECT CAST('A' AS varchar(8000)) AS X

GO 
CREATE TRIGGER TR 
ON Foo
INSTEAD OF INSERT AS 
SELECT COUNT(*)
FROM inserted

And then trying 

SET STATISTICS IO ON;
insert into  dbo.Foo
SELECT TOP 1000 REPLICATE('A',8000)
FROM master..spt_values v1;

The execution plan indicates that it inserts to a clustered index on Foo

enter image description here

There is of course no clustered index on Foo created above. The inserted table is a worktable in tempdb.

The statistics IO results show

Table 'Worktable'. Scan count 0, logical reads 7619, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'spt_values'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected) Table 'Worktable'. Scan count 1, logical reads 1002, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1000 row(s) affected)

The first reference is for the insert into it, the second for the SELECT in the trigger itself

Upvotes: 4

Related Questions