November 19, 2002 at 8:52 am
Hello - me again....
I'm unable to access the INSERTED or DELETE tables from inside an trigger that calls Master.dbo.xp_sendmail with the @query property set like 'Select * from INSERTED'.
It appears that since xp_sendmail resides in Master, that it cannot resolve back to wherever the INSERTED and DELETED tables may actually live.
Here's what I'm trying to do in ~ code:
CREATE TRIGGER tU_Update_Notify ON [dbo].[SomeTable]
FOR UPDATE
AS
EXEC MASTER.dbo.xp_Sendmail
@Subject='Update Event(s) occured',
@Recipients='MeMyselfAndI',
@Message='A Message',
@Query='SELECT * FROM INSERTED',
@attachments = @FileName,
@ansi_attachment = 'TRUE',
@attach_results = 'TRUE',
@Width = 512
I've also tried to use a TABLE variable to hold the results of a select statement outside of xp_Sendmail but I'm having syntax problems.
Any help is appreciated - B
November 19, 2002 at 9:23 am
OK - this works for the moment.
Only Global temp tables work - not local.
CREATE TRIGGER tU_Update_Notification ON [dbo].[cdAccount]
FOR UPDATE
AS
DECLARE @FileName varchar(30)
SET @FileName = CONVERT(varchar, GetDate(), 102) + '_.txt'
DECLARE @EUser varchar(50)
SET @EUser = User
DECLARE @EMessage varchar(255)
SET @EMessage = 'User ' + @EUser + ' has modified an Account(s) - see attachment.'
-- Prepare the Updated list (Can only use a Global temp table here - needs work!)
SELECT id_Account INTO ##AC_Updates FROM DELETED
EXEC MASTER.dbo.xp_Sendmail
@Subject='Account Update Event(s)',
@Recipients='YourMomma',
@Message=@EMessage,
@Query='SELECT vAC.* FROM HC_Prod01.dbo.vi_Accounts vAC WITH (NOLOCK) RIGHT JOIN ##AC_Updates ON vAC.id_Account = ##AC_Updates.id_Account',
@attachments = @FileName,
@ansi_attachment = 'TRUE',
@attach_results = 'TRUE',
@Width = 512
DROP TABLE ##AC_Updates
A few things to note:
> Using Global temp tables
> The @ansi_attachment is NECESSARY but undocumented - found it in the MS KnowldgeBase.
So - functional - but looking for a better solution.
oops - also - does putting WITH (NOLOCK) on Temp tables or Views actually do anything ??
November 19, 2002 at 9:35 am
no lock shouldn't matter.
I wouldn't do this. Instead, I'd use a process that runs every minute or so and looks for changes and sends mail. Less likely to cause a transaction issue.
Steve Jones
November 20, 2002 at 1:16 pm
ya - thanks Steve.
I'm just inserting into an Evetn table now and sending one EMail at the end of the day with All the events.
I'd still like to know if I "could" get at the Inserted/Deleted tables from xp_sendmail however....
November 20, 2002 at 2:40 pm
inserted and deleted are only available from the scope of the trigger. You can't even access them from an SP called from the trigger.
You either have to use global temp table as you have or a fixed table and specify the database in the query
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 26, 2002 at 6:19 pm
If you use a global temp table,
the object name will show up in the tempdb exactly as ##AC_Updates. This is unlike a non-global temp table. So you need to find a way to uniquely identify the table, so the trigger does not fail when two users trigger the trigger on an update at the same time.
If not, if the second trigger attempts to create the global temp table before the second drops the table, it will fail.
Andrew
What's the business problem you're trying to solve?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply