Post reply

Database Trigger....

  • February 25, 2008 at 9:10 am

    #124239

    HI Guys...

    Actually we are working as a team on database.. but all of v are using "sa" username and password to logon to sql server.. now i have created a different users and their profiles on the server... what i want to have a little help.. How can i create a database level trigger, which will make a inert a row into a specific table that .. this table/procedure/function/ has bee edit/created/delete by this user and the ip address of the machine on which he's working..

    Thanks and looking forward.

    -MALIK

  • February 25, 2008 at 9:22 am

    #781621

    Probably the eaiest thing for you to do is to create a trace on create database. But you can create a trigger using

    CREATE TRIGGER ddl_Trig_DBCreate

    ON ALL SERVER

    FOR CREATE_DATABASE

    ......

    Use the EventData function to get the information you want.

    Kenneth

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • February 25, 2008 at 9:42 am

    #781634

    CLR is another viable option. Check out David Ziffer's article -

    http://www.sqlservercentral.com/articles/SS2K5+-+CLR+Integration/creatingagenericaudittriggerwithsql2005clr/2502/[/url]

    Tommy

    Follow @sqlscribe

  • February 25, 2008 at 9:53 am

    #781638

    Thanks for reply..

    it's similar to that.. but the code and the out put is on table level and it's concern about data... but i m looking actuall structure of the database..

    who has/when/which database/ which table has modify

    Thanks and looking forward.

    -MALIK

  • February 25, 2008 at 9:56 am

    #781640

    Have you looked at the schema changes history report available in SSMS? (right-click the server and/or database, select reports, schema changes history report).

    Tommy

    Follow @sqlscribe

  • February 25, 2008 at 10:03 am

    #781643

    Kenneth Fisher (2/25/2008)

    Probably the eaiest thing for you to do is to create a trace on create database. But you can create a trigger using

    CREATE TRIGGER ddl_Trig_DBCreate

    ON ALL SERVER

    FOR CREATE_DATABASE

    ......

    Use the EventData function to get the information you want.

    Kenneth

    You can use this and restrict it to the database level as well. If you go to BOL and lookup DDL triggers the documentation is pretty good. The EventData function returns an XML doc

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply