Trigger to update data in another DB

Question

I have the following schema: Database: test. Table: per_login_user, Field: username (PK), password Database: wavinet. Table: login_user, Field: username (PK), password What I want to do is to create a trigger. Whenever a `password` field on table `per_login_user` in database `test` get updated, the same value will be copied to field `password` in Table `login_user` in database `wavinet` I have search trough Google and find this solution: http://forums.devshed.com/ms-sql-development-95/use-trigger-to-update-data-in-another-db-149985.html But, when I run this query: ``` CREATE TRIGGER trgPasswordUpdater ON dbo.per_login_user FOR UPDATE AS UPDATE wavinet.dbo.login_user SET password = I.password FROM inserted I INNER JOIN deleted D ON I.username = D.username WHERE wavinet.dbo.login_wavinet.password = D.password ``` the query return error message: ``` Msg 107, Level 16, State 3, Procedure trgPasswordUpdater, Line 4 The column prefix 'wavinet.dbo.login_wavinet' does not match with a table n…

Accepted Answer

You update login_user, but your FROM clause doesn't contain login_user. That's exactly what the error message is saying actually. If you want to update a table, any table, and the UPDATE statement uses a FROM clause, then the table being updated must be present in the FROM clause:

```
UPDATE  wavinet.dbo.login_user 
SET     password = I.password 
FROM    wavinet.dbo.login_user
JOIN inserted I ON wavinet.dbo.login_wavinet.username = I.username
```

I'm not sure what you want to achieve with the JOIN on the DELETED pseudo-table, nor with your strange WHERE clause. You seem to ignore the trivial case of user A and user B both having the same password and when user A changes his password, your code would change user B's password as well...

As for the simple fact of storing password in clear in the database: very bad idea.

Trigger to update data in another DB

Solution

Problem