March 9, 2006 at 5:00 pm
MSSQL2000
Original table is tblCustomer that contains Company, Phone, State, RecID.
Destination table is tblTempCustomer that contains Company, State, RecID.
RecID is unique value in both tables.
I need a Trigger to Update existing records in tblTempCustomer when updated in tblCustomer. I also need it to Insert new rows in tblTempCustomer when they're added to tblCustomer. Triggers seem like the way to go since it needs to be instant. My attempts have failed, or at least it appears to update every single record, not just the one being updated/added.
Any help would be greatly appreciated!
March 9, 2006 at 5:26 pm
Use
UPDATE tblTempCustomer
SET ...
FROM iserted
WHERE inserted.RecID = tblTempCustomer.RecId
_____________
Code for TallyGenerator
March 9, 2006 at 5:28 pm
I'm assuming RecID is the same between both tables... so if 'ABC Company' exists in tblCustomer there would already be an entry in tblTempCustomer for 'ABC Company'. You're probably referencing tblCustomer in your trigger, you need to reference the inserted and deleted tables (inserted = state of the record after the action, deleted = state of the record before the action).
Something like this (assuming you want to update tblTempCustomer with the values after your insert/update):
CREATE TRIGGER tr_tblCustomer_ins_upd
ON tblCustomer
FOR INSERT, UPDATE
AS
BEGIN
IF @@ROWCOUNT = 0 RETURN
IF NOT EXISTS (SELECT 1 FROM tblTempCustomer c JOIN INSERTED i ON c.RecID = i.RecID)
INSERT tblTempCustomer
SELECT Company, State, RecID
FROM inserted
ELSE
UPDATE t
SET t.Company = i.Company
, t.State = i.State
FROM tblTempCustomer t
JOIN inserted i
ON t.RecID = i.RecID
March 9, 2006 at 5:39 pm
Aaron, your trigger gonna fail of part of RecId from Inserted exist in tblTempCustomer and part does not.
DON'T USE "IF" FOR SETS!
There is "WHERE" clause for this.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply