create OR REPLACE trigger trans_code_t
after
insert
on EMR_ICH
FOR EACH row;
BEGIN
IF inserting THEN
trans_code_p(EMR); --调用存储过程
END IF;
END;
create or replace procedure trans_code_p(TABLE_NAME in varchar(100))as
VORG_CODE VARCHAR(100);
V_SQL VARCHAR2(1000);
NEWORG_CODE VARCHAR(100);Begin
VORG_CODE := (SELECT ORG_CODE FROM TABLE_NAME where rownum < 2 ); --获取插入记录的ORG_CODE字段的值
IF VORG_CODE = '371721A30001' --插入的值
THEN
NEWORG_CODE := '173023809'; --设置新值
V_SQL := 'UPDATE ' || TABLE_NAME || 'SET' || 'ORG_CODE' || '=' || NEWORG_CODE;
EXECUTE IMMEDIATE(V_SQL);
END;
End;
报错,很多错
after
insert
on EMR_ICH
FOR EACH row;
BEGIN
IF inserting THEN
trans_code_p(EMR); --调用存储过程
END IF;
END;
create or replace procedure trans_code_p(TABLE_NAME in varchar(100))as
VORG_CODE VARCHAR(100);
V_SQL VARCHAR2(1000);
NEWORG_CODE VARCHAR(100);Begin
VORG_CODE := (SELECT ORG_CODE FROM TABLE_NAME where rownum < 2 ); --获取插入记录的ORG_CODE字段的值
IF VORG_CODE = '371721A30001' --插入的值
THEN
NEWORG_CODE := '173023809'; --设置新值
V_SQL := 'UPDATE ' || TABLE_NAME || 'SET' || 'ORG_CODE' || '=' || NEWORG_CODE;
EXECUTE IMMEDIATE(V_SQL);
END;
End;
报错,很多错
create or replace procedure trans_code_p(table_name in varchar(100))
as
vorg_code varchar(100);
v_sql varchar2(1000);
neworg_code varchar(100);
begin
v_sql := 'select org_code from '||table_name||' where rownum < 2';
execute immediate v_sql into vorg_code;
if vorg_code = '371721A30001' then
neworg_code := '173023809'; --设置新值
v_sql := 'update ' || table_name || ' set org_code ' || ' = ' || neworg_code;
execute immediate v_sql;
commit;
end;
end;
你的触发器是行级触发,如果非要单独出来存储过程,拿那也是建议,把 :new.ORG_CODE 这个值以参数形式传递到存储过程里面,而不是再次获取