Post reply

Another Trigger question...

  • November 23, 2011 at 9:34 am

    #263381

    SQL Server 2005

    I have created a Create Database Trigger like this:

    Create TRIGGER [Create_Database_Trigger_DBADistributed]

    ON ALL Server

    WITH EXECUTE AS 'sa'

    FOR Create_Database -- Captures a Create Database Event

    AS

    DECLARE

    @EventType varchar(128),

    @PostTime datetime,

    @SPID int,

    @ServerName varchar(128),

    @LoginName varchar(128),

    @DatabaseName varchar(128),

    @TSQLCommand varchar(128),

    @mySubject varchar(300),

    @msg XML

    SET @msg = EVENTDATA()

    --load our values to variables in case we need them

    SELECT

    @EventType = @msg.value('(/EVENT_INSTANCE/EventType)[1]' ,'varchar(128)' ),

    @PostTime = @msg.value('(/EVENT_INSTANCE/PostTime)[1]' ,'datetime' ),

    @SPID = @msg.value('(/EVENT_INSTANCE/SPID)[1]' ,'int' ),

    @ServerName = @msg.value('(/EVENT_INSTANCE/ServerName)[1]' ,'varchar(128)' ),

    @LoginName = @msg.value('(/EVENT_INSTANCE/LoginName)[1]' ,'varchar(128)' ),

    @DatabaseName = @msg.value('(/EVENT_INSTANCE/DatabaseName)[1]' ,'varchar(128)' ),

    @TSQLCommand = @msg.value('(/EVENT_INSTANCE/TSQLCommand)[1]' ,'varchar(128)' )

    -- Print the message to the user

    SET @mySubject = 'Database ' + @DatabaseName + ' Created on ' + @@SERVERNAME + '';

    PRINT @mySubject

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'DBADistributed'

    , @recipients = 'DBADistributed@email.org'

    , @subject = @mySubject

    , @body = 'Database must be Added to the NetBackup Backup Scripts on KOCSQLDEV02'

    , @importance = 'high';

    My question is.. how can I query the system to find out if this is the only Server Level trigger running?

  • November 23, 2011 at 12:37 pm

    #1412425

    The following will show you a listing for all DDL triggers on your dB, including Database and Server-level triggers:

    SELECT * FROM sys.triggers WHERE parent_class=0

  • November 23, 2011 at 12:56 pm

    #1412440

    I create the Trigger (see above)

    and then run SELECT * FROM sys.triggers WHERE parent_class=0 against the master DB

    it returns 0 rows?

  • November 23, 2011 at 1:07 pm

    #1412446

    I think you want sys.server_triggers

    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

  • November 23, 2011 at 1:28 pm

    #1412457

    SELECT * FROM sys.server_triggers WHERE parent_class=0

    returns 0 rows as well.... bizzarre!!!!!!!!!!! anyone?

  • November 23, 2011 at 1:32 pm

    #1412459

    Remove the where clause and run

    SELECT * FROM sys.server_triggers

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

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