Instead of Insert trigger

[sparkbyte]

This is test I have been working with but can't seem figure out what is wrong. I am trying to set variables that then be used to varify the data.

USE [MailroomTracking_DEV]
GO
/****** Object:  Trigger [dbo].[IO_TRIG_Insert_tblTrackingTable_Test]    Script Date: 01/27/2011 16:41:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[IO_TRIG_Insert_tblTrackingTable_Test]
   ON  [dbo].[tblTrackingTable_Test]
   Instead of Insert
AS 
BEGIN
	SET NOCOUNT ON
	

DECLARE @FileNumber varchar(15)
DECLARE @BoxNumber varchar(50)
DECLARE @TrackingDate datetime
DECLARE @Message varchar(250)

-- TODO: Set parameter values here.
Set @FileNumber = (Select FileNumber from Inserted)
Set @BoxNumber = (Select BoxNumber from Inserted)
Set @TrackingDate = (Select TrackingDate from Inserted)

Set @Message = @FileNumber + ' / ' + @BoxNumber + ' / ' + Cast(@TrackingDate as Varchar(15))

RAISERROR (@Message,16,1)

--	RAISERROR ('Deletions not allowed from this table (source = instead of)', 16, 1)

--EXECUTE [MailroomTracking_DEV].[dbo].[usp_TrackingInput_v6] 
--   @FileNumber
--  ,@BoxNumber
--  ,@TrackingDate


END

This is the Insert

INSERT INTO [MailroomTracking_DEV].[dbo].[tblTrackingTable_Test]
(
            [BoxNumber]
           ,[FileNumber]
)
     VALUES
           ('nbc','a11111111')

And the error message

[red]
Msg 50000, Level 16, State 1, Procedure IO_TRIG_Insert_tblTrackingTable_Test, Line 21
[/red]

And just in case.

This is the table being inserted into

CREATE TABLE [dbo].[tblTrackingTable_Test](
	[Tracking_ID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeID] [varchar](50) NULL,
	[MachineName] [varchar](20) NULL,
	[BoxNumber] [varchar](45) NOT NULL,
	[FileNumber] [varchar](25) NOT NULL,
	[TrackingDate] [datetime] NULL,
	[Reship] [bit] NULL,
	[BoxNumberOriginal] [varchar](50) NULL,
	[RowGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_tblTrackingTable_Test_RowGUID]  DEFAULT (newid()),
	[RowDeleted] [bit] NULL CONSTRAINT [DF_tblTrackingTable_Test_RowDeleted]  DEFAULT ((0)),
	[ModifiedDate] [datetime] NULL,
	[ModifiedBy] [varchar](25) NULL,
	[TrackingYear]  AS (datepart(year,[TrackingDate])),
	[TrackingMonth]  AS (datepart(month,[TrackingDate])),
	[TrackingDay]  AS (datepart(day,[TrackingDate])),
	[FCO_PK] [int] NULL
) ON [PRIMARY]

Thanks

John Fuhrman

 

[SajidAttar]

I think insert statement missing TrackingDate, hence the variable @TrackingDate has null value.

When you concatenate values for @Message variable, it becomes null. Try changing variable assignment for @Message like this.

Set @Message = @FileNumber + ' / ' + @BoxNumber + ' / ' + Cast(ISNULL(@TrackingDate,GETDATE()) as Varchar(15))

Regards,


"Dream not what makes your sleep a pleasure, but what makes you work
hard to achieve it and forget your sleep (untill you achieve it)." -- SJD

 

[RiverGuy]

You are raising an error no matter what.

Also, you want to redesign your trigger. It's bad practice to create local variables and set them equal to something from inserted. Insert statements don't always insert a single row. Remove your variable declarations and remove the call to your stored procedure (if you were going to uncomment that). Replace it with logic similar to that contained within your stored procedure which operates on the entire inserted set.

 

[sparkbyte]

RiverGuy, the original intention was to covert the SP to an Instead of Insert Trigger on a view of the table but the SP is larger than any trigger I have ever built and I am having problems with figuring out how to rewrite the uSP into a trigger.

Here is the uSP.

USE [MailroomTracking_DEV]
GO
/****** Object:  StoredProcedure [dbo].[usp_TrackingInput_v6]    Script Date: 01/31/2011 10:24:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_TrackingInput_v6]
    -- Add the parameters for the stored procedure here
    @FileNumber VarChar(15) = Null,
    @BoxNumber VarChar(50) = Null,
    @TrackingDate DateTime = Null

/******************************************************
 Custom File Number error messages. (505XX)
*******************************************************/

--sp_dropmessage @msgnum = 50500
--go
--sp_dropmessage @msgnum = 50501
--
--sp_addmessage @msgnum = 50500,
--	@severity = 16,
--	@msgtext = N'The File Number you have entered does not meet existing criteria and will not be Accepted.'
--
--Go
--
--sp_addmessage @msgnum = 50501,
--	@severity = 16,
--	@msgtext = N'The File Number you have entered does not begin with a correct PREFIX and will not be Accepted.'
--
--Go
/******************************************************
 Custom Tracking Number error messages. (506XX)
*******************************************************/
--sp_dropmessage @msgnum = 50600
--go
--sp_dropmessage @msgnum = 50601
--go
--sp_dropmessage @msgnum = 50602

--sp_addmessage @msgnum = 50600,
--	@severity = 16,
--	@msgtext = N'The Tracking Number entered does not meet existing criteria and will not be Accepted.'
--Go
--sp_addmessage @msgnum = 50601,
--	@severity = 16,
--	@msgtext = N'The Tracking Number you have entered does not begin with a correct PREFIX and will not be Accepted.'
--Go
--sp_addmessage @msgnum = 50602,
--	@severity = 16,
--	@msgtext = N'The Tracking Number you have entered is not a valid UPS Tracking Number.'
--Go



AS
BEGIN
    SET NOCOUNT ON;

/******************************************************
 Tracking Number Validataion
--*******************************************************/

	If Len(@BoxNumber) = 3 
		Begin
			If Not Exists(Select 1 From dbo.tblFCO Where FCO_Code = @BoxNumber)
				  BEGIN
--					Print N'check 1'
					RAISERROR(50601,16,1)
					RETURN
				  END
			Else 
				Begin
						Set @BoxNumber = @BoxNumber
								+ Cast(Cast(RAND(CHECKSUM(NEWID())) / .0000000000001 As BigInt) As VarChar(13)) 
						  + '-' + Cast(Cast(RAND(CHECKSUM(NEWID())) / .001 As Int) As Varchar(3))
				End
		End
	
	If UPPER(Left(@BoxNumber,2)) = '1Z'
		Begin
			If Not (Len(@BoxNumber) = 18)
			  BEGIN
--				Print N'Check 2'
				RAISERROR(50602,16,1)
				RETURN
			  END
			Else 
				Begin
					Set @BoxNumber = @BoxNumber
				End
		End

	Declare @BoxNumberPrefix Varchar(5)
	Set @BoxNumberPrefix = Left(@BoxNumber, PatIndex('%[^a-z.]%', @BoxNumber + '1')-1)
		If Not (UPPER(Left(@BoxNumber,2)) = '1Z')
			Begin
				If Not Exists(Select 1 From dbo.tblFCO Where FCO_Code = @BoxNumberPrefix)
					  BEGIN
--						Print N'Check 3'
						RAISERROR(50601,16,1)
						RETURN
					  END
			End



--Select Cast(Cast(RAND(CHECKSUM(NEWID())) / .0000000000001 As BigInt) As VarChar(13)) 
--	+ '-' + Cast(Cast(RAND(CHECKSUM(NEWID())) / .001 As Int) As Varchar(3))

--Select	Cast(DATEPART(year, GetDate()) As VarChar(4)) 
--      + Cast(DATEPART(month, GetDate()) As VarChar(2)) 
--      + Cast(DATEPART(day, GetDate()) As VarChar(2))
--      + Cast(DATEPART(Second, GetDate()) As VarChar(2))
--      + Cast(DATEPART(millisecond, GetDate()) As VarChar(2))


/******************************************************
 File Number validation
*******************************************************/
    -- Validate the incoming data
    If Not (@FileNumber like '[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' 
        OR @FileNumber like '[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' 
        OR @FileNumber like '[a-zA-Z][a-zA-Z][a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' 
        OR @FileNumber like '[a-zA-Z][a-zA-Z][a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' 
        OR @FileNumber like '.BOX.END.')
      BEGIN
        RAISERROR(50500,16,1)
        RETURN
      END

	Declare @FileNumberPrefix VarChar(5)
	Set @FileNumberPrefix = Left(@FileNumber, PatIndex('%[^a-z.]%', @FileNumber + '1')-1)
	If Not Exists(Select 1 From tblFileNumPrefix Where FileNumPrefix = @FileNumberPrefix)
		  BEGIN
			RAISERROR(50501,16,1)
			RETURN
		  END

/******************************************************
      Passed validation, now insert the data.
*******************************************************/
	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 @MachineName  VarChar(15) -- Workstation Network ID
		Set @MachineName = Host_Name()

    DECLARE @LogonName varchar(10) -- Logged on user interting the new record
		Set @LogonName = SUSER_SNAME()

	DECLARE @FCO_PK VarChar(3) -- FCO 3 digit Code
		Set	@FCO_PK = (Select FCO_PK from dbo.tblFCO where FCO_Active = 1) 


	[blue]Insert Into dbo.tblTrackingTable_Test[/blue]
		(
			EmployeeID, 
			MachineName, 
			BoxNumber, 
			FileNumber, 
			TrackingDate,
			FCO_PK
		)
	VALUES
		(
			@EmployeeID,
			@MachineName,
			Upper(@BoxNumber),
			Upper(@FileNumber),
			@TrackingDate,
			@FCO_PK
		)
End

This is currently insterting into a test table (identicle to the live production table) for testing.

I am not looking for someone to convert this for me! I am needing someone to point me in the correct direction.

THANKS !!!!!

Thanks

John Fuhrman