Trigger errors(New) - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Trigger errors(New)

Author

Topic

lairpr74
Starting Member

8 Posts

Posted - 2008-05-19 : 15:20:09

Hi,

I am new to triggers in sql and I am trying to make this one to work.
CREATE TRIGGER NewRequestMailTrigger
ON form_tbl
AFTER INSERT

AS
Declare @body varchar(2000),
Declare @intDate datetime,
Declare @Name varchar(60)

BEGIN
SELECT intDate, Name, nbr, Org, RepEmail, RepName, ContactTel
FROM form_tbl

SET @body = 'Date: "' + @intDate + '" Name:' + @Name + '.'

--xp_sendmail sproc used to send the email
EXEC master..xp_sendmail
@recipients = 'myemail@myemail.com',
@subject = 'New Request',
@message = @body
END
GO

I keep getting this errors when trying to execute the trigger.

Msg 156, Level 15, State 1, Procedure NewRequestMailTrigger, Line 11
Incorrect syntax near the keyword 'Declare'.
Msg 156, Level 15, State 1, Procedure NewRequestMailTrigger, Line 12
Incorrect syntax near the keyword 'Declare'.

Any help will be very appreciated.

Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-19 : 15:23:11

You have extra commas on your DECLARE statements. Try:

Declare @body varchar(2000)
Declare @intDate datetime
Declare @Name varchar(60)

-- OR

Declare @body varchar(2000),
	@intDate datetime,
	@Name varchar(60)

lairpr74
Starting Member

8 Posts

Posted - 2008-05-19 : 15:25:58

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-19 : 15:28:27

As the error states, there are two errors. One at line 11 and one at line 12.
If you look closely at the end of these two line, what can you see?
What is there at the end of these two line that shouldn't be there?

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-19 : 15:30:07

Since you are new to triggers, you should know that this is a bad idea. To email based upon data, most people instead do this through a job that runs every minute. Have the data to be emailed go into a table and then the job would check this table to see if anything needs to get sent out.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

lairpr74
Starting Member

8 Posts

Posted - 2008-05-19 : 16:20:45

Oh, I didn't know that. A friend suggest me to do a trigger since the server does not have the smtp client configure. I got a form that the users will be entering the data and this data should get to a person to verify it or at least he needs to get a notification. I can still create a form where he can look at the data after receiving a notification.

I am going to look on how to create a Job.

Thanks.

quote:
Originally posted by tkizer

Since you are new to triggers, you should know that this is a bad idea. To email based upon data, most people instead do this through a job that runs every minute. Have the data to be emailed go into a table and then the job would check this table to see if anything needs to get sent out.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

lairpr74
Starting Member

8 Posts

Posted - 2008-05-19 : 16:22:48

Yes I found where the problem was. I've been working with these a few hours and I can't see clearly the errors.

Thanks.

quote:
Originally posted by Peso

As the error states, there are two errors. One at line 11 and one at line 12.
If you look closely at the end of these two line, what can you see?
What is there at the end of these two line that shouldn't be there?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources