有两个表!
A表:id name age
B表: id name age time action user
在A表中执行的动作的相应内容插入到B表中。
也就是说:
A表中执行insert:
insert into A values(‘123’,‘456’,‘789’);
B表中显示:
id NAME AGE TIME ACTION USER
123 456 789 2008 INSERT SYS
同样的如果在A表中执行UPDATE或者DELETE的话,在B表中也能显示执行动作的相应内容!
望各位高手指教。最好写个触发器给我参考下!因为小弟我写了个触发器,能创建但就是不执行动作!很是恼火!先谢谢啦!
A表:id name age
B表: id name age time action user
在A表中执行的动作的相应内容插入到B表中。
也就是说:
A表中执行insert:
insert into A values(‘123’,‘456’,‘789’);
B表中显示:
id NAME AGE TIME ACTION USER
123 456 789 2008 INSERT SYS
同样的如果在A表中执行UPDATE或者DELETE的话,在B表中也能显示执行动作的相应内容!
望各位高手指教。最好写个触发器给我参考下!因为小弟我写了个触发器,能创建但就是不执行动作!很是恼火!先谢谢啦!
for each row
begin
if inserting then
insert into tt values(:new.id,:new.name,:new.age,to_char(sysdate,'yyyy'),'insert',user);
elsif updating then
insert into tt values(:new.id,:new.name,:new.age,to_char(sysdate,'yyyy'),'update',user);
elsif deleting then
insert into tt values(:new.id,:new.name,:new.age,to_char(sysdate,'yyyy'),'delete',user);
end if;
end;
谢谢你的回复,可是我一创建你的触发器就报上面的错误,不知道为什么。望解答!
create or replace trigger tr
before update or delete or insert on A
for each row
begin
if inserting then
insert into B
values
(:new.id,
:new.name,
:new.age,
to_char(sysdate, 'yyyy'),
'insert',
user);
elsif updating then
insert into B
values
(:new.id,
:new.name,
:new.age,
to_char(sysdate, 'yyyy'),
'update',
user);
elsif deleting then
insert into B
values
(:new.id,
:new.name,
:new.age,
to_char(sysdate, 'yyyy'),
'delete',
user);
end if;
end;
after insert or update or delete on test_tracker
for each row
declare
tid number(10);
begin
select count(c.id) into tid from tms_historydata c
where c.test_id = :new.test_id
and c.test_name = :new.test_name
and c.test_department = :new.test_department;.
if inserting then
if (tid <1) then
insert into test_central(test_id,test_name,test_department,
test_inserttime,state,operater)
values(:new.test_id,:new.test_name,:new.test_department,sysdate,
'insert',user);
else
update test_central set test_inserttime = sysdate,state='insert',
operater = user
where test_id =:new.test_id,test_name=:new.test.name,
test_department = new.test_department;
end if;
else if updating then
if (tid <1) then
insert into test_central(test_id,test_name,test_department,
test_inserttime,state,operater)
values(:new.test_id,:new.test_name,:new.test_department,sysdate,
'update',user);
else
update test_central set test_inserttime = sysdate,state='update',
operater = user
where test_id =:new.test_id,test_name=:new.test.name,
test_department = new.test_department;
end if;
else if deleting then
if (tid <1) then
insert into test_central(test_id,test_name,test_department,
test_inserttime,state,operater)
values(:new.test_id,:new.test_name,:new.test_department,sysdate,
'delete',user);
else
update test_central set test_inserttime = sysdate,state='delete',
operater = user
where test_id =:new.test_id,test_name=:new.test.name,
test_department = new.test_department;
end if;
exception
when others then
dbms_output.put_line('happened exception...');
end;
你能帮我看看我的这个哪里有语法错误吗》
为什么一运行就报刚刚那个错误。。
after insert or update or delete on test_tracker
for each row
declare
tid number(10);
begin
select count(c.id) into tid from tms_historydata c
where c.test_id = :new.test_id
and c.test_name = :new.test_name
and c.test_department = :new.test_department;.
if inserting then
if (tid <1) then
insert into test_central(test_id,test_name,test_department,
test_inserttime,state,operater)
values(:new.test_id,:new.test_name,:new.test_department,sysdate,
'insert',user);
else
update test_central set test_inserttime = sysdate,state='insert',
operater = user
where test_id =:new.test_id,test_name=:new.test.name,
test_department = new.test_department; --, 改成and
end if;
else if updating then
if (tid <1) then
insert into test_central(test_id,test_name,test_department,
test_inserttime,state,operater)
values(:new.test_id,:new.test_name,:new.test_department,sysdate,
'update',user);
else
update test_central set test_inserttime = sysdate,state='update',
operater = user
where test_id =:new.test_id,test_name=:new.test.name,
test_department = new.test_department;
end if;
else if deleting then
if (tid <1) then
insert into test_central(test_id,test_name,test_department,
test_inserttime,state,operater)
values(:new.test_id,:new.test_name,:new.test_department,sysdate,
'delete',user);
else
update test_central set test_inserttime = sysdate,state='delete',
operater = user
where test_id =:new.test_id,test_name=:new.test.name,
test_department = new.test_department; end if;
exception
when others then
dbms_output.put_line('happened exception...');
end;
最后多了一个点“.”
before update or delete or insert on TEST_TRACKER
for each row
declare
c number;
begin
select count(*) into c from TEST_CENTRAL where test_id = :new.test_id;
if c = 0 then
if inserting then
insert into TEST_CENTRAL
values
(:new.test_id,
:new.test_name,
:new.test_department,
sysdate,
'insert',
user);
elsif updating then
insert into TEST_CENTRAL
values
(:new.test_id,
:new.test_name,
:new.test_department,
sysdate,
'update',
user);
elsif deleting then
insert into TEST_CENTRAL
values
(:new.test_id,
:new.test_name,
:new.test_department,
sysdate,
'delete',
user);
end if;
else
if inserting then
update TEST_CENTRAL
set test_name = :new.test_name,
test_department = :new.test_department,
test_inserttime = sysdate,
state = 'insert',
operater = user
where test_id = :new.test_id;
elsif deleting then
update TEST_CENTRAL
set test_name = :new.test_name,
test_department = :new.test_department,
test_inserttime = sysdate,
state = 'delete',
operater = user
where test_id = :new.test_id;
elsif updating then
update TEST_CENTRAL
set test_name = :new.test_name,
test_department = :new.test_department,
test_inserttime = sysdate,
state = 'update',
operater = user
where test_id = :new.test_id;
end if;
end if;
end;