using triggers in sql 2000 [Resolved]

Okay what I'd like to do is when a value is updated in one table I would like to add a value into another table

Code:

---

CREATE TRIGGER dbo.gmprospectdate
ON contact1
FOR UPDATE
AS
DECLARE @key1 varchar(255), @ACCOUNTNO varchar(20)

SELECT @key1 = key1, @ACCOUNTNO=ACCOUNTNO
FROM inserted
where key1 is not null

case  @key1
when 'Prospect' then update contact2 set uprospect = getdate() where ACCOUNTNO = @ACCOUNTNO
when 'Trial' then update contact2 set UFCSDATE = getdate() where ACCOUNTNO = @ACCOUNTNO
when 'Client' then update contact2 set USTARTDATE = getdate() where ACCOUNTNO = @ACCOUNTNO
end

---

This is what I've come up with and it obviuosly doesn't work, could you give me some pointers.
Cheers all.

Never assume the Inserted table has only one record. The Select statement will only return one Key/AccountNo and so you may miss records.

A simple Update statement should do the trick. This is off the top of my head so the syntax may be off.

Code:

---

Update Contact2 Set
  uprospect = Case When Inserted.Key1 = 'Prospect' Then GetDate() Else uprosect End,
  UFCSDATE = Case When Inserted.Key1 = 'Trial' Then GetDate() Else UFCSDATE End,
  USTARTDATE = Case When Inserted.Key1 = 'Client' Then GetDate() Else USTARTDATE End,
From Contact2 Inner Join
Inserted On Contact2.AccountNo = Inserted.AccountNo And Inserted.Key1 Is Not Null

---

Thanks, the below works a treat.

Code:

---

CREATE TRIGGER dbo.gmupstaus
ON contact1
for update
as
Update Contact2 Set
  uprospect = Case When Inserted.Key1 = 'Prospect' and uprospect is null Then GetDate() Else uprospect End,
  UFCSDATE = Case When Inserted.Key1 = 'Trial' and UFCSDATE is null  Then GetDate() Else UFCSDATE End,
  USTARTDATE = Case When Inserted.Key1 = 'Client' and USTARTDATE is null  Then GetDate() Else USTARTDATE End
From Contact2 Inner Join
Inserted On Contact2.AccountNo = Inserted.AccountNo And Inserted.Key1 Is Not Null

---