Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql auditing triggers Expand / Collapse
Author
Message
Posted Saturday, October 18, 2008 2:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 23, 2009 3:05 AM
Points: 2, Visits: 35

I have to write DDL trigger in sql 2005, using CLR feature. Can anyone provides help in this regard
Post #588094
Posted Saturday, October 18, 2008 3:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
What do you want to do with said trigger and why do you have to use CLR?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #588099
Posted Sunday, October 19, 2008 11:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:46 PM
Points: 31,081, Visits: 15,527
There is information in Books Online on DDL triggers and lots of articles on CLR programming. In addition to Gail's questions, you should do some research and ask specific questions, not an open one about how to do all of some project.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #588287
Posted Tuesday, October 28, 2008 11:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 23, 2009 3:05 AM
Points: 2, Visits: 35
Hi

Actually I am Developing a Database Auditing Infrastructure. I am working in SQL Server 2005.
So my main focus of area is "SQL Triggers" and "stored Procedures". I ask for help on CLR routine i thought it would help me.

If you can me some other suggestion. I would be thankful to you.
Secondly can you suggest me a way that is there any possibility of writing Generalized DML triggers on all tables of database or not ???

Thanks for fast Reply
Regards


Post #593389
Posted Wednesday, October 29, 2008 1:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
nzubaria (10/28/2008)
Hi
Actually I am Developing a Database Auditing Infrastructure. I am working in SQL Server 2005.
So my main focus of area is "SQL Triggers" and "stored Procedures". I ask for help on CLR routine i thought it would help me.


Anything dealing with data is probably better written in T-SQL than CLR, unless you have a good reason to need the CLR


If you can me some other suggestion. I would be thankful to you.
Secondly can you suggest me a way that is there any possibility of writing Generalized DML triggers on all tables of database or not ???


It is possible, but I would strongly recommend against it. Generic triggers tend to perform poorly, leading to slow data changes, deadlocks and all other such unpleasant things.

What I've done in the past is to have a stored procedure that can, using dynamic SQL, create triggers on all tables. That way you only have to write the code once, but the triggers are specific to their tables and hence reasonably fast.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #593409
Posted Monday, January 7, 2013 2:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:31 PM
Points: 161, Visits: 461
Both a trigger and cdc (change data capture) can be enabled to monitor changes to a table. When would you use each of them ?
Post #1403882
Posted Monday, January 7, 2013 3:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
Please post new questions in a new thread. Thank you.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1403886
Posted Tuesday, January 8, 2013 3:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:31 PM
Points: 161, Visits: 461
Thanks Gail, created thread 'SQL 2008 R2 - CDC or Trigger'
Post #1404463
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse