帮个忙,帮我看看这段触发器代码!!
有个表公用区电量信息pd_gyqdlxx(gyqbh,gyqmc),想写个触发器,当表中增加(insert),删除(delete),更
新(update)时,能记录下来,记到表pd_gyqdlxx_temp表中,pd_gyqdlxx_temp
(oldgyqbh,gyqbh,gyqmc,action)
1.如果在pd_gyqdlxx表增加一条记录('0001','齐鲁变压器'),则把增加得gyqbh,gyqmc记录下来,写到temp
表中,形成('','0001','齐鲁变压器','insert')
2.如果在pd_gyqdlxx表更新一条记录('0001','齐鲁变压器')改为('0001','难坛变压器'),则把
gyqbh,gyqmc记录下来,写到temp表中,形成('0001','0001','难坛变压器','update')
3.如果在pd_gyqdlxx表删除一条记录('0001','齐鲁变压器'),则把gyqbh,gyqmc记录下来,写到temp表中,
形成('0001','','','update')
不知道触发器怎么写,我写了这么一个,不过没通过,编译就有错
create or replace trigger trigger_pdgyqdlxx
after insert or update or delete
on pd_gyqdlxx
referencing old as old_value
new as new_value
for each row
begin
if inserting then
insert into pd_gyqdlxx_tmp(oldgyqbh,gyqbh,gyqmc,action) values
('',new_value.gyqbh,new_value.gyqmc,'insert')
elseif updating then
insert into pd_gyqdlxx_tmp(oldgyqbh,gyqbh,gyqmc,action) values
(old_value.gyqbh,new_value.gyqbh,new_value.gyqmc,'update')
elseif deleting then
insert into pd_gyqdlxx_tmp(oldgyqbh,gyqbh,gyqmc,action) values
(old_value.gyqbh,'','','delete')
end if;
end
问题点数：100、回复次数：7Top
1 楼cenlmmx（学海无涯苦作舟）回复于 2006-03-01 21:43:44 得分 70
create or replace trigger trigger_pdgyqdlxx
after insert or update or delete on pd_gyqdlxx
for each row
begin
if inserting then
insert into pd_gyqdlxx_tmp(oldgyqbh,gyqbh,gyqmc,action) values('',:new.gyqbh,:new.gyqmc,'insert')
elseif updating then
insert into pd_gyqdlxx_tmp(oldgyqbh,gyqbh,gyqmc,action) values(:old.gyqbh,:new.gyqbh,:new.gyqmc,'update')
elseif deleting then
insert into pd_gyqdlxx_tmp(oldgyqbh,gyqbh,gyqmc,action) values(:old.gyqbh,'','','delete')
end if;
end;Top
2 楼tcagence（tcagence）回复于 2006-03-01 21:58:38 得分 0
不行啊,sql plus报错,警告: 创建的触发器带有编译错误Top
3 楼cenlmmx（学海无涯苦作舟）回复于 2006-03-01 22:06:29 得分 0
elseif=>elsif
elsif 可以不用,就if then endif多好,清晰.Top
4 楼tcagence（tcagence）回复于 2006-03-01 22:19:28 得分 0
我在执行create trigger得时候报 创建的触发器带有编译错误
在执行 delete from pd_gyqxx where gyqbh='b'得时候
报 触发器 'YXZDH.TRIGGER_PDGYQDLXX' 无效且未通过重新确认
不知道怎么才能正确啊Top
5 楼cenlmmx（学海无涯苦作舟）回复于 2006-03-01 22:30:24 得分 0
sql>create or replace trigger a
2 before insert or update or delete on tableA
3 for each row
4 begin
5 if inserting then
6 insert into b values (:new.id);
7 end if;
8 if updating then
9 update b set id = :new.id where id = old.id;
10 end if;
11 if deleting then
12 delete from b where id = old.id;
13 end if;
14 end;
比着改Top
6 楼lzp_lrp（lzp）回复于 2006-03-02 08:34:35 得分 30
create or replace trigger trigger_pdgyqdlxx
after insert or update or delete on pd_gyqdlxx
for each row
begin
if inserting then
insert into pd_gyqdlxx_tmp(oldgyqbh,gyqbh,gyqmc,action) values('',:new.gyqbh,:new.gyqmc,'insert')
elsif updating then
insert into pd_gyqdlxx_tmp(oldgyqbh,gyqbh,gyqmc,action) values(:old.gyqbh,:new.gyqbh,:new.gyqmc,'update')
elsif deleting then
insert into pd_gyqdlxx_tmp(oldgyqbh,gyqbh,gyqmc,action) values(:old.gyqbh,'','','delete')
end if;
end;Top
7 楼lzp_lrp（lzp）回复于 2006-03-02 08:35:05 得分 0
你的日志表有主键吗？可能导致insert不成功Top
