数据库系统实训——实验七——触发器
第一部分:样例库的应用1)INSERT 触发器语句:CREATE TABLE orders_log(change_idintNOT NULL AUTO_INCREMENT,changed_ondatetime NOT NULL ,change_type char(1)NOT NULL ,order_numintNOT NULL ,PRIMARY KEY (change_id)) ENGINE=IN
第一部分:样例库的应用
1) INSERT 触发器
语句:
CREATE TABLE orders_log
(
change_id int NOT NULL AUTO_INCREMENT,
changed_on datetime NOT NULL ,
change_type char(1) NOT NULL ,
order_num int NOT NULL ,
PRIMARY KEY (change_id)
) ENGINE=INNODB;
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO orders_log(changed_on, change_type, order_num)
VALUES(Now(),‘A’, NEW.order_num);
END;
截图:
语句:
INSERT INTO orders(order_date, cust_id)
VALUES(Now(), 10001);
SELECT * FROM orders_log;
截图:
2) DELETE触发器
语句:
CREATE TABLE archive_orders
(
order_num int NOT NULL,
order_date datetime NOT NULL ,
cust_id int NOT NULL
) ENGINE=InnoDB;
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO orders_log(changed_on, change_type, order_num)
VALUES(Now(),‘D’, OLD.order_num);
INSERT INTO archive_orders(order_num, order_date, cust_id)
VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;
截图:
语句:
INSERT INTO orders(order_date, cust_id) VALUES(Now(), 10001);
SELECT * FROM orders_log;
SELECT * FROM archive_ orders;
截图:
3) UPDATE触发器
语句:
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);
CREATE TRIGGER updateORDER BEFORE UPDATE ON ORDERS
FOR EACH ROW
BEGIN
INSERT INTO orders_log(changed_on, change_type, order_num)
VALUES(Now(),‘U’, OLD.order_num);
SET NEW.ORDER_DATE = NOW();
END;
截图:
语句:
UPDATE orders SET cust_id=’10006’ WHERE cust_id=’10005’;
SELECT * FROM orders_log;
SELECT * FROM orders;
截图:
第二部分:所选课题数据库的应用
1)INSERT 触发器
语句:
CREATE TABLE orders_log
(
change_id int NOT NULL AUTO_INCREMENT,
change_type char(1) NOT NULL ,
order_num int NOT NULL ,
PRIMARY KEY (change_id)
) ENGINE=INNODB;
CREATE TRIGGER neworder AFTER INSERT ON movies
FOR EACH ROW
BEGIN
INSERT INTO orders_log(change_type, order_num)
VALUES(‘A’, NEW.year);
END;
截图:
语句:
INSERT INTO movies(year)
VALUES(1980);
SELECT * FROM orders_log;
截图:
2) DELETE触发器
语句:
CREATE TABLE archive_orders
(
year int NOT NULL,
length int NOT NULL
) ENGINE=InnoDB;
CREATE TRIGGER deleteorder BEFORE DELETE ON movies
FOR EACH ROW
BEGIN
INSERT INTO orders_log(change_type, order_num)
VALUES(‘D’, OLD.year);
INSERT INTO archive_orders(year,length)
VALUES(OLD.year, OLD.length);
END;
截图:
语句:
INSERT INTO movies(year) VALUES(118);
SELECT * FROM orders_log;
SELECT * FROM archive_ orders;
截图:
3) UPDATE触发器
语句:
CREATE TRIGGER updatevendor BEFORE UPDATE ON studio
FOR EACH ROW SET NEW.name = Upper(NEW.name);
CREATE TRIGGER updateORDER BEFORE UPDATE ON MOVIES
FOR EACH ROW
BEGIN
INSERT INTO orders_log(change_type, order_num)
VALUES(‘U’, OLD.year);
SET NEW.name = NOW();
END;
UPDATE movies SET year=’1980’ WHERE year=’1977’;
SELECT * FROM orders_log;
SELECT * FROM movies;
截图:
更多推荐
所有评论(0)