oracle 多表触发
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5341113/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trigger with multiple tables
提问by Tobias
I am trying to make a trigger that are going to take a value from one table and then make a subtraction with an other value in another table.
我正在尝试制作一个触发器，该触发器将从一个表中获取一个值，然后与另一个表中的另一个值进行减法。
The trigger are going to operate in a table could (deposit), and then I would like to take the value that the user just inserted to the deposit table and subtract it with a value in another table(account). I have no problem to get following code to compile:
触发器将在一个表中操作可以（存款），然后我想取用户刚刚插入到存款表中的值并用另一个表（帐户）中的值减去它。我没有问题可以编译以下代码：
CREATE OR REPLACE TRIGGER aifer_ins?ttning
AFTER INSERT
ON deposit
FOR EACH ROW
WHEN (new.belopp is not null)
BEGIN
UPDATE account SET SALDO = saldo + :new.belopp;
end;
/
But of course this update al the rows in the table account. I have tried everything to make it work with only one row, but I can′t get it. Does anyone have some suggestion hove I can resolve this? Here is my tables:
但是当然这会更新表帐户中的所有行。我已经尽一切努力使它只在一行中工作，但我无法得到它。有没有人有一些建议我可以解决这个问题？这是我的表：
SQL> desc account
Name Null? Type
----------------------------------------- -------- ------------
KNR NOT NULL NUMBER(8)
KTNR NOT NULL NUMBER(6)
REGDATUM NOT NULL DATE
SALDO NUMBER(10,2)
SQL> desc deposit
Name Null? Type
----------------------------------------- -------- ------------
RADNR NOT NULL NUMBER(9)
PNR NOT NULL VARCHAR2(11)
KNR NOT NULL NUMBER(8)
BELOPP NUMBER(10,2)
DATUM NOT NULL DATE
回答by Christopher Armstrong
Wouldn't you just need to add a whereclause to the updatestatement in the trigger body? Or am I missing something?
你不是只需要在触发器主体where的update语句中添加一个子句吗？或者我错过了什么？
回答by APC
Presuming that the common column KNR is the primary key of ACCOUNT, I think the code you want is:
假设common列KNR是ACCOUNT的主键，我想你想要的代码是：
CREATE OR REPLACE TRIGGER aifer_ins?ttning
AFTER INSERT
ON deposit
FOR EACH ROW
WHEN (new.belopp is not null)
BEGIN
UPDATE account a
SET a.SALDO = a.saldo + :new.belopp
WHERE a.knr - :new.knr;
end;
/
Note that I have added a table alias to make the code clearer.
请注意，我添加了一个表别名以使代码更清晰。
回答by SHAKEB
delimiter $$
create trigger ss_ss before update on employee
for each row
begin
update consignee set
created_by = NEW.employee_pin
where created_by = OLD.employee_pin;
end
$$
delimiter ;
分隔符 $$
在更新
每一行的员工之前创建触发器 ss_ss
开始
更新收货人集
created_by = NEW.employee_pin
where created_by = OLD.employee_pin;
结束
$$
分隔符；
NOTE->employee and consignee are two tables
NOTE->员工和收货人是两张表
回答by Hitesh
**
**
Generate Triggers for all Tables
为所有表生成触发器
** Well, I did this originally to generate triggers for all tables in a database to audit data changes, and that is simple enough, just move the entire row from the deleted table to a mirrored audit table.
** 嗯，我这样做最初是为了给数据库中的所有表生成触发器来审计数据变化，这很简单，只需将整个行从已删除的表移动到镜像审计表即可。
But someone wanted to track activity on tables, so it's a little more simple. Here we create one log table, and any time a DML operation occurs, it is written there.
但是有人想跟踪表上的活动，所以它更简单一些。这里我们创建了一个日志表，任何时候发生 DML 操作，都会写在那里。
Enjoy
享受
USE Northwind GO
CREATE TABLE LOG_TABLE (
Add_dttm datetime DEFAULT (GetDate()),
TABLE_NAME sysname,
Activity char(6)
);
GO
DECLARE
@sql varchar(8000),
@TABLE_NAME sysname SET NOCOUNT ON
SELECT @TABLE_NAME = MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.Tables
WHILE @TABLE_NAME IS NOT NULL
BEGIN
SELECT @sql = 'CREATE TRIGGER
[' + @TABLE_NAME + '_Usage_TR]
ON [' + @TABLE_NAME +'] '
+ 'FOR INSERT, UPDATE, DELETE AS '
+ 'IF EXISTS (SELECT * FROM inserted)
AND NOT EXISTS (SELECT * FROM deleted) '
+ 'INSERT INTO LOG_TABLE
(TABLE_NAME,Activity)
SELECT ''' + @TABLE_NAME + ''', ''INSERT''' + ' '
+ 'IF EXISTS (SELECT * FROM inserted)
AND EXISTS (SELECT * FROM deleted) '
+ 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT '''
+ @TABLE_NAME + ''', ''UPDATE''' + ' '
+ 'IF NOT EXISTS (SELECT * FROM inserted)
AND EXISTS (SELECT * FROM deleted) '
+ 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity)
SELECT ''' + @TABLE_NAME + ''',
''DELETE''' + ' GO'
SELECT @sql EXEC(@sql)
SELECT @TABLE_NAME = MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME > @TABLE_NAME
END
SET NOCOUNT OFF
