• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1958
  • Last Modified:

TRIGGER on newly inserted rows only in SQL Server 2005

How does the trigger fire?  I'm basically after an efficient way to update on a BULK INSERT in SQL Server 2005

i'm doing a BULK INSERT and want to update  the new rows only after the INSERT.

As the table gets larger and larger, i do not want the conditional UPDATE to query the whole destination table of tblCustomer  -  I only want to query the newly inserted rows.

CREATE TRIGGER triggerA ON tblCustomer AFTER INSERT AS
UPDATE tblCustomer
SET xflag = 1
FROM tblCustomer, tblAddress
WHERE tblCustomer.customerID  = tblAddress.customerID

This trigger runs across the whole of tblCustomers

Now the questions i have are
1.  does the bulk insert complete on all rows, before the trigger is fired
2.  how do i only run the UPDATE query against the new rows


0
charb0y
Asked:
charb0y
  • 2
1 Solution
 
adatheladCommented:
Hi,

1. Yes, the trigger will fire once at the end
2.
CREATE TRIGGER triggerA ON tblCustomer AFTER INSERT AS
UPDATE tblCustomer
SET xflag = 1
FROM tblCustomer
    JOIN tblAddress ON tblCustomer.customerID = tblAddress.customerID
    JOIN inserted on tblCustomer.customerID = inserted.customerID

"inserted" holds details on the new records created
0
 
adatheladCommented:
I wasn't sure if you actually needed the tblAddress join in there, but kept it in just incase (i.e. only update new customers that have an address recorded). If not, then just remove that join
0
 
charb0yAuthor Commented:
fantastic  - exactly what i've been looking for
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now