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;”就可以了，这是怎么回事？
------解决方案--------------------
trigger里的DDL语句不能这样写。
------解决方案--------------------
改成 execute immediate 'truncate table T_LX_LOAN_CUST_INFO';
------解决方案--------------------
- SQL code
tdbi@TDDW> create or replace trigger test_trgafter insert or update or deleteon testbegin execute immediate 'truncate table T_LX_LOAN_CUST_INFO';END;/ 2 3 4 5 6 7 Trigger created.Elapsed: 00:00:00.11tdbi@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 triggerORA-06512: at "TDBI.TEST_TRG", line 2ORA-04088: error during execution of trigger 'TDBI.TEST_TRG'