Triggers, Sp_Mail, and Newbies

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 Development (2000)

Triggers, Sp_Mail, and Newbies

Author

Topic

Jmotley
Starting Member

2 Posts

Posted - 2002-07-10 : 11:25:32

I am at that "Know just enough to be dangerous mode"

Here is my question.

I have created an ASP form that populates a MSSQL 2000 database.

I would like to be notified each time a new record has been added. So I guess I should create a trigger and as part of the trigger have it execute xp_sendmail command and send me a basic message like "A new record has been added."

But other then knowing how to create a basic trigger, I am looking for the code that would go in the trigger.

Thanks --

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-07-10 : 11:31:16

This should do it:

CREATE TRIGGER reminder
ON titles
FOR INSERT
AS
EXEC master..xp_sendmail 'Jmotley', 'A new record was inserted'
GO

Jmotley
Starting Member

2 Posts

Posted - 2002-07-10 : 13:16:05

Worked like a champ !! Thanks Yellow Bug --
** If you are a real newbie, I change the code example slightly to make it even easier to understand.

CREATE TRIGGER Trigger_Name
ON Table_Name
FOR INSERT
AS
EXEC master..xp_sendmail 'Username@domain.com', 'A new record was inserted'
GO

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-10 : 17:17:26

quote:
EXEC master..xp_sendmail 'Jmotley', 'A new record was inserted'

In SQL7.0 that wouldn't work... Triggers were unable to go accross databases. Is this a new featureof 2000?

to get around it I had to use

CREATE TRIGGER reminder
ON titles
FOR INSERT
AS
exec stored_proc_send_mail
GO
create stored_proc_send_mail
as
EXEC master..xp_sendmail 'Jmotley', 'A new record was inserted'

-----------------------
Take my advice, I dare ya

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-07-11 : 05:05:50

From BOL 2000:
"A trigger is created only in the current database; however, a trigger can reference objects outside the current database. "

ToddV
Posting Yak Master

218 Posts

Posted - 2002-07-11 : 09:16:08

I think is a bad idea. Putting this is a trigger creates a dependency that sqlmail finishes before the insert commits. I am not that confident in SqlMail (or any shelled out task). I would create a job. The job would find new records and send an email. You would have to include some sort of mechanism to recognize new records. Then you could start the job from your trigger (I might use a custom error message and an alert to do this) or schedule the job.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-11 : 09:24:46

Something like this would work:

http://www.sqlteam.com/item.asp?ItemID=5908

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-11 : 09:54:50

Heh sweet. Another reason for me to move to 2000... Triggers in 7.0 have always pissed me off with those limitations

-----------------------
Take my advice, I dare ya

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-07-11 : 16:15:30

M.E. I don't want to dissuade you from upgrading to SQL 2000, but my BOL for 7.0 has that same statement about triggers being able to reference objects outside of the current database... It's on the CREATE TRIGGER entry, under the Trigger Limitations list.

Mind you, I don't have a SQL 7 database to test it on, but that's what BOL says.

Edited by - AjarnMark on 07/11/2002 16:16:03

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-15 : 09:52:06

Hmmmmm.. Strange. Time to go back to the books I guess. Not sure where I got that from now. Thnx Ajarn

-----------------------
Take my advice, I dare ya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources