November 19, 2013 at 4:22 am
Hello,
I have idea about sending all changed which somebody do in sql to email, because here is little problem that all IT guys can do something in database.
Also I want to send for example design of table before and after, text of procedure before and after...
I tried create trigger for view sys.all_object
My first idea was that before insert/update row it will be send select text from.... and inserted.text, then I can compare....
, but is not possible create trigger for sys.tables ..
Also my question is why can I get text procedure before changed and after, because for example with job i can do some timer and execute procedure, but how can I get text and column before ???
the select for procedure i have here...
SELECT name
, A.object_id
,B.object_id
,B.definition
,type
,type_desc
,create_date
,modify_date
from sys.all_objects A
left join sys.sql_modules B
on A.object_id=B.object_id
order by modify_date desc
thx you for idea and respond
November 19, 2013 at 4:34 am
You need a DDL trigger, not a DML trigger. But I would recommend looking at service broker or extended events if you are looking at setting up some sort of auditing solution. The problem with triggers is that by the time you've thought of everything you want to audit, the number of triggers becomes unmanageable.
John
November 20, 2013 at 12:30 am
John Mitchell-245523 (11/19/2013)
You need a DDL trigger, not a DML trigger. But I would recommend looking at service broker or extended events if you are looking at setting up some sort of auditing solution. The problem with triggers is that by the time you've thought of everything you want to audit, the number of triggers becomes unmanageable.John
Yes I understand, you think that DDL for create,alter something... hm now I am reading something about broker so I will see, but like you said with trigger can be problem with lot of creating objects, for example rebuild index (alter) can be problem, but with condition it is possible handle... but maybe I will find something in broker, thx for adrive...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply