摘自 daisylh 博客
1.觸發器的查詢:
show triggers;
select action_statement from information_schema.triggers where trigger_name='ins_sum' and trigger_schema='demodb';/*笨笨的寫法
show triggers like 'account';/*account為table名稱
show triggers like 'acc%';/*acc為table名稱中的相關字母
show triggers like '%t%';/*t為table名稱中的一個字母
2.觸發器的刪除:
drop trigger demodb.ins_sum;
3.觸發器的建立:
語法如下:
Create trigger 觸發器名稱 /*觸發器名稱字符最多為64個
{before | after} /*觸發時間
{insert | update | delete} /*觸發事件
on table名稱 /*觸發表名
for each row /*觸發間隔
觸發器SQL語句;
p.s.1.要更新或刪除的字段/欄位用new | old .欄位名來表示
2.在for each row後的觸發器SQL語句若有多個可用begin ...end
3.同一張表不能存在2個如befor insert/before update/before delete/after insert/after update/after delete觸發器
4.觸發器實例:
I.有關test1,test2,test3,test4
A.創建table:test1,test2,test3,test4
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0);
B.創建testref觸發器
DELIMITER |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;|
DELIMITER ;
C.執行相關SQL時會觸發
INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);
INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
INSERT INTO test1 VALUES
(1),(3),(1),(7),(1),(8),(4),(4);
D.查詢結果
select * from test1;
select * from test2;
select * from test3;
select * from test4;
II.有關account
A.創建account
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
B.創建2個觸發器
CREATE TRIGGER ins_sum BEFORE INSERT ON account
For each row set @sum=@sum+New.amount;
CREATE TRIGGER upd_check BEFORE UPDATE ON account
for each row
begin
if new.amount<0 then
set new.amount=0;
elseif new.amount>100 then
set new.cmount=100;
end if;
end;
C.執行相關SQL查詢時觸發
SET @sum = 0;
Insert into account values(137,14.98),(141,1937.50),(97,-100.00);/*觸發ins_sum
select @sun as Total';
Update account set amount=20 where acct_num=137;/*觸發upd_check