Triggers on views

[BrilloPad]

Just had a brilliant issue where a view was dropped and recreated. It then no longer worked for update - no one could figure out how it had ever worked!

Turned out to be a trigger on a view! A very sneaky way of obfuscation if ever I saw one.

I thought I had seen it all - clearly not.

[Spacecadet]

Just had a brilliant issue where a view was dropped and recreated. It then no longer worked for update - no one could figure out how it had ever worked!

Turned out to be a trigger on a view! A very sneaky way of obfuscation if ever I saw one.

I thought I had seen it all - clearly not.

Had this a couple of times and now triggers are one of the things on my checklist of possible things to check.

It's a shame that they never became more popular as they are good for maintaining business logic in the database structure which the RDBMS might not be able to cope with.

[oscarose]

Just had a brilliant issue where a view was dropped and recreated. It then no longer worked for update - no one could figure out how it had ever worked!

Turned out to be a trigger on a view! A very sneaky way of obfuscation if ever I saw one.

I thought I had seen it all - clearly not.

Don't understand your post. There are rules when you can use an UPDATE statement on a view e.g. can't contain an aggregated field. If you are allowed to UPDATE a view, surely this just updates the base table so where does the trigger come in?



p.s. I passed a 'rigourous' 2 hour on-line advanced SQL test a while back and always interested in this type of thing!

[Spacecadet]

Don't understand your post. There are rules when you can use an UPDATE statement on a view e.g. can't contain an aggregated field. If you are allowed to UPDATE a view, surely this just updates the base table so where does the trigger come in?



p.s. I passed a 'rigourous' 2 hour on-line advanced SQL test a while back and always interested in this type of thing!

You can create a trigger on a view in the same way you create a trigger on a table.
Any DML performed through the view fires the trigger. If the view is dropped and recreated then the trigger is lost.

[oscarose]

You can create a trigger on a view in the same way you create a trigger on a table.
Any DML performed through the view fires the trigger. If the view is dropped and recreated then the trigger is lost.

Triggers on views sound dodgy to me. As we all know, views are primarily for hiding data complexity, summarising data and security. IMO, the trigger should be on the underlying table.

As always though, there's probably a valid reason and requirement to have the trigger on the view for a particular business reason.

[Spacecadet]

Triggers on views sound dodgy to me. As we all know, views are primarily for hiding data complexity, summarising data and security. IMO, the trigger should be on the underlying table.

As always though, there's probably a valid reason and requirement to have the trigger on the view for a particular business reason.

Not something I would do myself and I even avoid triggers on tables, the main reason being that they are not widely used and I don't like leaving suprises for other people.

[Moscow Mule]

Not something I would do myself and I even avoid triggers on tables, the main reason being that they are not widely used and I don't like leaving suprises for other people.

Is this platform specific?

We love triggers on the iSeries...

[oscarose]

Not something I would do myself and I even avoid triggers on tables, the main reason being that they are not widely used and I don't like leaving suprises for other people.

Triggers are useful for enforcing business logic - just another tool in the bag.

[Spacecadet]

Triggers are useful for enforcing business logic - just another tool in the bag.

I know

they are good for maintaining business logic

[BrilloPad]

Triggers on views sound dodgy to me. As we all know, views are primarily for hiding data complexity, summarising data and security. IMO, the trigger should be on the underlying table.

As always though, there's probably a valid reason and requirement to have the trigger on the view for a particular business reason.

CREATE TRIGGER [dbo].[my_trigger_name] ON [dbo].[my_view_name]

INSTEAD OF UPDATE, INSERT
AS
BEGIN
.
.
.
END

THERE IS NO VALID REASON!

Its just obfuscation.