473,573 Members | 4,459 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Trigger issue on Mass Insert/Update

3 New Member
I read topic the followoing topic: http://www.thescripts. com/forum/thread143458.ht ml

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,TestDat a1,TestData2,Te stData3)

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,Test Data2,TestData3 )
SELECT TestData1,TestD ata2,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,TestDat a1,TestData2,Te stData3)
, @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,TestDat a1,TestData2,Te stData3)
, @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 2514
zierde01
3 New Member
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

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

Similar topics

1
6147
by: Gent | last post by:
am using FOR UPDATE triggers to audit a table that has 67 fields. My problem is that this slows down the system significantly. I have narrowed down the problem to the size (Lines of code) that need to be compiled after the trigger has been fired. There is about 67 IF Update(fieldName) inside the trigger and a not very complex select statement...
7
2258
by: ZRexRider | last post by:
Hi, I have trigger that enforces the creation of a sortorder that is always 1 digit higher than the current highest on Inserts. This trigger works great if I add one row at a time so I think the logic is sound. However, I have a Stored Procedure that copies a bunch of rows into this table and all of the SortOrder values come up as 0....
3
7273
by: takilroy | last post by:
Hi, Does anyone know of a simple way to do this? I want to create an insert trigger for a table and if the record already exists based on some criteria, I want to update the table with the values that are passed in via the insert trigger without having to use all the 'set' statements for each field (so if we add fields in the future I...
18
5956
by: Bill Smith | last post by:
The initial row is inserted with the colPartNum column containing a valid LIKE pattern, such as (without the single quotes) 'AB%DE'. I want to update the column value with the results of a query against a different table (that uses the LIKE predicate) but cannot get around the SQL0132 error . I have tried the hex notation after the LIKE such...
0
2462
by: JohnO | last post by:
Thanks to Serge and MarkB for recent tips and suggestions. Ive rolled together a few stored procedures to assist with creating audit triggers automagically. Hope someone finds this as useful as I've found it educational. Note: - I build this for use in a JDEdwards OneWorld environment. I'm not sure how generic others find it but it...
6
2276
by: Robert Fitzpatrick | last post by:
On 7.4.2 I have a trigger that I want to update any existing boolean values to false if a new one in that group is declare true by inserting a new record or updating an existing record: ohc=# CREATE OR REPLACE FUNCTION "public"."clear_common_groups" () RETURNS trigger AS' ohc'# BEGIN ohc'# IF NEW.common_area = ''t'' THEN ohc'# ...
8
7867
by: Benzine | last post by:
Hi, I have an issue with my replication at the moment. I will try to describe the scenario accurately. I am using MS SQL 2000 SP4 with Merge Replication. Subscribers connect to the publisher to upload/download changes. I have a trigger set up on one table which updates another, here is an example of the trigger: "CREATE TRIGGER...
2
4307
by: paulmac106 | last post by:
Hi, I need to update a field in about 20 records on a table. The table has an update trigger (which updates the field whenever a record is updated). As a result I'm getting an error: "Subquery returned more than 1 value.", and the update fails. Is there a way in the stored procedure to handle this issue? thanks for your help.
11
7854
by: tracy | last post by:
Hi, I really need help. I run this script and error message appeal as below: drop trigger log_errors_trig; drop trigger log_errors_trig ERROR at line 1: ORA04080: trigger 'LOG_ERRORS-TRIG' does not exist drop table log_errors_tab;
0
7661
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7978
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8167
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7730
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8028
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5550
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3692
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2164
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1263
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.