SQL Server 2000 Update Trigger

My syntax appears to not work for column 25 (counting base-1 or 24 base 0 -
EDI_SENT). Also is there a better way to do this (audit)? Note: One of the
two servers involved in the transactions uses SQL 2005 rather than 2000. We
have mystery values showing up in the EDI_SENT column of the EDI856_LINE
table.

TRIGGER [dbo].[updEDI_SENT]
ON [dbo].[EDI856_LINE]
FOR update AS
IF (COLUMNS_UPDATED() & 16777216) =16777216--is edi_sent changing?
BEGIN
INSERT INTO tSentInformation(NTUserID,EDI_SENT,RecordType)select
User,getdate(),'OLD'
FROM deleted del
INSERT INTO tSentInformation(NTUserID,EDI_SENT,RecordType)select
User,getdate(),'NEW'
print 'done'
END

--Target Table for Update Trigger:
CREATE TABLE [dbo].[tSentInformation](
[NTUserID] [varchar](40) NULL,
[EDI_SENT] [smalldatetime] NULL,
[RecordType] [varchar](3) NULL
)

--Table requiring update trigger
TABLE [dbo].[EDI856_LINE](
[RecordID] [int] IDENTITY(1,1) NOT NULL,
[BATCH_NO] [varchar](25) NULL,
[SOURCE] [varchar](2) NULL,
[CO_NUMBER] [varchar](15) NULL,
[CO_LN_NO] [varchar](3) NULL,
[PONumber1] [varchar](15) NULL,
[PONumber2] [varchar](15) NULL,
[LOT] [varchar](20) NOT NULL,
[ITEM] [varchar](15) NULL,
[CUS_PO_LN] [varchar](6) NULL,
[MFG_ITEM] [varchar](15) NULL,
[BUYER_PART] [varchar](20) NULL,
[BUYER_ITEM] [varchar](20) NULL,
[JOSH_PO] [varchar](20) NULL,
[ROLL_NO] [varchar](9) NULL,
[MILL_DYE_LOT] [varchar](8) NULL,
[SHADE] [varchar](1) NULL,
[PC_CUT_NO] [varchar](9) NULL,
[CUT_YN] [varchar](1) NULL,
[YARDAGE1] [decimal](15, 3) NULL,
[YARDAGE2] [decimal](15, 3) NULL,
[DEFECTS] [decimal](15, 3) NULL,
[SHIP_QTY] [decimal](15, 3) NULL,
[SHIP_DATE] [smalldatetime] NULL,
[EDI_SENT] [smalldatetime] NULL,
[SHIPMENT_NO] [decimal](15, 3) NULL,
[ATPiece] [varchar](25) NULL,
CONSTRAINT [PK_EDI856_LINE] PRIMARY KEY NONCLUSTERED
(
[RecordID] ASC
) ON [PRIMARY]
)
--
Regards,
Jamie
.

Relevant Pages

  • Re: Peer to peer replication on SQL 2000?
    ... transactions. ... What would you suggest me to do with SQL 2000? ... I have 3 servers, and want to let all of them be both publishers and subscribers, so that any modification done on any of the three servers replicates to the others. ... I initially thought at merge replication, then I liked the suggestion about bi-directional replication... ...
    (microsoft.public.sqlserver.replication)
  • Re: Getting double count of updated records
    ... I have an update trigger on the table that updates ... Below is my ASP.NET SQL ... cmdSelect = New SqlCommand(strSQL, conFileData) ...
    (microsoft.public.dotnet.framework.adonet)