troubleshooting Question

Invalid column name 'filename' in trigger

Avatar of Phosphor
Phosphor asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
71 Comments1 Solution1546 ViewsLast Modified:
Hello Experts,
An on-inserted trigger:
I can't get past this error ("Invalid column name 'filename'.") when I run the alter trigger, but not if I parse it. One column's name in the inserted selects is "filename".

Just using [brackets], column alias: (SET @fil = (SELECT filename as f FROM inserted) or mutipart identifier: "fm_faxin_att.filename" still will not work.
Any ideas?

Thanks
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		TCG
-- Create date: 10/17/09
-- Mod date:	08/03/11
-- Description:	testMail Trigger
-- =============================================
ALTER TRIGGER [trgFaxinTest] 
   ON  [dbo].[fm_faxin] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
DECLARE @date datetime
DECLARE @routed_to nvarchar(255)
DECLARE @remote_id nvarchar(255)
DECLARE @result nvarchar(255)
DECLARE @fil nvarchar(255)
DECLARE @pages_transferred int
 
SET @date = (SELECT date FROM inserted)
SET @routed_to = (SELECT routed_to FROM inserted)
SET @fil = (SELECT filename FROM inserted)
SET @remote_id = (SELECT remote_id FROM inserted)
SET @result = (SELECT result FROM inserted) 
SET @pages_transferred = (SELECT pages_transferred FROM inserted)
--SET @id = (SELECT id FROM inserted)
WAITFOR DELAY '00:00:02'
IF @routed_to = 'scanning@example.com' --AND @remote_id = '555-555-1234'
  BEGIN
SELECT     fm_faxin_att.filename, fm_faxin.date, fm_faxin.remote_id, fm_faxin.result
FROM         fm_faxin INNER JOIN
                      fm_faxin_att ON fm_faxin.id = fm_faxin_att.id RIGHT OUTER JOIN
                      inserted ON inserted.id = fm_faxin_att.id
	DECLARE @sub varchar(255)
	DECLARE @msg varchar(MAX)
	SET @sub = 'New VIEW FAX notification --- '  + @remote_id + ' '   + @fil + ' ---- '
    SET @msg = 'NEW  -- We received your fax at ' + Cast(@date as varchar (35)) + '. It was ' + CAST(@pages_transferred as varchar(10)) + ' pages' + char(10) + char(13)+ 
'You can view the fax by following this link: https://www.example.com/ibfax/' + @fil
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 71 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 71 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros