q; how to write this trigger

I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
also have T2 in Database2 which has the same fields and some extra fields.
Now based on the T1.Type=Insert or Update, I need to perform either update
or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>â'Doneâ'. If this is
a success, I need to set T1.Status=â'Doneâ' for the updated records. So this
should only be updated records.
T1 is a frequently inserted table, so there might be more than one record
coming there at the same time. How should I write my insert trigger and
correctly set T1.Status=â'Doneâ', any example would be greatly appreciated.On Wed, 1 Nov 2006 06:09:02 -0800, JIM.H. wrote:
>I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
>also have T2 in Database2 which has the same fields and some extra fields.
>Now based on the T1.Type=Insert or Update, I need to perform either update
>or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>?Done?. If this is
>a success, I need to set T1.Status=?Done? for the updated records. So this
>should only be updated records.
>T1 is a frequently inserted table, so there might be more than one record
>coming there at the same time. How should I write my insert trigger and
>correctly set T1.Status=?Done?, any example would be greatly appreciated.
Hi Jim,
CREATE TRIGGER JimsTrigger
ON T1 AFTER INSERT
AS
IF @.@.ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
INSERT INTO T2 (ID, Type, Status, ...)
SELECT ID, Type, Status, ...
FROM inserted
WHERE Type = 'Insert'
AND Status <> 'Done'
UPDATE T2
SET Type = i.Type,
Status = i.Status,
...
FROM inserted AS i
JOIN T2 ON T2.ID = i.ID
WHERE i.Type = 'Update'
AND i.Status <> 'Done'
UPDATE T1
SET Status = 'Done'
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.Type IN ('Update', 'Insert')
AND i.Status <> 'Done'
AND i.ID = T1.ID)
go
Untested, and you'll probably want to add error handling.
--
Hugo Kornelis, SQL Server MVP