角本:
CREATE TABLE tb_SendSH (
SC_TIME Date not null,
TC_TIME date not null,
TEL varchar2 (12) not null,
COMMAND varchar2 (512) not null,
TRANS number not null,
SC_ID number not null,
G2T_SQ number not null,
T2M_SQ number not null,
RETRY number
);
CREATE TABLE tb_SendSjz (
SC_TIME date NOT NULL ,
TC_TIME date NOT NULL ,
TEL varchar2 (12) NOT NULL ,
COMMAND varchar2 (512) NOT NULL ,
TRANS number NOT NULL ,
SC_ID number NOT NULL ,
G2T_SQ number NOT NULL ,
T2M_SQ number NOT NULL ,
RETRY number NOT NULL
);
CREATE TABLE tb_SimNumber (
ID number(1,1) NOT NULL ,
TEL varchar2 (12) NOT NULL ,
ADDTIME date NOT NULL ,
TYPEID number NOT NULL
);CREATE or replace Trigger Trig_Input_tb_SendSH
before insert On tb_SendSH
beginif (SELECT count(*) FROM tb_SimNumber INNER JOIN inserted ON tb_SimNumber.TEL = inserted.TEL) = 0
BEGIN
insert into tb_SendSjz(SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry) select SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry from inserted ENDelse
BEGIN
insert into tb_SendSH(SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry) select SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry from inserted
END
end;
建表之后,建立这个前触发器,总是提示" 无法在表中创建 INSTEAD OF 触发器",这个是什么问题啊
CREATE TABLE tb_SendSH (
SC_TIME Date not null,
TC_TIME date not null,
TEL varchar2 (12) not null,
COMMAND varchar2 (512) not null,
TRANS number not null,
SC_ID number not null,
G2T_SQ number not null,
T2M_SQ number not null,
RETRY number
);
CREATE TABLE tb_SendSjz (
SC_TIME date NOT NULL ,
TC_TIME date NOT NULL ,
TEL varchar2 (12) NOT NULL ,
COMMAND varchar2 (512) NOT NULL ,
TRANS number NOT NULL ,
SC_ID number NOT NULL ,
G2T_SQ number NOT NULL ,
T2M_SQ number NOT NULL ,
RETRY number NOT NULL
);
CREATE TABLE tb_SimNumber (
ID number(1,1) NOT NULL ,
TEL varchar2 (12) NOT NULL ,
ADDTIME date NOT NULL ,
TYPEID number NOT NULL
);CREATE or replace Trigger Trig_Input_tb_SendSH
before insert On tb_SendSH
beginif (SELECT count(*) FROM tb_SimNumber INNER JOIN inserted ON tb_SimNumber.TEL = inserted.TEL) = 0
BEGIN
insert into tb_SendSjz(SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry) select SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry from inserted ENDelse
BEGIN
insert into tb_SendSH(SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry) select SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry from inserted
END
end;
建表之后,建立这个前触发器,总是提示" 无法在表中创建 INSTEAD OF 触发器",这个是什么问题啊
CREATE or replace Trigger Trig_Input_tb_SendSH
before insert On tb_SendSH
后面加for each row;
还有if then 后面不需要加begin。。end;
语法是if then ..;
else ..;
end if;
before of insert on tb_SendSH
for each row
begin
if (SELECT count(*) FROM tb_SimNumber INNER JOIN inserted ON tb_SimNumber.TEL = inserted.TEL) = 0
insert into tb_SendSjz(SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry)
select SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry from inserted
else
insert into tb_SendSH(SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry)
select SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry from inserted
end if
end Trig_Input_tb_SendSH;写成这个样子了,可是还是不行,还是提示 "无效的触发器类型"
before of insert on tb_SendSH
for each row
begin
if (SELECT count(*) FROM tb_SimNumber INNER JOIN inserted ON tb_SimNumber.TEL = inserted.TEL) = 0
insert into tb_SendSjz(SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry)
values (:NEW.SC_TIME,:NEW.TC_TIME,:NEW.Tel,:NEW.command,:NEW.trans,:NEW.sc_id,:NEW.g2t_sq,:NEW.t2m_sq,:NEW.retry );
else
insert into tb_SendSH(SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry)
values (:NEW.SC_TIME,:NEW.TC_TIME,:NEW.Tel,:NEW.command,:NEW.trans,:NEW.sc_id,:NEW.g2t_sq,:NEW.t2m_sq,:NEW.retry );
end if
end Trig_Input_tb_SendSH;
before of insert on tb_SendSH
for each row
begin
if (SELECT count(*) FROM tb_SimNumber INNER JOIN inserted ON tb_SimNumber.TEL = inserted.TEL) = 0 then
insert into tb_SendSjz(SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry)
values (:NEW.SC_TIME,:NEW.TC_TIME,:NEW.Tel,:NEW.command,:NEW.trans,:NEW.sc_id,:NEW.g2t_sq,:NEW.t2m_sq,:NEW.retry );
else
insert into tb_SendSH(SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry)
values (:NEW.SC_TIME,:NEW.TC_TIME,:NEW.Tel,:NEW.command,:NEW.trans,:NEW.sc_id,:NEW.g2t_sq,:NEW.t2m_sq,:NEW.retry );
end if
end ;
create or replace trigger trigger_name
before/after /*二者选一*/
insert/update/delete /*三者选一*/
on table_name /*on 表名*/
[for each rom[when conidition]] /*[]内的可选*/
begin
[if.....then] /*根据需要写*/
commd; /*要写的内容*/
[else]
commd;
[endif;]
end;
before insert On tb_SendSH
for each row
declare
inta number(5);
begin
SELECT count(*)
into inta
FROM tb_SimNumber
where tb_SimNumber.TEL = :new.TEL;
if inta = 0 then
begin
insert into tb_SendSjz (SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry)
values(
:new.SC_TIME,:new.TC_TIME,:new.Tel,:new.command,
:new.trans,:new.sc_id,:new.g2t_sq,:new.t2m_sq,:new.retry);
null;
END;
else
BEGIN
insert into tb_SendSH
(SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry)
values(
:new.SC_TIME,:new.TC_TIME,:new.Tel,:new.command,
:new.trans,:new.sc_id,:new.g2t_sq,:new.t2m_sq,:new.retry) ;
END;
end if;
end;
insert into tb_simnumber values(1,'01-dec-98','12345',2);
'无匹配的记录(这条运行正常 ,但是二个Send表各被插入了一条)
insert into tb_sendsh values('01-dec-98','01-dec-98','123456','123456789',1,2,3,4,5);'有匹配的记录(这条运行后,执行错误,原因: 超过递归 SQL 级别的最大值 50)
insert into tb_sendsh values('01-dec-98','01-dec-98','12345','123456789',1,2,3,4,5);
create or replace trigger trigger_name
before/after/
insert/update/delete
on table_name
[for each row[when condition]]
begin
[if'''then]
commd;
[else]
commd
[endif;]
end 学习了,谢谢楼上的..
before insert On tb_SendSH
for each row
declare
inta number(5);
begin
SELECT count(*)
into inta
FROM tb_SimNumber
where tb_SimNumber.TEL = :new.TEL;
if inta = 0 then
begin
insert into tb_SendSjz (SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry)
values(
:new.SC_TIME,:new.TC_TIME,:new.Tel,:new.command,
:new.trans,:new.sc_id,:new.g2t_sq,:new.t2m_sq,:new.retry);
null;
END;
else
BEGIN
insert into tb_SendSH
(SC_TIME,TC_TIME,Tel,command,trans,sc_id,g2t_sq,t2m_sq,retry)
values(
:new.SC_TIME,:new.TC_TIME,:new.Tel,:new.command,
:new.trans,:new.sc_id,:new.g2t_sq,:new.t2m_sq,:new.retry) ;
END;
end if;
end;
执行了一些操作而已。
你可以把触发器改成after的,然后在else中delete
ORA-06512: 在 "SYSTEM.TRIG_INPUT_TB_SENDSH", line 21
ORA-04088: 触发器 'SYSTEM.TRIG_INPUT_TB_SENDSH' 执行过程中出错