1:建表
CREATE TABLE t1 (
tid VARCHAR2(20),
tname VARCHAR2(20)
)
CREATE TABLE logg(
n_id VARCHAR2(20),
n_name VARCHAR2(10),
n_value VARCHAR2(40)
)
CREATE OR REPLACE TRIGGER tri_up_del_inser
BEFORE INSERT OR UPDATE OR DELETE --insert update delete 触发
ON t1
FOR EACH ROW
DECLARE
n_tid VARCHAR2(20);
log_id VARCHAR2(20); --日志id (就是t1表的tid)
log_value VARCHAR2(40); --日志的value (就是对tname的u d i )
log_name VARCHAR2(10); -- 日志的name (INSERT UPDATE DELETE)
BEGIN
IF inserting THEN
SELECT 'TID'||lpad(substr(nvl(MAX(tid),'TID000'),4,3)+1,3,0) INTO n_tid FROM t1;
:NEW.tid := n_tid;
log_id :=n_tid;
log_name :='INSERT';
log_value := :NEW.tname;
ELSIF updating THEN
log_id :=:OLD.tid;
log_name :='UPDATE';
log_value := :NEW.tname;
ELSIF deleting THEN
log_id :=:OLD.tid;
log_name :='DELETE';
log_value := :OLD.tname;
END IF;
INSERT INTO logg VALUES(log_id,log_name,log_value);
END;
重要知识点:
:new.tid ---->表示你要新增加的记录的自动编号
:old.tname ----->表示你要删除时的tname
inserting updating deleting
if inserting then
elsif updating then
elsif deleting then
end if;