若declare报missing semicolon的错误,在sql语句前加上
DELIMITER $$
创建触发器
DELIMITER $$
create trigger [触发器名]
AFTER [操作名:如update\delete\insert]
ON [表名]
for each row
begin
#要执行的操作
end;
例:
DELIMITER $$
create trigger TRI_Lineitem_Price_UPDATE AFTER UPDATE
ON lineitem
for each row
begin
DECLARE L_valuediff real;#中间变量声明
set L_valuediff=NEW.extendedprice*(1-NEW.discount)*(1+NEW.tax)-
OLD.extendedprice*(1-OLD.discount)*(1+OLD.tax);
UPDATE orders set totalprice = totalprice + L_valuediff
WHERE orderkey = NEW.orderkey;
end;