TRIGGER: Create Table error? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

TRIGGER: Create Table error?

Author

Topic

kirkeby
Yak Posting Veteran

57 Posts

Posted - 2002-09-04 : 13:01:37

I have created the below trigger. When executed from QA it runs fine, sending the email, etc. When executed as a trigger off the ReqCommon table I get the following error:

The CREATE TABLE statement is not allowed within a trigger.

What is it about this trigger that is causing the problem? BTW, the sproc being used to send the email does indeed send in HTML format.

Thanks for any help!

CREATE TRIGGER Survey on dbo.ReqCommon
FOR UPDATE
AS
set nocount on
DECLARE @RequesterEmail varchar(60)
DECLARE @RequesterName varchar(60)
DECLARE @RequestID int
DECLARE @RequestType char(1)
DECLARE @ReqType varchar(10)
DECLARE @CompletedDate datetime
DECLARE @Status int
DECLARE @msg varchar(8000)
DECLARE @AllRecipients varchar(500)

IF EXISTS(SELECT Request_ID FROM inserted)
BEGIN
SET @RequesterEmail = (SELECT Requester_Email FROM inserted)
SET @RequesterName = (SELECT Requester_Name FROM inserted)
SET @RequestID = (SELECT Request_ID FROM inserted)
SET @CompletedDate = (SELECT Completed_Date FROM inserted)
SET @RequestType = (SELECT Request_Type FROM inserted)
SET @Status = (SELECT Current_Status_ID FROM inserted)
SET @AllRecipients = @RequesterEmail-- + ';' + 'vinsonsm@bp.com'
IF @RequestType = 'S' BEGIN SET @ReqType = 'Scripting' END
IF @RequestType = 'T' BEGIN SET @ReqType = 'Testing' END
SET @msg = '<html><body><FORM name="frmSurvey" action="http://dboas.bpweb.bp.com/email/Email.asp">
<a href="http://dboas.bpweb.bp.com"><img src="http://dboas.bpweb.bp.com/images/DBOASLogo.gif" border="0"></a>
Request ID: ' + CAST(@RequestID as varchar(10)) + ' Requester Name: ' + @RequesterName + ' 
 Your request has been completed by the DBO Application Scripting team.What was your overall experience regarding Scripting?
<input type="radio" name="scripting" value="1" tabindex="1"> Service Not Associated with my request <input type="radio" name="scripting" value="2" tabindex="2"> Poor <input type="radio" name="scripting" value="3" tabindex="3"> Fair <input type="radio" name="scripting" value="4" tabindex="4"> Good <input type="radio" name="scripting" value="5" tabindex="5"> Excellent!
Comments:
<textarea name="comments" rows="5" cols="80" tabindex="16"></textarea>
<input type="submit" name="submit" value="Submit" tabindex="17"> </form></body></html>'
IF @CompletedDate IS NOT NULL AND @RequestType = 'S' OR @RequestType = 'T' AND @Status = 8 OR @Status = 11 OR @Status = 12 or @Status = 16
BEGIN
EXEC master..sp_sendSMTPmail @RequesterEmail, 'Your request has been completed by DBO Application Scripting', @msg, @Importance=2, @HTMLFormat=1
END
END

Lisa Kirkeby
BP
kirkeblm@bp.com
SQL 7.0, NT 4.0 (1381), CP 1252

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-04 : 13:10:01

look at master..sp_sendSMTPmail
It probably has a create table #... statement in it which is invalid.

You may be able to round it be creating a new version with a select into instead. (works on at least some versions).
I wouldn't advise it because it's probably a bug that it;s allowed.

Better to put your request into a table then have a scheduled task that sends the emails (then you can still run the application if the email service is down.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kirkeby
Yak Posting Veteran

57 Posts

Posted - 2002-09-04 : 14:36:27

Thank you! Your suggestion to dump it into a table and use a task (I'm using a SQL job) is working great! Thank you!

Lisa Kirkeby
BP
kirkeblm@bp.com
SQL 7.0, NT 4.0 (1381), CP 1252

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources