Create Or Replace Trigger Jde_Ap_After_Insert
After Insert On texp.Jde_Accountpayment
For Each RowBegin
if :New.company = 700 Then
Begin
Insert Into wnc.Jde_Accountpayment Values
(
:New.APPID
,:New.EEBATCHID
,:New.SUPPLIERNO
,:New.INVOICENO
,:New.INVOICEAMOUNT
,:New.INVOICEDATE
,:New.GLDATE
,:New.VOUCHER
,:New.TYPE
,:New.COMPANY
,:New.BUSINESSUNIT
,:New.GLBANKACCOUNT
,:New.SUBNO
,:New.SUBTYPE
,:New.STATE
,:New.FINALNAME
,:New.FINALDATE
,:New.PAYMENTTYPE
,:New.REMARK
);
Delete from texp.Jde_Accountpayment a Where a.Appid=:New.appid;
End;
End if;
End Jde_Ap_After_Insert;
After Insert On texp.Jde_Accountpayment
For Each RowBegin
if :New.company = 700 Then
Begin
Insert Into wnc.Jde_Accountpayment Values
(
:New.APPID
,:New.EEBATCHID
,:New.SUPPLIERNO
,:New.INVOICENO
,:New.INVOICEAMOUNT
,:New.INVOICEDATE
,:New.GLDATE
,:New.VOUCHER
,:New.TYPE
,:New.COMPANY
,:New.BUSINESSUNIT
,:New.GLBANKACCOUNT
,:New.SUBNO
,:New.SUBTYPE
,:New.STATE
,:New.FINALNAME
,:New.FINALDATE
,:New.PAYMENTTYPE
,:New.REMARK
);
Delete from texp.Jde_Accountpayment a Where a.Appid=:New.appid;
End;
End if;
End Jde_Ap_After_Insert;
解决方案 »
- 如何写exp和imp脚本命令?帮帮忙!
- 根据BOM表中物料需求的算法
- 为什么使用union 或者union all的连接查询的语句中不能够使用 Order 排序? 怎么查看具体的insert into a select ... from ...的过程呢?
- TOAD连接Oracle10g时提示“找不到oci.dll”
- Date型的累計問題
- 请问Oracle的错误号
- 如何批量把某个用户的表的所有索引转到另外一个表空间?
- 关于过程
- 在8i/Win2000中如何建立OMS(ORACLE Management Server)?
- 请帮我解决复制用户遇到的问题?
- 求Oracle Transparent Gateway for oracle 10g 的下载地址
- 关于数据库的建表问题
select * from texp.jde_accountpayment;可以查询结果!
After Insert On texp.Jde_Accountpayment@link
For Each Row Begin
if :New.company = 700 Then
Begin
Insert Into wnc.Jde_Accountpayment Values
(
:New.APPID
,:New.EEBATCHID
,:New.SUPPLIERNO
,:New.INVOICENO
,:New.INVOICEAMOUNT
,:New.INVOICEDATE
,:New.GLDATE
,:New.VOUCHER
,:New.TYPE
,:New.COMPANY
,:New.BUSINESSUNIT
,:New.GLBANKACCOUNT
,:New.SUBNO
,:New.SUBTYPE
,:New.STATE
,:New.FINALNAME
,:New.FINALDATE
,:New.PAYMENTTYPE
,:New.REMARK
);
Delete from texp.Jde_Accountpayment a Where a.Appid=:New.appid;
End;
End if;
End Jde_Ap_After_Insert; 编译时出现错误,出现ORA-02021: 不允许对远程数据库进行DDL 操作的错误,如何解决?
遍历texp.Jde_Accountpayment中的记录。
即:texp.Jde_Accountpayment 是mutating only for a row-level trigger. This means that we cannot
query it in a row-level trigger, but we can in a statement-level trigger你可以查询下相关Mutating Table(变异表)上trigger的应用。
grant insert,delete on wnc.Jde_Accountpayment to texp; 不能正确的进行赋权,提示为:ORA-01749:用户不能自己为自己 GRANT/REVOKE权限
--Delete from texp.Jde_Accountpayment a Where a.Appid=:New.appid;
----- 测试表
CREATE TABLE TEST_A(
COL_1 NUMBER,
COL_2 VARCHAR2(10)
);CREATE TABLE TEST_B(
COL_1 NUMBER,
COL_2 VARCHAR2(10)
);----- 测试 trigger
Create Or Replace Trigger TEST_A_After_Insert
After Insert On TEST_A
For Each Row Begin
if :NEW.COL_1 = 1 Then
Begin
Insert Into TEST_B Values
(
:New.COL_1
,:New.COL_2
);
--Delete from texp.Jde_Accountpayment a Where a.Appid=:New.appid;
End;
End if;
End TEST_A_After_Insert; ---- 测试数据
INSERT INTO TEST_A VALUES (1,'A');
INSERT INTO TEST_A VALUES (1,'B');
INSERT INTO TEST_A VALUES (2,'A');
INSERT INTO TEST_A VALUES (3,'A');
INSERT INTO TEST_A VALUES (4,'A');
INSERT INTO TEST_A VALUES (1,'C');
COMMIT;--------结果
SELECT * FROM TEST_A;
COL_1 COL_2
1 A
1 B
2 A
3 A
4 A
1 C
SELECT * FROM TEST_B;
COL_1 COL_2
1 A
1 B
1 C----- 删除
DROP TRIGGER TEST_A_After_Insert;
DROP TABLE TEST_A;
DROP TABLE TEST_B;
1、当前用户是否有create trigger的权限;
2、是否有向wnc.Jde_Accountpayment插入数据的权限;
3、到底是After Insert On texp.Jde_Accountpayment@link 还是
After Insert On texp.Jde_Accountpayment;
4、如果是通过db_link插入数据,请确认插入的数据没有LOB类型的字段
Create Or Replace Trigger Jde_Ap_After_Insert
After Insert On texp.Jde_Accountpayment
For Each RowBegin
if :New.company = 700 Then
begin Insert Into wnc.Jde_Accountpayment Values
(
:New.APPID
,:New.EEBATCHID
,:New.SUPPLIERNO
,:New.INVOICENO
,:New.INVOICEAMOUNT
,:New.INVOICEDATE
,:New.GLDATE
,:New.VOUCHER
,:New.TYPE
,:New.COMPANY
,:New.BUSINESSUNIT
,:New.GLBANKACCOUNT
,:New.SUBNO
,:New.SUBTYPE
,:New.STATE
,:New.FINALNAME
,:New.FINALDATE
,:New.PAYMENTTYPE
,:New.REMARK
);
Delete from texp.Jde_Accountpayment a Where a.Appid=:New.appid;
end;
End if;
Exception
WHEN Others THEN
Rollback;
End Jde_Ap_After_Insert;
这句是有问题的,看上面回复知道我建的触发器属于 行触发器,那语句触发器的语法是怎么样的呢?怎样修改才能成功执行上面删除语句呢