Help with after update trigger 1

[sparkbyte]

I can't seem to add the filenumber case to it successfully.

USE [MailroomTracking]
GO
/****** Object:  Trigger [dbo].[uTrackingTable]    Script Date: 12/01/2010 16:44:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[uTrackingTable]
   ON  [dbo].[tblTrackingTable]
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;

	Update dbo.tblTrackingTable
		Set ModifiedDate = getdate(),
		-- Make all File Numbers 9 digit
		[highlight]FileNumber = case 
			when ascii(substring(FileNumber,3,1))> 65 then FileNumber 
			when ascii(substring(FileNumber,2,1))> 65 then 'Second char LETTER'
			when len(FileNumber)<10 then left(FileNumber,1) + replicate('0', 10-len(FileNumber)) 
				+ right(FileNumber, len(FileNumber)-1) --pad with zeroes
		end[/highlight]
	From dbo.tblTrackingTable a
		Inner Join Inserted As i
            On a.Tracking_ID = i.Tracking_ID
END

Message:
[red]
Msg 209, Level 16, State 1, Procedure uTrackingTable, Line 12
Ambiguous column name 'FileNumber'.
Msg 209, Level 16, State 1, Procedure uTrackingTable, Line 13
Ambiguous column name 'FileNumber'.
Msg 209, Level 16, State 1, Procedure uTrackingTable, Line 14
Ambiguous column name 'FileNumber'.
Msg 209, Level 16, State 1, Procedure uTrackingTable, Line 12
Ambiguous column name 'FileNumber'.
Msg 209, Level 16, State 1, Procedure uTrackingTable, Line 14
Ambiguous column name 'FileNumber'.
Msg 209, Level 16, State 1, Procedure uTrackingTable, Line 14
Ambiguous column name 'FileNumber'.
Msg 209, Level 16, State 1, Procedure uTrackingTable, Line 15
Ambiguous column name 'FileNumber'.
Msg 209, Level 16, State 1, Procedure uTrackingTable, Line 15
Ambiguous column name 'FileNumber'.
[/red]

Thanks

John Fuhrman

 

[gmmastros]

You're getting "Ambiguous column name" because FileNumber is a column in both of your tables (tblTrackingTable and Inserted). Put the table alias in there to fix the problem.

        [!]a.[/!]FileNumber = case 
            when ascii(substring([!]i.[/!]FileNumber,3,1))> 65 then [!]i.[/!]FileNumber 
            when ascii(substring([!]i.[/!]FileNumber,2,1))> 65 then 'Second char LETTER'
            when len([!]i.[/!]FileNumber)<10 then left([!]i.[/!]FileNumber,1) + replicate('0', 10-len([!]i.[/!]FileNumber)) 
                + right([!]i.[/!]FileNumber, len([!]i.[/!]FileNumber)-1) --pad with zeroes
        end

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Man I'm getting tired. I should have seen that. Thanks!!!

Thanks

John Fuhrman

 

[sparkbyte]

thanks

This did it.

BEGIN
	SET NOCOUNT ON;

	Update dbo.tblTrackingTable
		Set dbo.tblTrackingTable.ModifiedDate = getdate(),
		-- Make all File Numbers 9 digit
		dbo.tblTrackingTable.FileNumber = case 
			when ascii(substring(i.FileNumber,3,1))> 65 then i.FileNumber 
			when ascii(substring(i.FileNumber,2,1))> 65 then 'Second char LETTER'
			when len(i.FileNumber)<10 then left(i.FileNumber,1) + replicate('0', 10-len(i.FileNumber)) 
				+ right(i.FileNumber, len(i.FileNumber)-1) 
		end
	From dbo.tblTrackingTable a
		Inner Join Inserted As i
            On a.Tracking_ID = i.Tracking_ID
END

for some reason using the alias 'a' did not work, but using the qualified name did.

Thanks

John Fuhrman

 

[gmmastros]

weird. I guess it's because you didn't update the 'alias'.

BEGIN
    SET NOCOUNT ON;

    Update [!]a[/!] --dbo.tblTrackingTable
        Set [!]a[/!].ModifiedDate = getdate(),
        -- Make all File Numbers 9 digit
        [!]a[/!].FileNumber = case 
            when ascii(substring(i.FileNumber,3,1))> 65 then i.FileNumber 
            when ascii(substring(i.FileNumber,2,1))> 65 then 'Second char LETTER'
            when len(i.FileNumber)<10 then left(i.FileNumber,1) + replicate('0', 10-len(i.FileNumber)) 
                + right(i.FileNumber, len(i.FileNumber)-1) 
        end
    From dbo.tblTrackingTable a
        Inner Join Inserted As i
            On a.Tracking_ID = i.Tracking_ID
END

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Have to check that out.

Thanks

John Fuhrman