Post reply

Db level trigger

  • January 25, 2010 at 1:03 pm

    #219851

    I have abot 20 tables in the db. and when ever a column is changed, 2 fields (whcih are avail in all tables) need to be updated. one is Date and the other is Username. So, i am a bit confused as in how to use db level trigger.

    CREATE TRIGGER TrgA_UpdateDateAndUserInTable

    On DATABASE ABC

    After

    AS

    ....

    i am not sure what to mention after 'AS', what will be the tsql command that i need to specify. do i need to specify the table name? or the 2 columns that i need to update?

    Noli Timere

  • January 25, 2010 at 1:20 pm

    #1108363

    You can't actually do that.

    DML triggers, the type that fire after data changes, are per-table and have to be created on a table.

    The database-level triggers are DDL triggers, they fire for database structure changes, not for data changes.

    An example of a database level trigger would be this:

    CREATE TRIGGER [trg_AuditSchemaChanges]

    ON DATABASE

    FOR DDL_DATABASE_LEVEL_EVENTS

    AS

    SET NOCOUNT ON

    DECLARE @data XML

    SET @data = EVENTDATA()

    IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)') NOT LIKE '%statistics%'

    INSERT testing.dbo.AuditSchemaChanges (DatabaseName, ObjectName, ObjectType, EventDate, EventType, UserName,FullCommand)

    VALUES (DB_Name(),

    @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(128)'),

    @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(50)'),

    GetDate(),

    @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)'),

    original_login(),

    @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')

    )

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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

  • January 25, 2010 at 1:53 pm

    #1108381

    thanks. it makes more sense now. 🙂

    Noli Timere

Viewing 3 posts - 1 through 2 (of 2 total)

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