November 5, 2008 at 12:11 pm
I have a view with an INSTEAD OF UPDATE trigger on it.
Is there any reason why this trigger would fire when one of the tables the view references is updated directly?
My understanding is that it would not, but I wanted to doublecheck.
This is how the view/trigger are constructed in case it's relevant:
CREATE VIEW vw_AR
AS
SELECT a.Field1, A.Field2, A.Field3, B.Field3, B.Field4
FROM TableA A
INNER JOIN TableB B ON A.Field2 = B.Field2
WHERE A.Field3 = 'E'
CREATE TRIGGER XU_vw_AR ON vw_AR
INSTEAD OF UPDATE
AS
UPDATE TableA
SET Field3 = 'SomeValue'
FROM TableA A INNER JOIN inserted i ON A.Field2 = i.Field2
In this scenario, would the trigger fire when TableA was updated directly (not referencing the view at all).
November 5, 2008 at 1:32 pm
In my opinion it won't fire if you bypass the view. Would be quite a mess if the same table is referenced by multiple views.
Can be easily tested by creating a table and a view+instead trigger, and issue an update directly to the table.
November 5, 2008 at 1:37 pm
Yeah, I was 90% sure it wasn't before, and now I'm almost positive it's not. It made no sense that it would be, I just couldn't understand how the trigger was being fired, and then I found this:
SELECT cast(Table_Name as nvarchar(40)) as 'Table_Name'
FROM INFORMATION_SCHEMA.Columns
WHERE Column_name = 'Field1'
as a cursor declaration which attempted to update everything in a massive update procedure. Mystery solved.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply