Post reply

Automate mail when a column is added

  • December 26, 2011 at 4:51 am

    #265444

    Hi,

    How can I automate a mail to be generated if a column is added to a table in my database. Is this possible?

    Regards,

    Nithin

  • December 26, 2011 at 6:31 am

    #1425487

    I figured it out

    USE Test;

    GO

    CREATE TRIGGER ColumnChanges

    ON DATABASE

    FOR ALTER_TABLE

    AS

    -- Detect whether a column was created/altered/dropped.

    DECLARE @Message varchar(200);

    DECLARE @Message1 varchar(100);

    DECLARE @Message2 varchar(100);

    SET @Message1='The folowing Changes have been applied to the database : '

    SET @Message2=(SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)'))

    SET @Message=@Message1+@Message2

    RAISERROR ('Table schema has been modified', 16, 1);

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'Admin',

    @recipients = 'nithin@gmail.com;',

    @body = @Message,

    @subject = 'Database Schema Change' ;

  • December 26, 2011 at 6:59 am

    #1425490

    My other requirement is:

    I am inserting values into my table test.

    The attributes are:

    col1

    col2

    status

    Now when inserting if I insert 3 into status, i need to generate a trigger which will send mail to the recipient stating the insertion of 3into the status column. I want to generate mail only if 3 is inserted into the status column.

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

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