触发更新另一个数据库中的数据

发布于 2024-09-02 05:17:28 字数 1112 浏览 1 评论 0原文

我有以下架构：

数据库：测试。表：per_login_user，字段：用户名 (PK)、密码

数据库：wavinet。表：login_user，字段：用户名（PK），密码

我想要做的是创建一个触发器。每当数据库 test 中的表 per_login_user 上的 password 字段更新时，相同的值将被复制到字段 password在数据库wavinet的表login_user中

我通过Google搜索并找到了这个解决方案：http://forums.devshed.com/ms-sql-development-95/use- trigger-to-update-data-in-another-db-149985.html

但是，当我运行此查询时：

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

查询返回错误消息：

Msg 107, Level 16, State 3, Procedure trgPasswordUpdater, Line 4
The column prefix 'wavinet.dbo.login_wavinet' does not match with a table name or alias name used in the query.

原文

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 name or alias name used in the query.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

聚集的泪 2024-09-09 05:17:28

您更新了login_user，但FROM 子句不包含login_user。这正是错误消息的实际含义。如果要更新表，任何表，并且 UPDATE 语句使用 FROM 子句，则正在更新的表必须出现在 FROM 子句中：

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

我不确定您希望使用 DELETED 伪上的 JOIN 实现什么目的-table，也不使用奇怪的 WHERE 子句。您似乎忽略了用户 A 和用户 B 都具有相同密码的小情况，当用户 A 更改其密码时，您的代码也会更改用户 B 的密码......

至于将密码以明文形式存储在数据库：非常糟糕的主意。

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.

回复收藏 0 原文

~没有更多了~