469,602 Members | 1,947 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,602 developers. It's quick & easy.

Trigger issue on Mass Insert/Update

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.
Oct 27 '06 #1
1 2357
I could have swore I was in the MSSQL topic area, not MySQL...should I repost, or can someone move this....very sorry...
Oct 27 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by ZRexRider | last post: by
3 posts views Thread by takilroy | last post: by
18 posts views Thread by Bill Smith | last post: by
reply views Thread by JohnO | last post: by
6 posts views Thread by Robert Fitzpatrick | last post: by
2 posts views Thread by paulmac106 | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.