(4)触发器练习

(1)创建product表和operate表

CREATE TABLE product(id INT(10) NOT NULL UNIQUE PRIMARY KEY,
    NAME VARCHAR(20) NOT NULL,
    FUNCTION VARCHAR(50),
    company VARCHAR(20) NOT NULL,
    address VARCHAR(50)

    );

CREATE TABLE operate(o_id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
    op_name VARCHAR(20) NOT NULL,
    op_time TIME NOT NULL

    );

(2)创建product_bf_insert触发器

CREATE TRIGGER product_bf_insert BEFORE INSERT
ON product FOR EACH ROW

INSERT INTO operate VALUES(NULL,'Insert product',NOW());

创建完成后，执行select语句来查看触发器的基本信息

SELECT  * FROM information_schema.triggers WHERE trigger_name='product_bf_insert';

(3)创建product_af_update触发器

CREATE TRIGGER product_af_update AFTER UPDATE
ON product FOR EACH ROW

INSERT INTO operate VALUES(NULL,'update product',NOW());

(4)创建product_af_del触发器

CREATE TRIGGER product_af_del AFTER DELETE
ON product FOR EACH ROW

INSERT INTO operate VALUES(NULL,'delete product',NOW());

(5)对product表进行操作

插入一条记录

INSERT INTO product VALUES(1,'abc','治疗感冒','北京abc制药厂','北京市昌平区');

更新表的记录

UPDATE product SET address='北京市海定区' WHERE id=1;

删除表的一条记录

DELETE FROM product WHERE id=1;

查看operate表的记录

SELECT * FROM operate;

(6)删除触发器

DROP TRIGGER product_bf_insert;

执行select语句来查看触发器是否还存在

SELECT  * FROM information_schema.triggers WHERE trigger_name='product_bf_insert';