CLR Trigger won't fire on a View

Question

Post reply

CLR Trigger won't fire on a View

sbowell

SSChasing Mays

Points: 621
More actions
August 27, 2009 at 2:49 pm

#210076

Hi there,
I have a T-SQL view built on several underlying tables. When one of the records in any of the tables gets updated I want to fire a CLR Trigger on the changes. However I only want to have one trigger on the view.
In this way I will only need one trigger on the view and if I add or remove tables from the view I won't have to add or remove all the triggers as well.
When I have tried to do this I get an error stating
The object 'VW_DSC_AccountValuationData' does not exist or is invalid for this operation.
The view definitely exists and the only thing I can imagine is that you can't have a CLR Trigger on a View.
Does anyone know if this is the case?
Many Thanks
Stuart

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

are you sure the view in question qualifies as an updatable view?

that could be the issue with your CLR, rather than CLR itself.

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!

sbowell SSChasing Mays Points: 621 More actions · Answer 2

I had the same thought. So I made another view based on one table selecting the whole thing.

I tried to make another CLR Trigger on that just returned a string when it fired.

Same result.

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 3

I have a feeling you might have misunderstood triggers on views... A trigger on a view will fire when rows on the view are updated/inserted/deleted from - not when one of the rows in tables which it references are...

Can you post the DDL that you used to create the trigger on the view?

And can you make a normal SQL DML trigger to do what you wanted?

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

Can I ask a stupid question first, you are defining it as an INSTEAD OF trigger aren't you?

CEWII

sbowell SSChasing Mays Points: 621 More actions · Answer 5

Stupid Questions are ALWAYS a good idea.

No, I was try to use an after trigger. However due to the complexity, age and size of our system, it won't be possible to change all of the code to update the views instead of the underlying tables. I think Matt is right when he says I misunderstood triggers on views.

I was indeed hoping that if I updated an underlying table, the views triggers would fire. I now realise that it doesn't quite work like that.

I have made a c# dictionary in code that is populated using data readers and the following SQL. It gets a list of all the underlying views and the tables that are depended on for them. I need to update the SQL to use the 2005 system views but never-the-less you get the idea. I have then placed very simple triggers on the relevant underlying tables that call a CLR function and pass in the table name. This allows me to have similar functionality to what I thought would happen in the first place.

SELECT

DISTINCT

vw.[name] AS ViewName,

t.[name] AS TableName

FROM

sysdepends sd

JOIN sysobjects vw ON vw.id = sd.id

JOIN sysobjects t ON t.id = sd.depid AND t.xtype = 'U'

WHERE

vw.[name] LIKE 'VW_%'

ORDER BY

ViewName, TableName

SELECT

DISTINCT

t.[name] AS TableName,

vw.[name] AS ViewName

FROM

sysdepends sd

JOIN sysobjects vw ON vw.id = sd.id

JOIN sysobjects t ON t.id = sd.depid AND t.xtype = 'U'

WHERE

vw.[name] LIKE 'VW_%'

ORDER BY

TableName, ViewName

Thank you for all your help and thoughts though chaps.

Much appreciated

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 6

sbowell (9/15/2009)
I need to update the SQL to use the 2005 system views but never-the-less you get the idea.

Can I just say - if you're on 2008 (i know this in the 2005 forum - but still) then use sys.sql_expression_dependencies in preference to sys.sql_dependencies.

sysdepends and sys.sql_dependencies are both broken in similar fashion. One way you can get around that is to define your view(s) with SCHEMABINDING - but that means you won't actually be able to change the underlying table without dropping and re-creating the view.

Hope that helps. 😀

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

sbowell (9/15/2009)
Stupid Questions are ALWAYS a good idea.
No, I was try to use an after trigger. However due to the complexity, age and size of our system, it won't be possible to change all of the code to update the views instead of the underlying tables. I think Matt is right when he says I misunderstood triggers on views.
I was indeed hoping that if I updated an underlying table, the views triggers would fire. I now realise that it doesn't quite work like that.

Yep, from BOL triggers on views need to be INSTEAD OF... You'll get it figured out..

CEWII