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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 error referencing 'inserted' table in trigger
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kaus
Posting Yak Master

179 Posts

Posted - 05/30/2001 :  12:30:53  Show Profile  Reply with Quote
I have sp that inserts data from asp page and a trigger that fires email to inform us of the insert. I'm getting an 'invalid object name' for the inserted table when I try to reference it in the trigger. Am I not correctly using 'inserted' ?? Below is trigger and sp

Trigger
CREATE TRIGGER mail_data_request ON Requests
FOR INSERT
AS
exec master.dbo.xp_sendmail 'pvitt@sawpa.org', @query = 'DataRequests.dbo.sp_data_request_query',
@subject = 'new data request'

Query Stored Proc
CREATE PROCEDURE [sp_data_request_query]
AS
select ins.FirstName + ' ' + ins.LastName + ', ' + ins.title + ' of ' +
ins.Organization as Requestor, ins.request_info as Requests, ins.email as email,
ins.ph_area_code + '-' + ins.ph_prefix + '-' + ins.ph_suffix as phone
from inserted ins

Thanks a lot


nizmaylo
Constraint Violating Yak Guru

USA
258 Posts

Posted - 05/30/2001 :  12:45:02  Show Profile  Reply with Quote
Cut your select statement from the proc, and move it into the body of the trigger. It'll work.

PS You can't reference inserted table in the stored proc.

Helena

helena
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/30/2001 :  12:47:27  Show Profile  Visit nr's Homepage  Reply with Quote
inserted is only available in the trigger not in an sp called from the trigger I think.
The xp_sendmail will create a new (bound) connection (again I think) to send the mail so you will doubly fail.

Go to Top of Page

kaus
Posting Yak Master

179 Posts

Posted - 05/30/2001 :  16:11:19  Show Profile  Reply with Quote
Thanks for both of your responses. I
tried adding the query from sp directly to the trigger and it still didnt work. I also tried using @@identity directly in the trigger but that came up with a null -- As you said the new bound connection must prevent you from using all this stuff -- I looked closer at the xp_sendmail documentation for query and it said -- "The query statement, however, cannot refer to the logical inserted and deleted
tables that are only available within a trigger."

I just used select(max) and it works fine -- we have a site that gets very little usage so it shouldnt be a problem. It would have been nice to use one of the other techniques though !!

Thanks

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/30/2001 :  16:54:15  Show Profile  Visit nr's Homepage  Reply with Quote
You could put the data into a table keyed with the spid - put in the primary key of the record in question.

trigger
delete mailtbl where spid = @@spid
insert mailtbl select @@spid, ....

@query = 'DataRequests.dbo.sp_data_request_query ' + @@spid + primary key

but if you are doing that you might just as well select the data from inserted to put into @query without calling an sp.

n.b. have you thought about what happens if more than one rec is inserted at a time?

Go to Top of Page

kaus
Posting Yak Master

179 Posts

Posted - 05/30/2001 :  17:58:26  Show Profile  Reply with Quote
I'm not sure I understand --
Are you saying I could insert the items from 'selected' into another table and then use this table for my query in xp_sendmail ??

I'm not worried about more than one record being inserted -- the form only allows one record at a time, unless two people insert at the same time ??

Thanks

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000