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 12»»

Understanding DDL Triggers in SQL Server 2005 Expand / Collapse
Author
Message
Posted Wednesday, March 14, 2007 1:19 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 5:48 AM
Points: 102, Visits: 125
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/sSrivathsani/2927.asp
Post #351580
Posted Wednesday, July 4, 2007 12:42 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, July 11, 2014 3:12 AM
Points: 148, Visits: 80
It's a pitty it does not cover renaming columns, tables...


Post #378856
Posted Wednesday, July 4, 2007 2:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 19, 2012 8:38 AM
Points: 4, Visits: 40

Most of the code samples are untested and need fixing if you want to see them work.

Here's what I've settled on to cover most activities - created in all mission-critical databases:

CREATE TRIGGER Trig_DBEvents ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS
    INSERT master.dbo.ddl_all_databases
        (
            DateTime,
            LoginName,
            DBName,
            UserName,
            EventType,
            CommandText
        )
    SELECT  GETDATE(),
            EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(50)'),
            EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(50)'),
            EVENTDATA().value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(50)'),
            EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(50)'),
            EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(1024)')
GO

Post #378876
Posted Thursday, July 5, 2007 9:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 13, 2009 2:12 PM
Points: 59, Visits: 8
where do I create trigger? in master database? it will fire where any new database is created,right?
Post #379277
Posted Thursday, July 5, 2007 10:15 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962

I can see trouble brewing here.  Would it be possible to:

  1. Create triggers that block all changes on tables, etc.
  2. Create a trigger that blocks changes on all triggers?

How would one every get out of this?

Another reason to tighly control such things.  A ticked off admin could do this and then leave.  You might not find it for a long time.  I have production databases that have not had a DDL change in years.

 



ATB

Charles Kincaid

Post #379298
Posted Thursday, July 5, 2007 10:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 13, 2009 2:12 PM
Points: 59, Visits: 8
I am confused with what you told in previous post.
Post #379305
Posted Thursday, July 5, 2007 5:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 3, 2009 5:41 PM
Points: 10, Visits: 12
Oracle has had this functionality for a loooong time. As a result, you might want to look at how Oracle databases use the DDL (and other) System level triggers.

For myself, I have created a DDL trigger in Oracle's SYS schema to record all changes including what object changed when, and by whom. As well, I have LOGIN/LOGOFF triggers that can be used to track who logged in, from where and if they changed any data (track COMMITs/rollbacks from Oracle's DMV)

John Kanagaraj
Post #379391
Posted Friday, July 6, 2007 12:36 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, July 11, 2014 3:12 AM
Points: 148, Visits: 80

As i already told , if you use the sp_rename stored procedures these triggers do nothing, nada.

 




Post #379461
Posted Friday, July 6, 2007 8:02 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962

Thank you.  You just answered two questions for me at the same time:

  1. How to force everything to go through this method so that everything is tracked.
  2. How to get around the "lock out" that I discribed.

You restrict the use of sp_rename.  This forces everybody else to use ALTER TABLE, ALTER COLUMN, etc. to make changes.  If your logon still has access to sp_rename, etc. this gives you a back door.



ATB

Charles Kincaid

Post #379590
Posted Thursday, August 21, 2008 10:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2008 10:46 AM
Points: 1, Visits: 10
can Iplease haver the command for trigger and stored procedures and the standard format for querying tables

thank you
Post #556751
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse