求助各位高人解答,触发器如下:
Create or replace Trigger trigger_NewStudent
after insert
on t_welcome_student
begin
insert into t_xsjbxx_jbxx(xh,xm,xznj,sfbs,sfzjlxm,sfzjh,xbdm,yxsh,zydm,xsbh,sfzj,sfzx,xslbdm)
select CID,NAME,GRADE,'0','01',IDCARD,(case sexname when '男' then '1' else '2'end),deptcode,specid,
(dbms_random.string('l',8)||'-8c45-11e0-8a9a-67da650471f5'),'01','01',
(case studyyears when '4'then '01'else '02'end)
from t_welcome_student
where CID not in (select xh from t_xsjbxx_jbxx);
dbms_output.put_line("4")
end trigger_NewStudent;
commit;
错误:ORA-04098:trigger 'USR_CES.TRIGGER_NEWSTUDENT' is invalid and failed re-validation
Create or replace Trigger trigger_NewStudent
after insert
on t_welcome_student
begin
insert into t_xsjbxx_jbxx(xh,xm,xznj,sfbs,sfzjlxm,sfzjh,xbdm,yxsh,zydm,xsbh,sfzj,sfzx,xslbdm)
select CID,NAME,GRADE,'0','01',IDCARD,(case sexname when '男' then '1' else '2'end),deptcode,specid,
(dbms_random.string('l',8)||'-8c45-11e0-8a9a-67da650471f5'),'01','01',
(case studyyears when '4'then '01'else '02'end)
from t_welcome_student
where CID not in (select xh from t_xsjbxx_jbxx);
dbms_output.put_line("4")
end trigger_NewStudent;
commit;
错误:ORA-04098:trigger 'USR_CES.TRIGGER_NEWSTUDENT' is invalid and failed re-validation
解决方案 »
- 关于PLSQL导入数据的问题 。。。。。。。。。~~~~。。。~。~。~。
- HELP(高分,高手请进!)英文版win2003 Oracle10g备份问题
- 请教: oracle oci 开发的导出工具ociuldr
- oracle 9i grant 赋予权限问题
- 如何将sequence S的下一个数值插入表T1的id字段 (T1只有id一个字段)?
- oracle字段默认值问题,在线等!
- 请大家帮忙把这段SQL存储过程改成ORACLE的存储过程,谢谢
- 一个实在令我想不通的问题,关于update语句更新(oracle)
- 各位兄弟,我的回退段出现问题,能否帮我看一下,感激不尽!
- Imp导入Oracle数据库时出现‘SYS.XBD_PI_TRIG’无效未通过重新验证
- 用命令把关键字“编码”中含有数字的记录筛选出来
- 关于数据库字符集的问题
触发器里不用commit;
触发器也是过程级原子性,也就是说当主体语句执行失败时,触发器也会失败;
CREATE OR REPLACE TRIGGER trigger_NewStudent
AFTER INSERT ON t_welcome_student
FOR EACH ROW --如果是行级触发器加上这一行,否则为语句级触发器
BEGIN
--如果引用t_welcome_student表的当前字段值,加上:new.
INSERT INTO t_xsjbxx_jbxx
(xh,
xm,
xznj,
sfbs,
sfzjlxm,
sfzjh,
xbdm,
yxsh,
zydm,
xsbh,
sfzj,
sfzx,
xslbdm)
SELECT :new.CID,
:new.NAME,
:new.GRADE,
'0',
'01',
:new.IDCARD,
(CASE :new.sexname
WHEN '男' THEN
'1'
ELSE
'2'
END),
:new.deptcode,
:new.specid,
(dbms_random.string('l', 8) || '-8c45-11e0-8a9a-67da650471f5'),
'01',
'01',
(CASE :new.studyyears
WHEN '4' THEN
'01'
ELSE
'02'
END)
FROM t_welcome_student
WHERE :new.CID NOT IN (SELECT xh FROM t_xsjbxx_jbxx);
dbms_output.put_line('4'); --这是输出'4'字符串应该是单引号,语句结束少了分号
END trigger_NewStudent;