Update trigger old values natural key

I have an accounts table with the account owner as the primary key. In the update trigger, I want to update some accounts to new owners. Since th开发者_运维知识库is table doesn't have an id field, how do I use the inserted/updated tables in the trigger? DB is sql server 2008.

CREATE TRIGGER accounts_change_owner on accounts AFTER INSERT
AS BEGIN
   MERGE INTO accounts t
   USING 
   (
       SELECT *
       FROM inserted e
           INNER JOIN deleted f ON 
              e.account_owner = f.account_owner ---this won't work since the new account owner value is diff
   ) d
   ON (t.account_owner = d.account_owner)
   WHEN MATCHED THEN
        UPDATE SET t.account_owner = d.account_owner
END

---

I think I understood your question, but I am not sure. You want to be able update account owner name in one table and to have this update propagated to the referencing tables?

If so you don't really need a trigger, you can use on update cascade foreign key.

Like this:

create table AccountOwner
(
    Name varchar(100) not null 
        constraint PK_AccountOwner primary key
)

create table Account
(
    AccountName varchar(100) not null,
    AccountOwnerName varchar(100) not null 
        constraint FK_Account_AccountOwnerName references AccountOwner(Name) on update cascade
)

insert AccountOwner values('Owner1')

insert Account values('Account1', 'Owner1')

Now if I update table AccountOwner like this

update AccountOwner
set Name = 'Owner2'
where Name = 'Owner1'

it will automatically update table 'Account'

select *
from Account

AccountName   AccountOwnerName
-----------   -----------------
Account1      Owner2

---

I think you need to modify the design of your table. Recall that the three attributes of a primary key are that the primary key must be

1. Non-null
2. Unique
3. Unchanging

(If the primary key consists of multiple columns, all columns must follow the rules above). Most databases enforce #1 and #2, but the enforcement of #3 is usually left up to the developers.

Changing a primary key value is a classic Bad Idea in a relational database. You can probably come up with a way to do it; that doesn't change the fact that it's a Bad Idea. Your best choice is to add an artificial primary key to your table, put NOT NULL and a UNIQUE constraints on the ACCOUNT_OWNER field (assuming that this is the case), and change any referencing tables to use the artificial key.

The next question is, "What's so bad about changing a primary key value?". Changing the primary key value alters the unique identifier for that particular data; if something else is counting on having the original value point back to a particular row, such as a foreign key relationship, after such a change the original value will no longer point where it's supposed to point.

Good luck.