Triggers on views

[Spacecadet]

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.

You may want to put constraints on the data which a normal constraint would not be able to handle.
A trigger can catch those inserts or updates which break the business rules, return an error and rollback the insert/update.

It makes more sense to put that sort of constraint in the table/view trigger than having to define than allow potentially bad data into the table and always trust other applications to behave properly.

[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.

[Spacecadet]

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

I know

they are good for maintaining business logic

[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.

[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...

[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.

[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]

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]

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]

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.

[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.