Oracle - 更新触发触发器的同一个表 [英] Oracle - update same table on which trigger is fired

问题描述

我有一个包含以下列的表 temp_table身份证号码，名称 varchar，密码varchar，pwd_change_date 时间戳

I have a table temp_table with the following columns Id number, name varchar, Password varchar, pwd_change_date timestamp

我只想在更改密码列时捕获 pwd_change_date 列中的时间戳.所以基本上我想在触发器内使用 update 语句来更新同一记录的 pwd_change_date 列中的时间戳值.

I want to capture the timestamp in pwd_change_date column only when password column is changed. So basically i want to use update statement inside the trigger to update timestamp value in pwd_change_date column for the same record.

示例当为一个用户更改密码时，我想在 pwd_change_date 中捕获同一记录的时间戳值.

Example When a password is changed for one user, I want to capture the timestamp value in pwd_change_date for the same record.

我尝试在 temp_table 上插入密码之前和之后插入密码，但出现突变错误.Oracle 中是否允许更新触发触发器的同一行/表?

I tried with before insert and after insert of password on temp_table, but getting mutation error. Is it allowed in Oracle to update the Same row/table on which trigger is fired?

推荐答案

您无需再次更新表；您可以在插入之前修改数据，使用 before-insert 行级触发器，例如:

You don't need to update the table again; you can modify the data before it is inserted, with a before-insert row level trigger, e.g.:

create trigger trig_pwd_date
before insert or update on temp_table
for each row
when (old.password is null and new.password is not null or new.password != old.password)
begin
  :new.pwd_change_date := systimestamp;
end;
/

db<>fiddle 演示

这里使用了 new 和 old 相关名称 以确定密码值是否已更改；以及new相关名，用于将系统时间分配给伪记录中的字段，当插入完成时，该字段成为列值.

This used the new and old correlation names to decide if the password value has changed; and the new correlation name to assign the system time to the field in the pseudorecord, which becomes the column value when the insert completes.

希望您没有在表格中存储纯文本密码.

Hopefully you aren't storing plain-text passwords in your table.

这篇关于Oracle - 更新触发触发器的同一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！