Search code examples
sqlsql-server-2008triggerssqlmail

Troubleshooting A Simple SQL Server Trigger

I'm using SQL Server 2008 here. I inherited an old web app that is dying, and being replaced by a totally new web app. The new project is up and running but the old one will exist for the next month and a half for the transition period.

Here's the problem: action needs to be taken when someone adds a new record to a table in SQL Server using this app. The old source code is pretty hosed (seriously, no version control before my arrival) and I can't afford to take the time to hobble something together just so I can get an email notification using the old app.

My thought - use a SQL Server trigger to send an email AFTER INSERT. Really this is all I want: whenever a new record (and it's always one, not dozens) is entered into a table, I want to send myself and another lucky person an email. I've never done this in SQL Server but it seems doable.

Here's my SQL script as it currently stands:

CREATE TRIGGER NotificationMail 
ON OldJunk.[dbo].[JunkTable]
AFTER INSERT
AS
BEGIN
   EXEC msdb.dbo.sp_send_dbmail --QUESTION: I HAVE NO IDEA WHAT TO PUT HERE, WHAT FOLLOWS
                                -- IS JUST COPYPASTA FROM A FORUM
            @recipients = '[email protected], [email protected]',
            @subject = 'Old Registration Request - New Record',
            @body = 'Somebody decided to register using the old system.'
END            
GO

I'm getting this error when I try to execute this create statement:

Cannot create trigger on 'OldJunk.dbo.JunkTable' as the target is not in the current database.

Thanks in advance for your help.

Solution

  • You have to be in the OldJunk database (by using the USE .... command in SQL Server Management Studio), and then create the trigger using these SQL statements:

    USE OldJunk; 
GO 

CREATE TRIGGER NotificationMail ON [dbo].[JunkTable]
.....

    You cannot use the three-part (database).(schema).(object) notation in the trigger definition.

    If that doesn't work - then you probably don't have such a table - is there a typo? Or is this not really a table?