1. The experiment purpose

Master the design and use of database triggers

   2. The contents and requirements of the experiment

Definition  BEFORE  Trigger and  AFTER  trigger . Be able to understand the function and execution principle of different types of triggers , Verify the validity of the trigger .

3. The experimental steps

1. AFTER trigger

① stay Lineitem Define a table UPDATE trigger , When modifying order details （ That is, modify the order details price extendeprice、 discount discount、 tax rate tax） when , Automatically modify the order Orders Of TotalPrice, To maintain data consistency .

Source code ：

CREATE TRIGGER UP_lineitem AFTER UPDATE ON lineitem FOR EACH ROW
BEGIN
	UPDATE orders
SET totalprice = totalprice + (
	new.extendedprice * (1 - new.discount) * (1 + new.tax) - old.extendedprice * (1 - old.discount) * (1 + old.tax)
)
WHERE
	orderkey = new.orderkey;

END

② stay Lineitem Define a table INSERT trigger , When adding an order detail , Automatically modify the order Orders  Of TotalPrice, To keep the data consistent .

Source code ：

CREATE TRIGGER IN_lineitem AFTER INSERT ON lineitem FOR EACH ROW
BEGIN
	UPDATE orders
SET totalprice = new.extendedprice * (1 - new.discount) * (1 + new.tax)
WHERE
	orderkey = new.orderkey;


END

③ stay Lineitem  Define a table DELETE  trigger , When deleting an order detail , Automatically modify the order  Orders  Of TotalPrice, To maintain data consistency .

Source code ：

CREATE TRIGGER DE_lineitem AFTER DELETE ON lineitem FOR EACH ROW
BEGIN
	UPDATE orders
SET totalprice = totalprice - old.extendedprice * (1 - old.discount) * (1 + old.tax)
WHERE
	orderkey = old.orderkey;

END

④ Verify that the above three triggers work .

verification UPDATE Trigger source code ：

SELECT
	*
FROM
	lineitem
WHERE
	orderkey = 11;

SELECT
	*
FROM
	orders
WHERE
	orderkey = 11;

UPDATE lineitem
SET tax = 0.16
WHERE
	orderkey = 11;

SELECT
	*
FROM
	lineitem
WHERE
	orderkey = 11;

SELECT
	*
FROM
	orders
WHERE
	orderkey = 11;

verification UPDATE Screenshot of trigger result ：

Before the change lineitem surface ：

Before the change orders surface ：

After modification lineitem surface ：

After modification orders surface ：

verification INSERT Trigger source code ： 

SELECT
	*
FROM
	lineitem
WHERE
	orderkey = 15;

SELECT
	*
FROM
	orders
WHERE
	orderkey = 15;

INSERT INTO lineitem
VALUES
	(
		15,
		5001,
		15634,
		1,
		45,
		95402,
		0.10,
		0.13,
		'',
		'',
		NULL,
		NULL,
		NULL,
		' ',
		' ',
		NULL
	);

SELECT
	*
FROM
	lineitem
WHERE
	orderkey = 15;

SELECT
	*
FROM
	orders
WHERE
	orderkey = 15;

verification INSERT Screenshot of trigger result ：

Before insertion lineitem surface ：

Before insertion orders surface ：

After inserting lineitem surface ：

After inserting orders surface ： 

verification DELETE Trigger source code ：

SELECT
	*
FROM
	lineitem
WHERE
	orderkey = 10;

SELECT
	*
FROM
	orders
WHERE
	orderkey = 10;

DELETE
FROM
	lineitem
WHERE
	orderkey = 10
AND partkey = 20521;

SELECT
	*
FROM
	lineitem
WHERE
	orderkey = 10;

SELECT
	*
FROM
	orders
WHERE
	orderkey = 10;

verification DELETE Screenshot of trigger result ：

Before deleting lineitem surface ：

Before deleting orders surface ： 

After deleting lineitem surface ：  After deleting orders surface ：

        2.BEFORE  trigger

① stay  Lineitem  Define a table  BEFORE UPDATE  trigger , When modifying the quantity in the order details

（quantity） when , Check the supply list first  PartSupp  Available quantity in  availqty  Is it enough .

Source code ：

CREATE TRIGGER BUP_lineitem BEFORE UPDATE ON lineitem FOR EACH ROW
BEGIN
	SELECT
		availqty INTO @UP_availqty
	FROM
		partsupp
	WHERE
		partkey = new.partkey
	AND suppkey = new.suppkey;


IF (
	@UP_availqty - (new.quantity - old.quantity) >= 0
) THEN

BEGIN
	UPDATE partsupp
SET availqty = availqty - (new.quantity - old.quantity)
WHERE
	partkey = new.partkey
AND suppkey = new.suppkey;


END;


END
IF;


END

② stay Lineitem  Define a table BEFORE INSERT  trigger , When inserting order details , Check the supply list first  PartSupp  Available quantity in availqty  Is it enough .

Source code ：

CREATE TRIGGER BIN_lineitem BEFORE INSERT ON lineitem FOR EACH ROW
BEGIN
	SELECT
		availqty INTO @IN_availqty
	FROM
		partsupp
	WHERE
		partkey = new.partkey
	AND suppkey = new.suppkey;


IF (
	@IN_availqty - new.quantity >= 0
) THEN

BEGIN
	UPDATE partsupp
SET availqty = availqty - (new.quantity)
WHERE
	partkey = new.partkey
AND suppkey = new.suppkey;


END;


END
IF;


END

③ stay Lineitem  Define a table BEFORE DELETE  trigger , When deleting order details , The ordered quantity of this order detail item shall be returned to the corresponding part supply record .

Source code ：

CREATE TRIGGER BDE_lineitem BEFORE DELETE ON lineitem FOR EACH ROW
BEGIN
	UPDATE partsupp
SET availqty = availqty + (old.quantity)
WHERE
	partkey = old.partkey
AND suppkey = old.suppkey;


END

④ Verify that the above three triggers work .

verification UPDATE Trigger source code ：

SELECT
	*
FROM
	lineitem
WHERE
	partkey = 24941;

SELECT
	*
FROM
	partsupp
WHERE
	partkey = 24941;

UPDATE lineitem
SET quantity = 6
WHERE
	orderkey = 6
AND partkey = 24941;

SELECT
	*
FROM
	lineitem
WHERE
	partkey = 24941;

SELECT
	*
FROM
	partsupp
WHERE
	partkey = 24941;

verification UPDATE Screenshot of trigger result ：

Before updating lineitem surface ：

Before updating partsupp surface ： 

After the update lineitem surface ：

After the update partsupp surface ： 

  verification INSERT Trigger source code ：

SELECT
	*
FROM
	lineitem
WHERE
	partkey = 44117;

SELECT
	*
FROM
	partsupp
WHERE
	partkey = 44117;

INSERT INTO lineitem
VALUES
	( 
		5,
		44117,
		533,
		1,
		12,
		6666.00,
		0.69,
		0.39,
		'',
		'',
		NULL,
		NULL,
		NULL,
		' ',
		' ',
		NULL
	);

SELECT
	*
FROM
	lineitem
WHERE
	partkey = 44117;

SELECT
	*
FROM
	partsupp
WHERE
	partkey = 44117;

verification INSERT Screenshot of trigger result ：

Before insertion lineitem surface ：

Before insertion partsupp surface  

After inserting lineitem surface ：  

After inserting partsupp surface ： 

verification DELETE Trigger source code ：

SELECT
	*
FROM
	lineitem
WHERE
	partkey = 44117;

SELECT
	*
FROM
	partsupp
WHERE
	partkey = 44117;

DELETE
FROM
	lineitem
WHERE
	partkey = 44117
AND orderkey = 9;

SELECT
	*
FROM
	lineitem
WHERE
	partkey = 44117;

SELECT
	*
FROM
	partsupp
WHERE
	partkey = 44117;

verification DELETE Screenshot of trigger result ：

Before deleting lineitem surface ：

Before deleting partkey surface ： 

After deleting lineitem surface ： 

After deleting partkey surface ：