--创建触发器
Create or replace trigger biu_gqzydp_attachment
Before insert or update On mwt_om_fsdata
for each row
when (new.attr_id = '4DC345D9-071F-4534-B1C1-B43A93D1C988')
declare
--定义变量
user_name varchar2(64);
action varchar2(64);
begin
--从另外一张表获取用户姓名
SELECT USER_WID INTO USER_NAME FROM MWT_OM_OBJ WHERE OBJ_ID = :NEW.OBJ_ID;
--记录操作类型
CASE
WHEN inserting THEN
action := 'I';
WHEN updating THEN
action := 'U';
END CASE;
--更新日志记录
insert into bhdzd_log
(obj_id, attr_id, modify_time, modify_type,username)
values
(:new.obj_id, :new.attr_id, sysdate, action,user_name);
end biud_gqzydp_attachment;
Create or replace trigger biu_gqzydp_attachment
Before insert or update On mwt_om_fsdata
for each row
when (new.attr_id = '4DC345D9-071F-4534-B1C1-B43A93D1C988')
declare
--定义变量
user_name varchar2(64);
action varchar2(64);
begin
--从另外一张表获取用户姓名
SELECT USER_WID INTO USER_NAME FROM MWT_OM_OBJ WHERE OBJ_ID = :NEW.OBJ_ID;
--记录操作类型
CASE
WHEN inserting THEN
action := 'I';
WHEN updating THEN
action := 'U';
END CASE;
--更新日志记录
insert into bhdzd_log
(obj_id, attr_id, modify_time, modify_type,username)
values
(:new.obj_id, :new.attr_id, sysdate, action,user_name);
end biud_gqzydp_attachment;
解决方案 »
- oracle中的一个触发器错误。
- 异构数据库移植
- ORACLE JOB 问题,请高手帮忙修改一下,先谢过了
- 求以sql
- 关于往一个表中插入blob数据时引发的问题~~~~
- 请问题出在哪里了呢?
- 远程连接一个oracle数据库 监听该怎么配??
- 各位大哥,Oracle动态修改值的的sql语句为什么出错呢???
- 关于排序问题,为何顺序不对呢
- 求助oracle执行慢
- 我在ORACLE官方网站上看到Oracle 9i的大小是1.7个G左右,11g的大小也是1.7G左右,为什么我要下载的10g for vista只有600多M呢?
- 当文本字节超过4096只能用BOLB类型吗?文本存入BOLB类型后能直接看到明码吗?
/--创建触发器
Create or replace trigger biu_gqzydp_attachment
Before insert or update On mwt_om_fsdata
for each row
/--不需要, when (new.attr_id = '4DC345D9-071F-4534-B1C1-B43A93D1C988')
declare
/--定义变量
user_name varchar2(64);
action varchar2(64);
begin
/--从另外一张表获取用户姓名
SELECT USER_WID INTO USER_NAME FROM MWT_OM_OBJ WHERE OBJ_ID = :NEW.OBJ_ID;
/--记录操作类型
CASE
WHEN inserting THEN
action := 'I';
WHEN updating THEN
action := 'U';
END CASE;
/--更新日志记录
insert into bhdzd_log
(obj_id, attr_id, modify_time, modify_type,username)
values
(:new.obj_id, :new.attr_id, sysdate, action,user_name);
end biud_gqzydp_attachment;
INTO action
FROM dual;----------------------------------------
--记录操作类型
CASE
WHEN inserting THEN
action := 'I';
WHEN updating THEN
action := 'U';
END CASE;
------------------------------------------
还是不行 编译没有提示错误 但是 pl/sql中的目录树中的触发器名字上面有个红叉叉
你对我的触发器没有修改呀 除了那个when条件,为什么不需要呀?--创建触发器
Create or replace trigger biu_gqzydp_attachment
Before insert or update On mwt_om_fsdata
for each row
when (new.attr_id = '4DC345D9-071F-4534-B1C1-B43A93D1C988')
declare
--定义变量
user_name varchar2(64);
action varchar2(64);
begin
--更新日志记录
insert into bhdzd_log
(obj_id, attr_id, modify_time)
values
(:new.obj_id, :new.attr_id, sysdate);
end biud_gqzydp_attachment;
假若是这样的 能够过滤不需要的触发呀 运行起来正确
假若我这么写:--创建触发器
Create or replace trigger biu_gqzydp_attachment
Before insert or update On mwt_om_fsdata
for each row
when (new.attr_id = '4DC345D9-071F-4534-B1C1-B43A93D1C988')
declare
--定义变量
user_name varchar2(64);
action varchar2(64);
begin
--记录操作类型
CASE
WHEN inserting THEN
insert into bhdzd_log
(obj_id, attr_id, modify_time, modify_type)
values
(:new.obj_id, :new.attr_id, sysdate, 'I');
WHEN updating THEN
insert into bhdzd_log
(obj_id, attr_id, modify_time, modify_type)
values
(:new.obj_id, :new.attr_id, sysdate, 'U');
END CASE;
end biud_gqzydp_attachment;
运行起来也是完全正确的
2 v_x varchar2(20);
3 begin
4 case when 1=1 then
5 v_x := '1=1';
6 else
7 v_x := '1<>1';
8 end;
9 dbms_output.put_line(v_x);
10 end;
11 /
end;
*
第 8 行出现错误:
ORA-06550: 第 8 行, 第 8 列:
PLS-00103: 出现符号 ";"在需要下列之一时:
case
符号 "case" 被替换为 ";" 后继续。
scott@STUDY> declare
2 v_x varchar2(20);
3 begin
4 select case
5 when 1=1 then '1=1'
6 else '1<>1'
7 end
8 into v_x
9 from dual;
10 dbms_output.put_line(v_x);
11 end;
12 /
1=1PL/SQL 过程已成功完成。scott@STUDY> declare
2 v_x varchar2(20);
3 begin
4 case
5 when 1=1 then dbms_output.put_line('1=1');
6 else dbms_output.put_line('1<>1');
7 end;
8 end;
9 /
end;
*
第 7 行出现错误:
ORA-06550: 第 7 行, 第 7 列:
PLS-00103: 出现符号 ";"在需要下列之一时:
case
符号 "case" 被替换为 ";" 后继续。
--创建触发器
Create or replace trigger biu_gqzydp_attachment
Before insert or update On mwt_om_fsdata
for each row
when (new.attr_id = '4DC345D9-071F-4534-B1C1-B43A93D1C988')
declare
--定义变量
user_name varchar2(64);
action varchar2(64);
begin
--记录操作类型
SELECT case when inserting then 'I' when updating then 'U' end case
INTO action
FROM dual;
--更新记录
insert into bhdzd_log
(obj_id, attr_id, modify_time, modify_type)
values
(:new.obj_id, :new.attr_id, sysdate, action);
end biud_gqzydp_attachment;
那我这么写 编译器来没有问题 但是trigger目录下的触发器名字上面有个红叉叉
再弱弱问一下 怎么在command窗口执行编译触发器呀
--创建触发器
Create or replace trigger biu_gqzydp_attachment
Before insert or update On mwt_om_fsdata
for each row
when (new.attr_id = '4DC345D9-071F-4534-B1C1-B43A93D1C988')
declare
--定义变量
user_name varchar2(64);
action varchar2(64);
begin
--记录操作类型
SELECT case when inserting then 'I' when updating then 'U' end case
INTO action
FROM dual;
--更新记录
insert into bhdzd_log
(obj_id, attr_id, modify_time, modify_type)
values
(:new.obj_id, :new.attr_id, sysdate, action);
end biud_gqzydp_attachment;
在pl/sql的trigger编辑界面中去编译,提示这样的错误:Compilation errors for TRIGGER MW_SYS.BIU_GQZYDP_ATTACHMENTError: PL/SQL: ORA-00920: invalid relational operator
Line: 12
Text: INTO actionError: PL/SQL: SQL Statement ignored
Line: 12
Text: INTO actionError: Hint: Variable 'user_name' is declared but never used in 'biu_gqzydp_attachment'
Line: 8
Text: action varchar2(64);
--创建触发器
Create or replace trigger biu_gqzydp_attachment
Before insert or update On mwt_om_fsdata
for each row
when (new.attr_id = '4DC345D9-071F-4534-B1C1-B43A93D1C988')
declare
--定义变量
action varchar2(64);
begin
--记录操作类型
if inserting then
action = 'I';
elsif updating then
action = 'U';
end if;
--更新记录
insert into bhdzd_log
(obj_id, attr_id, modify_time, modify_type)
values
(:new.obj_id, :new.attr_id, sysdate, action);
end biud_gqzydp_attachment;
--创建触发器
Create or replace trigger biu_gqzydp_attachment
Before insert or update On mwt_om_fsdata
for each row
when (new.attr_id = '4DC345D9-071F-4534-B1C1-B43A93D1C988')
declare
--定义变量
user_name varchar2(64);
action varchar2(64);
begin
--记录操作类型
if inserting then
action := 'I';
elsif updating then
action := 'U';
end if;
--更新记录
insert into bhdzd_log
(obj_id, attr_id, modify_time, modify_type)
values
(:new.obj_id, :new.attr_id, sysdate, action);
end biud_gqzydp_attachment;