oracle触发器 PLS-00103: 出现符号 "TABLE"在需要下列之一时：:= .

[wgh770513146]

create trigger cust_info
after insert or update or delete
on T_CP_CUST_INFO
begin
truncate table T_LX_LOAN_CUST_INFO;
--公司客户插入T_LX_LOAN_CUST_INFO
insert into T_LX_LOAN_CUST_INFO

(CUST_ID,CUST_NO,CUST_NAME,CUST_TYPE_CD,BRANCH_NO,CREDIT_LIMIT,loan_start_date,loan_e

nd_date,loan_amt,aval_amt,CREATED_TIME,CREATED_PERSON,UPDATE_TIME,UPDATE_PERSON)

(select SEQ_TI3.nextval as CUST_ID,t.* from
(select
a.cust_no,
a.cust_name,
a.cust_type_cd,
a.BRANCH_NO,
c.credit_limit,
to_char(c.start_date,'yyyymmdd'),
to_char(c.expiration_date,'yyyymmdd'),
sum(e.LN_BAL),
d.DEPOSITS_BALANCE,
sysdate as CREATED_TIME,
'lx' as CREATED_PERSON,
sysdate as UPDATE_TIME,
'lx' as UPDATE_PERSON
from
T_CP_CUST_INFO a,T_CP_CREDIT_LIMIT c,T_CP_CUST_DEPOSITS_ACCT d,
T_CP_CONT_LOAN_ACCT e
where
a.cust_type_cd=1
and c.CUSTOMER_NUM=a.cust_no
and a.CUST_NO=d.CUST_NO
and d.DCC_CUSTOMER_NO=e.DCC_CUST_NO
and (c.limit_status_cd='1' or c.limit_status_cd='3')
group by
a.cust_no,
a.cust_name,
a.cust_type_cd,
a.BRANCH_NO,
c.credit_limit,
c.start_date,
c.expiration_date,
d.DEPOSITS_BALANCE) t);
end;
/
执行以上语句，提示：
PLS-00103: 出现符号 "TABLE"在需要下列之一时?
:= . ( @ % ;
符号 ":=在 "TABLE" 继续之前已插入。

如果去掉“truncate table T_LX_LOAN_CUST_INFO;”就可以了，这是怎么回事？

[yixilan]

trigger里的DDL语句不能这样写。

[wgh770513146]

[Quote=引用 1 楼 yixilan 的回复:]
trigger里的DDL语句不能这样写。
[/Quote]

那要怎么写，第一次用触发器啊。

[forgetsam]

改成 execute immediate 'truncate table T_LX_LOAN_CUST_INFO';

[luoyoumou]

先搞清楚：你为什么要在触发器中这样做？
truncate table T_LX_LOAN_CUST_INFO;

[luoyoumou]

tdbi@TDDW> create or replace trigger test_trg
after insert or update or delete
on test
begin
  execute immediate 'truncate table T_LX_LOAN_CUST_INFO';
END;
/  2    3    4    5    6    7  

Trigger created.

Elapsed: 00:00:00.11
tdbi@TDDW> insert into test select 1 from dual;
insert into test select 1 from dual
            *
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "TDBI.TEST_TRG", line 2
ORA-04088: error during execution of trigger 'TDBI.TEST_TRG'

[luoyoumou]

tdbi@TDDW> drop trigger test_trg;

Trigger dropped.

Elapsed: 00:00:00.25
tdbi@TDDW> insert into test select 1 from dual;

1 row created.

Elapsed: 00:00:00.00
tdbi@TDDW> commit;

Commit complete.

[Galen_Gao]

你不说我还不知道呢。。。。[Quote=引用 1 楼 yixilan 的回复:]
trigger里的DDL语句不能这样写。
[/Quote]

[wgh770513146]

[Quote=引用 3 楼 forgetsam 的回复:]
改成 execute immediate 'truncate table T_LX_LOAN_CUST_INFO';
[/Quote]

执行没问题，但在更新T_CP_CUST_INFO表时报错了
16:30:37 [UPDATE - 0 row(s), 0.000 secs] [Error Code: 4092, SQL State: 42000] ORA-04092: COMMIT 不能在触发器中
ORA-06512: 在 "SCOTT.CUST_INFO", line 2
ORA-04088: 触发器 'SCOTT.CUST_INFO' 执行过程中出错

[Jerry901217]

[Quote=引用 3 楼 的回复:]

改成 execute immediate 'truncate table T_LX_LOAN_CUST_INFO';
[/Quote]
学习了，还有许多需要学习的