当表record_daiban的COMPLATE_STATUTS字段变为1时,将修改的记录插入到另外一个日志表里。然后删除当前记录create or replace trigger trg_upd_daiban
after update
of COMPLATE_STATUTS
ON record_daiban
FOR EACH ROW
BEGIN
if :new.COMPLATE_STATUTS=1 then
insert into record_log
SELECT * FROM record_daiban t
where t.complate_statuts='1' and t.id=:new.id; delete from record_daiban t where t.complate_statuts='1' and t.id=:new.id;
end if;
END trg_upd_daiban;
after update
of COMPLATE_STATUTS
ON record_daiban
FOR EACH ROW
BEGIN
if :new.COMPLATE_STATUTS=1 then
insert into record_log
SELECT * FROM record_daiban t
where t.complate_statuts='1' and t.id=:new.id; delete from record_daiban t where t.complate_statuts='1' and t.id=:new.id;
end if;
END trg_upd_daiban;
pragma autonomous_transaction
--搞个自治事务 PRAGMA AUTONOMOUS_TRANSACTION;create or replace trigger trg_upd_daiban
after update
of COMPLATE_STATUTS
ON record_daiban
FOR EACH ROW
declare
pragma autonomous_transaction;
BEGIN
if :new.COMPLATE_STATUTS=1 then
insert into record_log
SELECT * FROM record_daiban t
where t.complate_statuts='1' and t.id=:new.id; delete from record_daiban t where t.complate_statuts='1' and t.id=:new.id;
end if;
END trg_upd_daiban
DROP TABLE test;
CREATE TABLE test
(a NUMBER(2));
CREATE OR REPLACE TRIGGER TR_TEST
BEFORE INSERT ON TEST
FOR EACH ROW
DECLARE
-- local variables here
v_count NUMBER(2);
BEGIN
SELECT COUNT(*) INTO v_count FROM test WHERE a=:new.a;
IF v_count>0 THEN
DELETE FROM test WHERE a=:new.a;
END IF;
END TR_TEST;INSERT INTO test
VALUES(1);
INSERT INTO test
VALUES(2);
INSERT INTO test
VALUES(1);
SELECT * FROM test;
正常的trigger加commit都报错
麻烦改下我的代码,我改了好久都不能正常insert
trigger里的before改成after
pragma autonomous_transaction
DROP TABLE test;
CREATE TABLE test
(a NUMBER(2));
CREATE OR REPLACE TRIGGER TR_TEST
AFTER INSERT ON TEST
FOR EACH ROW
DECLARE
v_count NUMBER(2);
pragma autonomous_transaction;
BEGIN SELECT COUNT(*) INTO v_count FROM test WHERE a=:new.a;
IF v_count>0 THEN
DELETE FROM test WHERE a=:new.a;
END IF;
COMMIT;
END TR_TEST;INSERT INTO test
VALUES(1);
INSERT INTO test
VALUES(2);
INSERT INTO test
VALUES(1);