我有两张表,想要写一个触发器,当对一个表进行操作时,另一张表进行相应的操作,这两张表是相同或者相似的表,例如
create table STUDENTINFO
(
ID INTEGER not null,
NAME VARCHAR2(32) not null,
AGE INTEGER,
INPUT INTEGER,
ADRESS VARCHAR2(128) not null,
REMARK VARCHAR2(32),
);
alter table VNMP_CAMERAINFO add primary key (CAMERAID);
create table STUDENTINFOA
(
ID INTEGER not null,
NAME VARCHAR2(32) not null,
AGE INTEGER,
INPUT INTEGER,
ADRESS VARCHAR2(128) not null,
REMARK VARCHAR2(32),
GREAD INTEGER,
);
alter table VNMP_CAMERAINFO add primary key (CAMERAID);如何当增加、删除、修改STUDENTINFO时,STUDENTINFOA也做相应的改变。尤其是对STUDENTINFO进行修改操作时如何写触发器,注意STUDENTINFO表中除了ID以外,其他字段都有可能进行了修改!给出实例。
谢谢!
create table STUDENTINFO
(
ID INTEGER not null,
NAME VARCHAR2(32) not null,
AGE INTEGER,
INPUT INTEGER,
ADRESS VARCHAR2(128) not null,
REMARK VARCHAR2(32),
);
alter table VNMP_CAMERAINFO add primary key (CAMERAID);
create table STUDENTINFOA
(
ID INTEGER not null,
NAME VARCHAR2(32) not null,
AGE INTEGER,
INPUT INTEGER,
ADRESS VARCHAR2(128) not null,
REMARK VARCHAR2(32),
GREAD INTEGER,
);
alter table VNMP_CAMERAINFO add primary key (CAMERAID);如何当增加、删除、修改STUDENTINFO时,STUDENTINFOA也做相应的改变。尤其是对STUDENTINFO进行修改操作时如何写触发器,注意STUDENTINFO表中除了ID以外,其他字段都有可能进行了修改!给出实例。
谢谢!
ON STUDENTINFO
FOR EACH ROW
BEGIN
IF :NEW.AGE <> :OLD.AGE THEN
update STUDENTINFOA set age = :NEW.AGE
where id = :old.id;
END IF;
END Trg_STUDENTINFO_UPDATE ;
CREATE OR REPLACE TRIGGER Trg_STUDENTINFO_UPDATE AFTER UPDATE
ON STUDENTINFO
FOR EACH ROW
BEGIN
IF :NEW.NAME <> :OLD.NAME THEN
update STUDENTINFOA set name = :NEW.NAME
where id = :old.id;
END IF;
IF :NEW.AGE <> :OLD.AGE THEN
update STUDENTINFOA set age = :NEW.AGE
where id = :old.id;
END IF;
IF :NEW.INPUT <> :OLD.INPUT THEN
update STUDENTINFOA set INPUT = :NEW.INPUT
where id = :old.id;
END IF;
IF :NEW.ADRESS <> :OLD.ADRESS THEN
update STUDENTINFOA set ADRESS = :NEW.ADRESS
where id = :old.id;
END IF;
IF :NEW.REMARK <> :OLD.REMARK THEN
update STUDENTINFOA set REMARK = :NEW.REMARK
where id = :old.id; END Trg_STUDENTINFO_UPDATE ;
CREATE OR REPLACE TRIGGER Trg_STUDENTINFO_UPDATE AFTER UPDATE
ON STUDENTINFO
FOR EACH ROW
BEGIN update STUDENTINFOA set name = :NEW.NAME,AGE = :NEW.AGE,INPUT = :NEW.INPUT,ADRESS = :NEW.ADRESS,REMARK=:NEW.REMARK
where id = :old.id;
END Trg_STUDENTINFO_UPDATE ;
after insert
on STUDENTINFO
for each row
begin
insert into STUDENTINFOA values(:new.ID, :new.NAME, :new.AGE, :new.INPUT, :new.ADRESS, :new.REMARK,NULL);
end;
/create or replace trigger tr_STUDENTINFO_D
after delete
on STUDENTINFO
for each row
begin
delete from STUDENTINFOA where ID = :old.ID;
end;
/create or replace trigger tr_STUDENTINFO_M
after update
on STUDENTINFO
for each row
begin
update STUDENTINFOA set NAME=:new.NAME, AGE=:new.AGE, INPUT=:new.INPUT, ADRESS=:new.ADRESS, REMARK=:new.REMARK
where ID = :old.ID;
end;
/
end if ;exception
when others then
rollback ;
end ;
create or replace trigger aiudfer_studentbak
after insert or update or delete
on studentbak
for each row
begin
if inserting then
insert into studentbak values(:new.ID, :new.NAME, :new.AGE, :new.INPUT);
elsif updating then
update studentbak set name=:new.NAME, age=:new.AGE, input=:new.INPUT where id = :old.ID;
elsif deleting then
delete from studentbak where ID = :old.ID;
end if;
end;