TRIGGER AFTER DELETE question

  • I have a delete trigger but I want to email me the username field value that has been deleted. Is this possible - if so how?

    Sample code:

    CREATE TRIGGER delUsers

    ON db_users

    AFTER DELETE

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    EXECUTE

    MSDB.dbo.sp_send_dbmail @profile_name = 'Mail Profile 1'

    , @recipients = 'me@email.co.uk'

    , @copy_recipients = ''

    , @blind_copy_recipients = ''

    , @SUBJECT = 'User Deleted'

    , @body = 'User Deleted'

    , @body_format = 'HTML'

    , @importance = 'Normal'

    , @sensitivity = 'Normal'

    END

    GO

  • CREATE TRIGGER delUsers ON db_users AFTER DELETE

    AS

    BEGIN

    SET NOCOUNT ON

    Declare

    @User_name varchar(100),

    @body varchar(100)

    Select @User_name=usrname from deleted

    Set @body='User ' +@User_name+ ' Deleted'

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'Mail Profile 1',

    @recipients = 'me@email.co.uk',

    @body = @body,

    @subject ='User Deletion'

    END

    --Where usrname is the field in your table db_users

    ..

  • Thanks a million - works great!

  • Matt Toynbee (5/6/2009)


    Thanks a million - works great!

    Actually it only works great if you have single row delete. If you have a batch delete occur you will only get one email sent out and you cannot be guaranteed which row will be the one you alerted about.

    For example if I run this code:

    Delete

    From

    db_users

    Where

    UserName Like 'A%'

    You will get only 1 email.

    In my opinion, you really should stage the deleted data in a table and then have a job that reads that table and sends you an email or multiple emails. Or you could use SSRS and create an email subscription that sends you an email with deleted users in the last N minutes/hours.

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

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