3
I read topic the followoing topic: http://www.thescripts.com/forum/thread143458.html
and the people attempting to help requested further code/documentation to assist in resolving the problem. I have a similar problem that I would appreciate any help with.
I have a table called EZTest which has the following five fields:
[TestID] [int] IDENTITY(1,1) NOT NULL,
[TestData1] [varchar](50)
[TestData2] [varchar](50)
[TestData3] [int] NULL,
[CRC] [int] NULL
The data in the CRC field (if you haven't guessed already) will be added via a trigger that will do a BINARY_CHECKSUM(TestID,TestData1,TestData2,TestDat a3)
I will use the CRC field to verify if the imported data for a given row has changed and thus not import data that hasn't changed.
Data will be inserted and updated via stored procedures that will dump massive amounts of data into the table, but for our example, lets say we have 5 records in the table now and I do the following:
INSERT INTO EZTest
(TestData1,TestData2,TestData3)
SELECT TestData1,TestData2,TestData3
FROM EZTest
This should duplicate the existing data in the table.
OR
UPDATE EZTest
SET TestData2 = '3'
WHERE TestData2 = '2'
My trigger is as follows:
ALTER TRIGGER [tri_EZTest] ON [dbo].[EZTest]
FOR INSERT
AS
DECLARE @TestID int
DECLARE @CRC int
SELECT @CRC = BINARY_CHECKSUM(TestID,TestData1,TestData2,TestDat a3)
, @TestID = i.TestID
FROM inserted i
UPDATE EZTest
SET CRC = @CRC
WHERE TestID = @TestID
-----------------
AND
ALTER TRIGGER [tru_EZTest] ON dbo.EZTest
FOR UPDATE
AS
DECLARE @TestID int
DECLARE @CRC int
SELECT @CRC = BINARY_CHECKSUM(TestID,TestData1,TestData2,TestDat a3)
, @TestID = i.TestID
FROM inserted i
UPDATE EZTest
SET CRC = @CRC
WHERE TestID = @TestID
What I'm seeing is the first column gets updated but the rest do not.
As I said above, I'd appreciate any advice on how to best facilitate this without doing a cursor to reduce the insert to one record at a time.
