uSP to Instead of Insert trigger??

[sparkbyte]

How would I turn this uSP into an Instead of Trigger??

ALTER PROCEDURE [dbo].[usp_TrackingInput_v5]
    -- 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) 


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

Thanks

John Fuhrman

 

[sparkbyte]

No hints??


As you can see I am trying to build a data validation trigger. Hasn't anyone done this?? There has to be an example of something somewhere out there. Everything I have been able to google so far are all very simple examples and I can't seem to figure it out so far.

Thanks

John Fuhrman