Using INSTEAD OF Triggers On Views
-
http://msdn.microsoft.com/en-us/library/def01zh2(v=vs.80).aspx
when i am trying to create the trigger as shown in the link, i am getting the error
Msg 207, Level 16, State 1, Procedure ShowInsert, Line 6
Invalid column name 'address'.
Msg 207, Level 16, State 1, Procedure ShowInsert, Line 6
Invalid column name 'city'.
Msg 207, Level 16, State 1, Procedure ShowInsert, Line 6
Invalid column name 'contract'.
Msg 207, Level 16, State 1, Procedure ShowInsert, Line 6
Invalid column name 'phone'.
Msg 207, Level 16, State 1, Procedure ShowInsert, Line 6
Invalid column name 'state'.
Msg 207, Level 16, State 1, Procedure ShowInsert, Line 6
Invalid column name 'zip'.
Am i missing anything?
Thanks
rxm119528
-
Something is obviously not right if you're getting those errors. However, without knowing exactly what you have typed into SSMS, we have no way to know what the cause is. Given that it says the field names aren't valid, there are a number of possible explanations, including the possibility that you didn't have all of your query selected when you hit execute, or that the default database at the time of execution wasn't the correct AdventureWorks database. But again, you haven't posted what you have actually typed in, nor any other details than the error message. Please post your code and provide more detail.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Please see the screenshot
-
Have you checked the tables to be sure those fields still have the names that the article was using? That article looked kind of old, and the field names may have changed over the years. I'm not terribly familiar with AdventureWorks, and I don't even have it installed on my local machine, so I can't see for myself.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Attached is the script for authors table.
Basically what i am trying to understand is
"You may want to insert data to columns not visible in the view. To do so create an INSTEAD OF trigger on the view to handle inserts."
Is it possible to insert data to columns that are not part of the view using the insted of trigger.
thanks
-
That article says:
For more information and examples of INSTEAD OF triggers, see the documentation for your database server. If you are using Microsoft SQL Server, see "INSTEAD OF" in SQL Server Books Online.
The books online article is the right one to use:
http://technet.microsoft.com/en-us/library/ms175089(v=sql.105).aspx
-
rxm119528 (8/14/2014)
Attached is the script for authors table.Basically what i am trying to understand is
"You may want to insert data to columns not visible in the view. To do so create an INSTEAD OF trigger on the view to handle inserts."
Is it possible to insert data to columns that are not part of the view using the insted of trigger.
thanks
I have never looked closely at this but it really doesn't seem to make sense to me. How can the inserted virtual table have more columns than the view does? I realize is the MS documentation but it seems to be horribly in this case. In order for this to work as described your insert into the view would have to include columns that don't exist in the view which would throw an exception too. If you instead used a constant or some other way of deriving the value for the columns not in the view it would work just fine. BOL has lots of errors but this one seems to be really bad.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Thanks Sean for confirming that. I know that is not possible, but i am reading the Offical Microsoft dcoumentation, So wanted to corfirm with experts.
-
rxm119528 (8/14/2014)
Thanks Sean for confirming that. I know that is not possible, but i am reading the Offical Microsoft dcoumentation, So wanted to corfirm with experts.Don't think I should be considered an expert but it sure seems like a strange example. 😉 Hopefully somebody else will chime in to either confirm or deny my comments.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
expert is definitely not the right word. how about someone who is has more knowledge and expretise with the sql server then me.:-)
-
I think you just need to take the point of view of the trigger. Triggers operate with tables, but can fire off of a view, so the INSERTED virtual table is going to have ALL of the columns in the underlying table. You just don't want to insert without regard to column order. It took me a while to realize that it might be column order that could be a problem, but there's a real easy way to see what's going on.
Do a test by creating the trigger, and let the trigger insert the values into some test table that duplicates the authors table, but specify the fields in the exact order they appear in the table, or, specify the field order as part of the INSERT statement, like this:
INSERT authors(au_id,au_lname,au_fname,phone,address,city,state,zip,contract)
SELECT au_id,au_lname,au_fname,phone,address,city,state,zip,contract
FROM INSERTED
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
sgmunson (8/14/2014)
I think you just need to take the point of view of the trigger. Triggers operate with tables, but can fire off of a view, so the INSERTED virtual table is going to have ALL of the columns in the underlying table. You just don't want to insert without regard to column order. It took me a while to realize that it might be column order that could be a problem, but there's a real easy way to see what's going on.Do a test by creating the trigger, and let the trigger insert the values into some test table that duplicates the authors table, but specify the fields in the exact order they appear in the table, or, specify the field order as part of the INSERT statement, like this:
INSERT authors(au_id,au_lname,au_fname,phone,address,city,state,zip,contract)
SELECT au_id,au_lname,au_fname,phone,address,city,state,zip,contract
FROM INSERTED
Right but this is NOT a case of inserting into the table. This is an instead of insert on a view. That means the ONLY columns available in inserted are those in the view.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I guess that will teach me not to trust older MS articles. I don't mess with triggers often, and I personally wouldn't set a trigger on a view, nor can I think of a good reason to. Consider me smarter because of you...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
sgmunson (8/14/2014)
I guess that will teach me not to trust older MS articles. I don't mess with triggers often, and I personally wouldn't set a trigger on a view, nor can I think of a good reason to. Consider me smarter because of you...Not because of me really. I saw the post here and it just looked strange. I had never considered using an instead of insert on a view until this post. I just looked at the examples that MS posted and they just plain and simple don't work. The very concept they are trying to convey seems flawed by the implementation. Quite strange!!! In fact, the most recent version of that article is exactly the same.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
The author of the article definitely seems to have dropped the ball here. I'm guessing what he intended to write was something like:
CREATE TRIGGER ShowInsert on AuthorsNames
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO authors
SELECT SomeOtherTable.address, inserted.au_fname
, inserted.au_id, inserted.au_lname
, SomeOtherTable.city, SomeOtherTable.contract
, SomeOtherTable.phone, SomeOtherTable.state
, SomeOtherTable.zip
FROM inserted
INNER JOIN SomeOtherTable
ON authors.au_id = SomeOtherTable.au_id
END
- Les
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply