有两张表,一个是产品表PRODUCTS,一个是产品的畅销表POPULAR_PRODUCTS,怎么才能实现产品的名字被修改了后,在产品畅销表中及时的去更新产品的名字呢?这个时候我们就用到了触发器,废话不说了先创建这些表
CREATE TABLE PRODUCTS(product_id NUMBER, product_name VARCHAR2(120));
CREATE TABLE POPULAR_PRODUCTS(id NUMBER , product_id NUMBER , product_name VARCHAR2(120));
插入几条简单数据
INSERT INTO PRODUCTS VALUES (1, '冰箱');
INSERT INTO PRODUCTS VALUES (2, '空调');
INSERT INTO PRODUCTS VALUES (3, '电脑');
INSERT INTO POPULAR_PRODUCTS VALUES (1, 2, '空调');
INSERT INTO POPULAR_PRODUCTS VALUES (2, 3, '电脑');
建立触发器
CREATE OR REPLACE TRIGGER TR_PRODUCT
AFTER UPDATE ON PRODUCTS
FOR EACH ROW
BEGIN
UPDATE POPULAR_PRODUCTS
SET PRODUCT_NAME = :NEW.PRODUCT_NAME
WHERE PRODUCT_ID = :NEW.PRODUCT_ID;
END;
接着去修改PRODUCTS 表中的信息
UPDATE PRODUCTS
SET PRODUCT_NAME = 'strivebo'
WHERE PRODUCT_ID = '2';
COMMIT;
提交之后你在查询表数据
SELECT * FROM PRODUCTS;
SELECT * FROM POPULAR_PRODUCTS;
至此完成一个简单的触发器。
========MODIFY TO 2012-03-18 ======
进一步修改下:如果不想要在周六、周日 甚至某个时间去修改PRODUCTS表中的信息,那怎么办呢?下边的触发器可以帮你解决
CREATE OR REPLACE TRIGGER DML_PRODUCT
BEFORE INSERT OR UPDATE OR DELETE ON PRODUCTS
BEGIN
DECLARE
WEEKDAY VARCHAR2(1);
BEGIN
SELECT TO_CHAR(SYSDATE, 'd') INTO WEEKDAY FROM DUAL;
IF (WEEKDAY = '1' OR WEEKDAY = '7') THEN
RAISE_APPLICATION_ERROR(-20001, '你没有权限周末修改');
END IF;
END;
END;
此时可以把系统时间修改下,当你在insert或者update、delete的时候你可以看到效果如何
DELETE FROM PRODUCTS WHERE PRODUCT_ID = '1';
INSERT INTO PRODUCTS VALUES (5, '微波炉');