Trigger on a View

[SA812]

Does anyone know if it's possible to put a trigger on a view when it's modified?

We had an issue with someone changing a custom view that runs a lot of different reports. after the change was made everything was messed up. It took me 2.5 days to figure out what the problem was. Unfortuntly there is no way of knowing who changed the view.

 

[SQLDenis]

in 2005 it is pretty easy

first create this ddl trigger

CREATE TRIGGER safety 
ON DATABASE 
FOR DROP_View, ALTER_VIEW 
AS 
   PRINT 'You must disable Trigger "safety" to drop or alter tables!' 
   ROLLBACK
;

now create this view

create view abd
as
select getdate() as t
go

now try to modifiy the view

alter view abd
as
select getdate() as t2
go

here is the message that is shown

You must disable Trigger "safety" to drop or alter tables!
Server: Msg 3609, Level 16, State 2, Procedure abd, Line 3
The transaction ended in the trigger. The batch has been aborted.

Usually changes to production objects go through change control so that you know what happens
in sql server 2000 you will have a hard time without taking away privileges and some change control to prevent this kind of stuff

you can look into schemabinding but that might not help you


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SA812]

I guess I’m looking for a way to log who modified the view not just prevent them from modifying it. Preventing modification to a view is very useful though. The views are being modified via SQL Server Management Studio or Enterprise Manager.

Thanks,

 

[SQLDenis]

so instead of the rollback capture that info in the trigger and log it with host_name() etc


select host_name(),suser_sname(),getdate()

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[genomon]

Can't you execute a sp from the trigger that writes to a log table as well?

"Artificial Intelligence is no match for Natural Stupidity"

 

[SA812]

I guess i'm using SQL 2000

 

[genomon]

I guess I’m looking for a way to log...

I guess i'm using SQL 2000

You GUESS?? Doesn't matter what version you are using. If you want to create a simple log that has nothing to do with SQL services, create a table and write the relevant data to it with a stored procedure call.


"Artificial Intelligence is no match for Natural Stupidity"

 

[ESquared]

genomon, it does matter what version you are using, if you want to get notification of changes to database objects such as DROP_VIEW and ALTER_VIEW...

 

[genomon]

Gotcha - got off topic from original thread. Trigger has to fire first...

"Artificial Intelligence is no match for Natural Stupidity"