創建一個trigger名字;TR_RollupExpAmt
觸發在after updating ExpApprAmt or deleting a row of the ExpenseItem
triiger的邏輯解釋:
如果更新(updating) ExpApprAmt這一行(the rollup amount should be the difference of the New.ExpApprAmt minus the Old.ExpApprAmt. ) rollup amount 應該是New.ExpApprAmt 和 Old.ExpApprAmt 的差
如果刪除表ExpenseItem中的一列 rollup amount 應該是Old.ExpApprAmt的負值
要執行rollup, 要調用the spRollupExpenseItem procedure described in Task 2E.
如果輸出的參數of the spRollupExpenseItem procedure is true 插入一列在Log_Table中
ExcText(解釋錯誤的信息)值應該表明選項是 (update or delete) and the rollup amount.
如果輸出的參數of the spRollupExpenseItem procedure is false. 什麽都不做~
觸發在after updating ExpApprAmt or deleting a row of the ExpenseItem
triiger的邏輯解釋:
如果更新(updating) ExpApprAmt這一行(the rollup amount should be the difference of the New.ExpApprAmt minus the Old.ExpApprAmt. ) rollup amount 應該是New.ExpApprAmt 和 Old.ExpApprAmt 的差
如果刪除表ExpenseItem中的一列 rollup amount 應該是Old.ExpApprAmt的負值
要執行rollup, 要調用the spRollupExpenseItem procedure described in Task 2E.
如果輸出的參數of the spRollupExpenseItem procedure is true 插入一列在Log_Table中
ExcText(解釋錯誤的信息)值應該表明選項是 (update or delete) and the rollup amount.
如果輸出的參數of the spRollupExpenseItem procedure is false. 什麽都不做~
解决方案 »
- 数据库加密问题
- oracle按照默认的安装完成后会有几个用户?
- oracle case用法疑问
- 关于权限和表的问题?
- PL/SQL用scott/tiger登录时不能以Normal权限登录?
- Windows的客户端可以连接Redhat8.0里的oracle,但是不能连接Redhat Enterprise 3里的oracle?
- 关于bat调用sql文件。
- oracle错误ORA-12560:TNS:协议适配器错误
- 100分求实现这样的查询的SQL语句怎么写
- 各位在线的大虾,紧急求助.
- Oracle 10g 中如何对一个表进行排他锁定 而且需要锁定一定时间
- start with connect by性能问题
-- Author:
-- Filename : PART2f.sql
-- Description: This script creating a trigger called TR_RollupExpAmt
-- and updating/deleting data in the table for testing the trigger and showing the testing results
-- Last modified: create or replace trigger TR_RollupExpAmt
after delete or update of ExpApprAmt
on ExpenseItem
for each row
declare
ra number;
begin
if updating then
ra:=:new.ExpApprAmt-:old.ExpApprAmt;
if spRollupExpenseItem(:old.erno,:old.ecno,ra) then
insert into LOG_TABLE values(excno_seq.nextval,'TR_RollupExpAmt','ExpenseItem',:old.EINO,SYSTIMESTAMP,'update operation and rollup amount is:'||ra);
end if;
elsif deleting then
ra:=0-:old.ExpApprAmt;
if spRollupExpenseItem(:old.erno,:old.ecno,ra) then
insert into LOG_TABLE values(excno_seq.nextval,'TR_RollupExpAmt','ExpenseItem',:old.EINO,SYSTIMESTAMP,'delete operation and rollup amount is:'||ra);
end if;
end if;
end;
/select EINO,EXPDESC,EXPENSEDATE,EXPAMT,EXPAPPRAMT,ERNO,ECNO,ASSETNO from ExpenseItem;
update ExpenseItem set EXPAPPRAMT=98 where EINO=2;
delete from ExpenseItem where eino=2;
select * from log_table;
select EINO,EXPDESC,EXPENSEDATE,EXPAMT,EXPAPPRAMT,ERNO,ECNO,ASSETNO from ExpenseItem;