Send email when new data entered into table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Send email when new data entered into table

Author

Topic

kirkeby
Yak Posting Veteran

57 Posts

Posted - 2001-06-05 : 16:41:02

I tried your suggestion in the topic entitled "Email when data inserted into table" but it's not working for me. Here's my trigger:

CREATE TRIGGER ConfirmIt
ON ReqCommon
FOR INSERT
AS
DECLARE @RequesterName varchar(60),
@RequesterEmail varchar(60),
@RequestID int,
@Time datetime,
@msg varchar(100);

IF EXISTS (SELECT Request_ID FROM ReqCommon WHERE Current_Status_ID = 7)
BEGIN
SET @RequesterName = (SELECT Requester_Name FROM ReqCommon)
SET @RequesterEmail = (SELECT Requester_Email FROM ReqCommon)
SET @RequestID = (SELECT Request_ID FROM ReqCommon)
SET @Time = (SELECT Request_Date FROM ReqCommon)
SET @msg = @RequesterName + @RequestID + cast(@Time as varchar(32))
exec master.dbo.xp_sendmail @Recipients = @RequesterEmail, @subject = 'Your request has been received', @message = @msg, @dbuse='Staging'
end

In Query Analyzer, here's the error I receive:

Server: Msg 512, Level 16, State 1, Line 0
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I get this error also for lines 10, 11 and 12, and of course, an error telling me the mail session was not started.

I'm stumped. Also, how do you enable and disable triggers? Thanks!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources