有个表公用区电量信息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
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
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;
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;
你的日志表有主键吗?可能导致insert不成功
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;在insert语句中漏了分号!