QUOTE:

I have a personal details table and a table that records an individuals transactions through a process. Each time a new record is added to show a new transaction I want the trigger to ensure that all previous transaction records for that individual have a record status of R leaving the new record as the active one.

---

Absolutely no need for a cursor (in a trigger? come on!), or for variables.  Think SET based...your trigger would only work for one record, as only one value will fit in a variable.  You're crippling SQL when you force it to deal with one row at a time, either this way or through a cursor.

From the logical table inserted you should join to the updated table on the individualID's being the same and the RecordIDs being different:

create trigger myTrigger

on myTable

After Update

as

Update

Set   RecordStatus = 'R'

From   inserted i

JOIN   dbo.myTable t on t.IndividualID = i.IndividualID

Where i.RecordID <> t.RecordID

Now while this should work for you; you run a risk of some pretty bad locking going on.  This is a tough problem and you should consider other options if possible, although if this table is open to be updated directly then you're pretty much stuck with this.  An option is only allowing updates through procs, which can handle all the logic normally put in triggers.