- 创建行级触发器
要求创建行级触发器,当在productinfo中增加数据,修改,删除时将激发该触发器,并把所做的操作记录到表log_tab中
CREATE TABLE log_tab(--建表
ID VARCHAR2(10) NOT NULL,
oper_table VARCHAR2(20),
oper_kd VARCHAR2(10),
oper_table_prk VARCHAR2(50),
oper_date DATE,
CONSTRAINT log_tab_prk PRIMARY KEY(ID)
)
CREATE SEQUENCE log_tab_id
MINVALUE 100000
MAXVALUE 999999
START WITH 100000
INCREMENT BY 1
CREATE OR REPLACE TRIGGER productinfo_oper_tgr
AFTER INSERT OR UPDATE OR DELETE
ON productinfo
FOR EACH ROW
BEGIN
CASE
WHEN inserting THEN
INSERT INTO log_tab VALUES(
log_tab_id.nextval,
'productinfo',
'insert',
:new.productid,
SYSDATE
);
dbms_output.put_line('插入数据主键是 '|| :new.productid);
WHEN updating THEN
INSERT INTO log_tab VALUES(
log_tab_id.nextval,
'productinfo',
'update',
:old.productid,
SYSDATE
);
dbms_output.put_line('修改数据主键是 '|| :old.productid);
WHEN deleting THEN
INSERT INTO log_tab VALUES(
log_tab_id.nextval,
'productinfo',
'delete',
:old.productid,
SYSDATE
);
dbms_output.put_line('删除数据主键是 '|| :old.productid);
END CASE;
END;
当插入数据时触发
insert into PRODUCTINFO (productid, productname, productprice, quantity, category, desperation, origin)
values ('8', '触发器测试', 2800, 22, '2', null, '测试');
输出结果为
插入数据主键是 8