Instead of Insert trigger help 1

[sparkbyte]

I am having problems figuring out the propper syntax for this ...

This is on SQL Server 2005

USE [MailroomTracking_DEV]
GO
/****** Object:  Trigger [dbo].[uitblTrackingTable_Test]    Script Date: 01/25/2011 11:34:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tblTrackingTable_Test_InsteadOfInsert]
   ON  [dbo].[tblTrackingTable_Test]
   INSTEAD OF INSERT
AS 
BEGIN
	SET NOCOUNT ON;

	DECLARE @EmployeeID VarChar(10) -- Users Network Logon ID to Domain
		Set @EmployeeID = (Select cast(Items As VarChar(10)) As LoginID from fn_Split(SUSER_SNAME(),'\') Where ID = 2)
    DECLARE @TrackingDate DateTime
		Set @TrackingDate = GetDate()
    DECLARE @LogonName varchar(10) -- Logged on user interting the new record
		Set @LogonName = SUSER_SNAME()
	DECLARE @MachineName  VarChar(15) -- Workstation Network ID
		Set @MachineName = Host_Name()
	DECLARE @FCO_PK VarChar(3) -- FCO 3 digit Code
		Set	@FCO_PK = (Select FCO_PK from dbo.tblFCO where FCO_Active = 1) 


	Insert Into dbo.tblTrackingTable_Test
		(
			EmployeeID, 
			MachineName, 
			BoxNumber, 
			FileNumber, 
			TrackingDate,
			ModifiedDate,
			ModifiedBy,
			FCO_PK
		)
	VALUES
		(
			@EmployeeID,
			@MachineName,
			BoxNumber, 
			FileNumber, 
			TrackingDate,
			@ModifiedDate,
			@ModifiedBy,
			@FCO_PK
		)
	From dbo.tblTrackingTable_Test a
		Inner Join Inserted As i
            On a.Tracking_ID = i.Tracking_ID
END

The error message is

[red]
Msg 128, Level 15, State 1, Procedure tblTrackingTable_Test_InsteadOfInsert, Line 35
The name "BoxNumber" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
[/red]

Thanks

John Fuhrman

 

[gmmastros]

maybe this:

USE [MailroomTracking_DEV]
GO
/****** Object:  Trigger [dbo].[uitblTrackingTable_Test]    Script Date: 01/25/2011 11:34:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tblTrackingTable_Test_InsteadOfInsert]
   ON  [dbo].[tblTrackingTable_Test]
   INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @EmployeeID VarChar(10) -- Users Network Logon ID to Domain
        Set @EmployeeID = (Select cast(Items As VarChar(10)) As LoginID from fn_Split(SUSER_SNAME(),'\') Where ID = 2)
    DECLARE @TrackingDate DateTime
        Set @TrackingDate = GetDate()
    DECLARE @LogonName varchar(10) -- Logged on user interting the new record
        Set @LogonName = SUSER_SNAME()
    DECLARE @MachineName  VarChar(15) -- Workstation Network ID
        Set @MachineName = Host_Name()
    DECLARE @FCO_PK VarChar(3) -- FCO 3 digit Code
        Set    @FCO_PK = (Select FCO_PK from dbo.tblFCO where FCO_Active = 1) 


    Insert Into dbo.tblTrackingTable_Test
        (
            EmployeeID, 
            MachineName, 
            BoxNumber, 
            FileNumber, 
            TrackingDate,
            ModifiedDate,
            ModifiedBy,
            FCO_PK
        )
    [!]Select[/!]
            @EmployeeID,
            @MachineName,
            [!]i.[/!]BoxNumber, 
            [!]i.[/!]FileNumber, 
            [!]i.[/!]TrackingDate,
            @ModifiedDate,
            @ModifiedBy,
            @FCO_PK
    From dbo.tblTrackingTable_Test 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]

That fixed it.

Now I have to add the verification processes.

Thanks!!!

Thanks

John Fuhrman