Shulou(Shulou.com)06/01 Report--

Product list-Table "goods" DDL:

CREATE TABLE goods (

Gid int (11) NOT NULL

Name varchar (20) DEFAULT NULL

Num smallint (6) DEFAULT NULL

PRIMARY KEY (gid)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Order form-Table "orders" DDL:

CREATE TABLE orders (

Oid int (11) NOT NULL

Gid int (11) DEFAULT NULL

Much smallint (6) DEFAULT NULL

Create_time datetime DEFAULT NULL

PRIMARY KEY (oid)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1. Insert trigger:

(1) after trigger:

Demand: after the customer buys a commodity, the inventory quantity of the goods decreases automatically.

Create trigger trigerafterInsertOrder

After insert

On orders

For each row

Begin

Update goods set num=num-new.much where gid=new.gid

End

(2) before trigger:

Demand: after the customer buys a commodity, the inventory quantity of the goods decreases automatically.

The create_time of the order table is the current time at the same time.

Create trigger trigerbeforeInsertOrder

Before insert

On orders

For each row

Begin

Update goods set num=num-new.much where gid=new.gid

Set new.create_time=now ()

End

Note: new represents a new row in the orders table.

2. Delete trigger:

Demand: after the customer cancels the order, the inventory quantity of the commodity table increases automatically.

Create trigger triggerDeleteOrder

After delete

On orders

For each row

Begin

Update goods set num=num+old.much where gid=old.gid

End

Note: old means that the orders table deletes rows.

3. Update trigger:

Demand: the customer modifies the purchase quantity of the order, and the inventory quantity of the commodity table changes automatically.

Create trigger triggerUpdateOrder

Before update

On orders

For each row

Begin

Update goods set num=num+old.much-new.much where gid=old.gid

End

Fourth, the difference between after and before triggers:

1. After first completes the addition, deletion and modification of data, and then triggers. The triggered statement is later than the monitored addition, deletion and modification operation, and cannot affect the previous addition, deletion and modification action.

2. Before first completes the trigger, then adds, deletes and modifies, and the triggered statement is prior to the monitored addition, deletion and modification.

Example 1: for the trigerbeforeInsertOrder trigger above, because the create_time field of the orders table is also assigned.

The assignment to the field create_time predates insert, so before triggers are used.

Note: if you use after triggers at this time, an Updating of new row is not allowed in after trigger error will be reported.

Example 2: the inspection of the burst business is that the customer buys a commodity, and if the quantity of the purchased goods exceeds the inventory quantity, the purchase quantity is the inventory quantity, and the inventory quantity of the commodity table changes automatically.

Create trigger triggerBeforeInsertOrder before insert on orders for each row

Begin

Declare kcNum int

Select num into kcNum from goods where gid=new.gid

If new.much > kcNum then

Set new.much=kuNum

End if

Update goods set num=num-new.much where gid=new.gid

End

Note: if you use after triggers at this time, an Updating of new row is not allowed in after trigger error will be reported.

Fifth, triggers reference row variables

The following figure clearly describes the row variables new and old of the trigger.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.