Troubleshooting A Simple SQL Server Trigger

Question

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 = 'shubniggurath@email.com, someoneelse@email.com',
            @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.

You have to be **in** the `OldJunk` database, and then create the trigger CREATE TRIGGER NotificationMail ON [dbo].[JunkTable]` - you cannot use the three-part `(database).(schema).(object)` notation in the trigger definition — marc_s, Sep 19 '13 at 14:18
I was connected to the schema and had just prior to this run a query in the database in question. When I try to kill the (database).(schema).(object) in favor of (schema).(object), it gives this message: The object 'dbo.JunkTable' does not exist or is invalid for this operation. Whenever I just use (object), I get this: The object 'JunkTable' does not exist or is invalid for this operation. — shubniggurath, Sep 19 '13 at 15:57
I suppose I may not understand what you mean by 'in the OldJunk database' - I'm connected to the schema and am able to run queries on tables - is there more to it than that? — shubniggurath, Sep 19 '13 at 16:15
Also, is this something I shouldn't be attempting in my Management Studio? That is, should I use BIDS instead? — shubniggurath, Sep 19 '13 at 19:53
To "be in" a database means: do a `USE OldJunk; GO` in SQL Server Mgmt Studio, and then do `CREATE TRIGGER NotificationMail ON dbo.JunkTable......` or whatever you're table is called. 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? — marc_s, Sep 19 '13 at 20:20
Oh yeah, the use statement. Thanks thanks thanks. It worked!!!! If you use this as an answer I'll mark it as correct/upvote. — shubniggurath, Sep 19 '13 at 20:28

marc_s · Accepted Answer · 2013-09-19T20:41:46.400

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?

Troubleshooting A Simple SQL Server Trigger

1 Answers1