Trigger not firing
-
Hi everybody. One of our developers came to me this morning with a strange problem and even though we found a way of fixing it, I'd still want to share it and maybe someone can explain to me what caused this behaviour.
We had a updatable View:
CREATE VIEW dbo.US_FUNCTIONLIST_VALUES
WITH VIEW_METADATA
AS
select
liv.id,
lst.id lst_id,
lst.name lst_name,
lst.fnc_id,
lst.fnc_name,
liv.value_1,
liv.value_2,
liv.value_3
from
(select fnc.id fnc_id,fnc.name fnc_name,lst.*
from us_functions fnc, us_lists lst
where lst.list_type = 1) lst
left join us_list_values liv
on liv.lst_id = lst.id and liv.fnc_id = lst.fnc_id
On this view an INSTEAD OF Trigger was created.
ALTER TRIGGER UPDATE_US_FUNCTIONLIST
ON US_FUNCTIONLIST_VALUES
INSTEAD OF update
AS
BEGIN
SET NOCOUNT ON
UPDATE US_LIST_VALUES SET LST_ID=INSERTED.LST_ID,
FNC_ID=INSERTED.FNC_ID,
VALUE_1=INSERTED.VALUE_1,
VALUE_2=INSERTED.VALUE_2,
VALUE_3=INSERTED.VALUE_3
FROM INSERTED
WHERE US_LIST_VALUES.ID = INSERTED.ID
END
However every time we tried to update the view we received either error 8624 internal SQL error (in QA) or "Another user has modified the contents of this table or view; the database row you're modifying no longer exists in the database".
After some testing I figuered out that the trigger wasn't actually firing at all. Even when I reduced the trigger to a simple PRINT statement it wouldn't do a thing.
The only workaround I found was creating two views instead of one and creating triggers on both.
CREATE lst_view
AS
SELECT
fnc.id fnc_id,
fnc.name fnc_name,
lst.*
from us_functions fnc, us_lists lst
where lst.list_type = 1
-- and
CREATE VIEW dbo.US_FUNCTIONLIST_VALUES
WITH VIEW_METADATA
AS
select
liv.id,
lst.id lst_id,
lst.name lst_name,
lst.fnc_id,
lst.fnc_name,
liv.value_1,
liv.value_2,
liv.value_3
from lst_View lst
left join us_list_values liv
on liv.lst_id = lst.id and liv.fnc_id = lst.fnc_id
I looked around at MS knowledgebase, several websites and discussionforums but I can't find anything about such a case. Why does the trigger not fire in the first case, and if you're not allowed to create a trigger on this view, why don't we get an error while creating the trigger.
M
[font="Verdana"]Markus Bohse[/font]
-
Are you sure it's not a problem with the Update statement. If you look up the topic 'INSTEAD OF UPDATE trigger' in BOL, you can read that
quote:
UPDATE statements that reference views with INSTEAD OF UPDATE triggers must supply values for all nonnullable view columns referenced in the SET clause. This includes view columns that reference columns in the base table for which input values cannot be specified, ...A way to check this is by supplying a value in the SET clause for all columns in the view.
-
quote:
Are you sure it's not a problem with the Update statementWhat's for sure in this world ??? But I'm pretty sure it's not the Update statement. I've tested it with updating one, several and all columns no difference.
[font="Verdana"]Markus Bohse[/font]
-
While doing some more research and testing I discovered some interesting fact. If I change the order of the tables in my view definition the trigger works.
So instead of
from
(select fnc.id fnc_id,fnc.name fnc_name,lst.*
from us_functions fnc, us_lists lst
where lst.list_type = 1) lst
left join us_list_values liv
on liv.lst_id = lst.id and liv.fnc_id = lst.fnc_id
It needs to be
from
us_list_values liv
Right join (select fnc.id fnc_id,fnc.name fnc_name,lst.*
from us_functions fnc, us_lists lst
where lst.list_type = 1) lst
on liv.lst_id = lst.id and liv.fnc_id = lst.fnc_idCan anyone explain why this makes a difference ?
[font="Verdana"]Markus Bohse[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply