create trigger PERMISSION_ITEM_SYS
after insert on t_jc_xzxk_permissionitemsys
for each row --每行
declare
ID t_jc_xzxk_permissionitem.id%TYPE;--大项编号
nameed t_jc_xzxk_permissionitem.name%TYPE;--大项名称
items t_jc_xzxk_permissionitem.items%TYPE;--小项编码
status t_jc_xzxk_permissionitem.status%TYPE;--状态
itemsname t_jc_xzxk_permissionitem.itemname%TYPE;--小项名称
department t_jc_xzxk_permissionitem.department%TYPE;
begin
select s.id,s.name,s.items,s.status, s.itemname,s.department--找出大项编号,大项名称,小项编号,状态,小项名称
into ID,nameed,items,status,itemname,department--对声明的变量赋值
from t_jc_xzxk_permissionitemsys s
end
begin
insert into t_jc_xzxk_permissionitem(ID,Name,items,itemname,status,department)
values(ID,nameed,items,itemname,status,department)
COMMIT;
RETURN;
end
解决方案 »
- merge问题求助
- 批量更新的办法?
- 谁知道Orcale中给用户赋权限sql语句是?---急!!!!
- oracle作业为什么不执行
- Oracle8.16在win2000上的安装后在Enterprise Manager里没有Configuration Assistant这一项!!!??
- 【高分求书】:ORACLE8 PL/SQL程序设计(美:SCOTT URMAN)
- asp调用oracle存储过程时的recordset问题
- 请介绍sql语言好书
- 两个用户之间进行数据导入,表名一样
- oracle 如何通过一个表 更新另外一个表 update set……
- 有关sql嵌套查询的问题
- Oracle 10G启动报错,启问能否提供解决方案?
create trigger PERMISSION_ITEM_SYS
before insert on t_jc_xzxk_permissionitemsys
for each row --每行
begin
insert into t_jc_xzxk_permissionitem(ID,Name,items,itemname,status,department)
values(:new.id,:new.name,:new.items,:new.status,:new.itemname,:new.department);
--commit; 触发器中不能用commit;
end;
after insert on t_jc_xzxk_permissionitemsys
for each row --每行
declare
ID t_jc_xzxk_permissionitem.id%TYPE;--大项编号
nameed t_jc_xzxk_permissionitem.name%TYPE;--大项名称
items t_jc_xzxk_permissionitem.items%TYPE;--小项编码
status t_jc_xzxk_permissionitem.status%TYPE;--状态
itemsname t_jc_xzxk_permissionitem.itemname%TYPE;--小项名称
department t_jc_xzxk_permissionitem.department%TYPE;
begin
select s.id,s.name,s.items,s.status, s.itemname,s.department--找出大项编号,大项名称,小项编号,状态,小项名称
into ID,nameed,items,status,item[code=SQL]sname,department--对声明的变量赋值
from t_jc_xzxk_permissionitemsys s
end
begin
insert into t_jc_xzxk_permissionitem(ID,Name,items,itemname,status,department)
values(ID,nameed,items,itemsname,status,department)
--- COMMIT;
RETURN;
end
[/code]
after insert on t_jc_xzxk_permissionitemsys
for each row --每行
declare
ID t_jc_xzxk_permissionitem.id%TYPE;--大项编号
nameed t_jc_xzxk_permissionitem.name%TYPE;--大项名称
items t_jc_xzxk_permissionitem.items%TYPE;--小项编码
status t_jc_xzxk_permissionitem.status%TYPE;--状态
itemsname t_jc_xzxk_permissionitem.itemname%TYPE;--小项名称
department t_jc_xzxk_permissionitem.department%TYPE;
begin
select s.id,s.name,s.items,s.status, s.itemname,s.department--找出大项编号,大项名称,小项编号,状态,小项名称
into ID,nameed,items,status,itemsname,department--对声明的变量赋值
from t_jc_xzxk_permissionitemsys s
end
begin
insert into t_jc_xzxk_permissionitem(ID,Name,items,itemname,status,department)
values(ID,nameed,items,itemsname,status,department)
--- COMMIT;
RETURN;
end
触发器里不需要commit;
--同步把t_jc_xzxk_permissionitemsys 的插入信息插入t_jc_xzxk_permissionitem ?
create trigger PERMISSION_ITEM_SYS
before insert on t_jc_xzxk_permissionitemsys
for each row
begin
insert into t_jc_xzxk_permissionitem(ID,Name,items,itemname,status,department)
values(:new.id,:new.name,:new.items,:new.status,:new.itemname,:new.department);
end
create trigger PERMISSION_ITEM_SYS
after update on t_jc_xzxk_permissionitemsys
for each row --每行
begin
insert into t_jc_xzxk_permissionitem(ID,Name,items,itemname,status,department)
values(:new.id,:new.name,:new.items,:new.status,:new.itemname,:new.department);
end;
我的理解是把你更新的t_jc_xzxk_permissionitemsys表数据放到t_jc_xzxk_permissionitem表中
如果理解错了 你指明
create trigger PERMISSION_ITEM_SYS
after update or insert or delete on t_jc_xzxk_permissionitemsys
--相应的操作
create trigger PERMISSION_ITEM_SYS
after insert or update or delete on t_jc_xzxk_permissionitemsys
for each row
begin
when inserting then --如果是insert
insert into t_jc_xzxk_permissionitem(ID,Name,items,itemname,status,department)
values(:new.id,:new.name,:new.items,:new.status,:new.itemname,:new.department);
when updating then --如果是update
update t_jc_xzxk_permissionitem
set id=:new.id,name=:new.name,.... ;--后面的自己完成
where id=:old.id and name=:old.name...;--用主键就可以
when deleting then 如果是delete
delete t_jc_xzxk_permissionitem
where id=:old.id and name=:old.name...;--用主键就可以
end;
ID NUMBER(18,0),
Name VARCHAR2(20)
);CREATE TABLE t2(
ID NUMBER(18,0),
Name VARCHAR2(20)
);-- 触发器(功能:当向t1表插入数据的同时,向t2表插入相同的数据)
CREATE OR REPLACE TRIGGER t1_trg
AFTER INSERTON t1
FRO EACH ROW
BEGIN
INSERT INTO t2(ID, Name)
VALUES(:new.ID, :new.Name);
END;
/-- 测试:
insert into t1(id,name) values(1,'luoyoumou');
commit;select * from t1;
select * from t2;
--before insert on t_jc_xzxk_permissionitemsys
--for each row --每行
--begin
-- if INSERTING THEN
--insert into t_jc_xzxk_permissionitem(ID,Name,items,itemname,department)
--values(:new.id,:new.name,:new.items,:new.itemname,:new.department);
--ELSIF UPDATING THEN
--update t_jc_xzxk_permissionitem set name=:new.name,itemname=:new.itemname,department=:new.department
-- where id=:old.id and items=:old.items and name=:old.name and itemname=:old.itemname and department=:pld.department;
--commit; 触发器中不能用commit;
--end;
after insert OR UPDATE OR DELETE on t_jc_xzxk_busiindex
for each row
DECLARE
M_ACTION NUMBER(1); -- 此条数据表示的操作动作:1:新增 2:修改 3:删除
BEGIN IF INSERTING THEN
M_ACTION := 1;
ELSIF UPDATING THEN
......
ORA-04098: 触发器无效且未通过重新验证问题
看看具体错误
CREATE OR REPLACE TRIGGER musicloguser_incert_trig
AFTER INSERT ON musicloguser
FOR EACH ROW
DECLARE
v_a1 VARCHAR2(500);
v_c1 VARCHAR2(500);
v_c2 VARCHAR2(500);
v_e1 VARCHAR2(500);
v_k VARCHAR2(500);
v_nm VARCHAR2(500);
v_s VARCHAR2(500);
v_v1 VARCHAR2(500);
v_q2 VARCHAR2(500);
v_v2 VARCHAR2(500);
v_v3 VARCHAR2(500);
v_p2 VARCHAR2(500);
v_mobile VARCHAR2(500);
v_mintimed TIMESTAMP(6);BEGIN
v_a1 := nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<a1>.*?</a1>'),'<a1>',''),'</a1>','')),null);
v_c1 := nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<c1>.*?</c1>'),'<c1>',''),'</c1>','')),null);
v_c2 := nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<c2>.*?</c2>'),'<c2>',''),'</c2>','')),null);
v_e1 := nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<e1>.*?</e1>'),'<e1>',''),'</e1>','')),null);
v_k := nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<k>.*?</k>'),'<k>',''),'</k>','')),null);
v_nm := nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<nm>.*?</nm>'),'<nm>',''),'</nm>','')),null);
if v_nm like '%&#x%' then
v_nm := unistr(REPLACE(REPLACE(v_nm,'&#x','\'),';',''));
end if;
v_s := nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<s>.*?</s>'),'<s>',''),'</s>','')),null);
if v_s like '%&#x%' then
v_s := unistr(REPLACE(REPLACE(v_s,'&#x','\'),';',''));
end if;
v_v1 := nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<v1>.*?</v1>'),'<v1>',''),'</v1>','')),null);
if v_v1 like '%&#x%' then
v_v1 := unistr(REPLACE(REPLACE(v_v1,'&#x','\'),';',''));
end if;
v_q2 := nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<q2>.*?</q2>'),'<q2>',''),'</q2>','')),null);
v_v2 := nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<v2>.*?</v2>'),'<v2>',''),'</v2>','')),null);
if v_v2 like '%&#x%' then
v_v2 := unistr(REPLACE(REPLACE(v_v2,'&#x','\'),';',''));
end if;
v_v3 := nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<v3>.*?</v3>'),'<v3>',''),'</v3>','')),null);
if v_v3 like '%&#x%' then
v_v3 := unistr(REPLACE(REPLACE(v_v3,'&#x','\'),';',''));
end if;
v_p2 := nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<p2>.*?</p2>'),'<p2>',''),'</p2>','')),null); IF v_a1 IS NOT NULL THEN
IF v_e1 IS NOT NULL THEN
v_mobile := v_e1;
ELSE
SELECT e1 INTO v_mobile
FROM (SELECT DISTINCT a1, e1, 1 AS ords
FROM musicloguser_detail
WHERE e1 is not null
AND a1= v_a1
UNION ALL
SELECT NULL AS a1, NULL as e1, 0 AS ords FROM DUAL) t
WHERE rownum=1;
END IF;
END IF; INSERT INTO musicloguser_detail(C1,R2,C2,K1,E1,U1,T1,K,T,F,G,W,NM,S,V1,O1,FNN,PID,A1,B1,Q2,V2,V3,AC,MN,Z,P2,LOGID,TIMED)
VALUES( v_c1,
nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<r2>.*?</r2>'),'<r2>',''),'</r2>','')),null),
v_c2,
nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<k1>.*?</k1>'),'<k1>',''),'</k1>','')),null),
v_e1,
nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<u1>.*?</u1>'),'<u1>',''),'</u1>','')),null),
nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<t1>.*?</t1>'),'<t1>',''),'</t1>','')),null),
v_k,
nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<t>.*?</t>'),'<t>',''),'</t>','')),null),
nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<f>.*?</f>'),'<f>',''),'</f>','')),null),
nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<g>.*?</g>'),'<g>',''),'</g>','')),null),
nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<w>.*?</w>'),'<w>',''),'</w>','')),null),
v_nm,v_s,v_v1,
nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<o1>.*?</o1>'),'<o1>',''),'</o1>','')),null),
nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<fn>.*?</fn>'),'<fn>',''),'</fn>','')),null),
nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<pid>.*?</pid>'),'<pid>',''),'</pid>','')),null),
v_a1,
nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<b1>.*?</b1>'),'<b1>',''),'</b1>','')),null),
v_q2,v_v2,v_v3,
nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<ac>.*?</ac>'),'<ac>',''),'</ac>','')),null),
nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<mn>.*?</mn>'),'<mn>',''),'</mn>','')),null),
nvl(trim(replace(replace(REGEXP_SUBSTR(:new.sxml,'<z>.*?</z>'),'<z>',''),'</z>','')),null),
v_p2,:new.logid,:new.timed); IF v_k = 'userlogin' AND v_a1 IS NOT NULL AND v_q2 IS NOT NULL AND v_p2 IS NOT NULL THEN
INSERT INTO musicinstalllog(logid,mobile,imsi,cid,pid,ver,logcreationdate,phonetype)
VALUES (:new.logid,v_mobile,v_a1,v_q2,v_p2,v_c1,:new.timed,v_c2);
END IF;
END;
/
create trigger PERMISSION_ITEM_SYS
after insert or update or delete on t_jc_xzxk_permissionitemsys
for each row
begin
case
when inserting then --如果是insert
insert into t_jc_xzxk_permissionitem(ID,Name,items,itemname,status,department)
values(:new.id,:new.name,:new.items,:new.status,:new.itemname,:new.department);
when updating then --如果是update
update t_jc_xzxk_permissionitem
set id=:new.id,name=:new.name,.... ;--后面的自己完成
where id=:old.id and name=:old.name...;--用主键就可以
when deleting then 如果是delete
delete t_jc_xzxk_permissionitem
where id=:old.id and name=:old.name...;--用主键就可以
end case;
end;
from t_jc_xzxk_permissionitemsys s
你的这条查出的数据是否只有一条啊
你的这个写法应该算个procedure了 呵呵
-- 给个例子给你:
CREATE OR REPLACE TRIGGER IMSI2MOBILE_UserPoints_trig
AFTER INSERT OR UPDATE OR DELETE ON imsi2mobile
FOR EACH ROW
DECLARE
v_singlepoints NUMBER(18);
BEGIN
IF UPDATING THEN
UPDATE UserPoints SET mobile=:new.mobile WHERE mobile=:old.mobile;
ELSIF DELETING THEN
DELETE FROM UserPoints WHERE mobile=:old.mobile;
ELSIF INSERTING THEN
SELECT singlepoints INTO v_singlepoints FROM opera_points_map WHERE useropera='TO_REGISTER';
INSERT INTO UserPoints(mobile, usertype, points,grade, cdate) VALUES(:new.mobile,1,v_singlepoints,1,:new.cdate);
END IF;
END;
/
create trigger PERMISSION_ITEM_SYS
after insert or update or delete on t_jc_xzxk_permissionitemsys
for each row
begin
if inserting then --如果是insert
insert into t_jc_xzxk_permissionitem(ID,Name,items,itemname,status,department)
values(:new.id,:new.name,:new.items,:new.status,:new.itemname,:new.department);
elsif updating then --如果是update
update t_jc_xzxk_permissionitem
set id=:new.id,name=:new.name,.... ;--后面的自己完成
where id=:old.id and name=:old.name...;--用主键就可以
elsif deleting then 如果是delete
delete t_jc_xzxk_permissionitem
where id=:old.id and name=:old.name...;--用主键就可以
end if;
end;
begin case declare else
elsif end exit for goto if loop mod null pragma raise return
select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
行:11
文本:elsif updating then错误:PLS-00103: 出现符号 ","在需要下列之一时:
. ( * @ % & - + ; / at for
mod remainder rem <an exponent (**)> and or group having
intersect minus order start union where connect || indicator
multiset
行:11
文本:elsif updating then错误:PLS-00103: 出现符号 ";"在需要下列之一时:
. ( ) , * @ % & - + / at mod
remainder rem <an exponent (**)> and or || indicator multiset
行:11
文本:elsif updating then
begin case declare else
elsif end exit for goto if loop mod null pragma raise return
select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
行:11
文本:where id=:old.id and name=:old.name and items=:old.items and itemname=:old.itemname and department=:old.department;--用主键就可以错误:PLS-00103: 出现符号 "ELSIF"
行:12
文本:elsif deleting then --如果是delete
after insert or update or delete on t_jc_xzxk_permissionitemsys
for each row
begin
if inserting then --如果是insert
insert into t_jc_xzxk_permissionitem(ID,Name,items,itemname,department)
values(:new.id,:new.name,:new.items,:new.itemname,:new.department);
elsif updating then --如果是update
update t_jc_xzxk_permissionitem
set id=:new.id,name=:new.name,items=:new.items,itemname=:new.itemname,department=:new.department;--后面的自己完成
where id=:old.id and name=:old.name and items=:old.items and itemname=:old.itemname and department=:old.department;--用主键就可以
elsif deleting then --如果是delete
delete t_jc_xzxk_permissionitem
where id=:old.id and name=:old.name and items=:old.items and itemname=:old.itemname and department=:old.department;--用主键就可以
end if;
end;