sql使用create trigger创建触发器
Create trigger (触发器名)
{before /after} (触发事件) on (表名)
Referencing
new/oldrow as (变量)
For each{row/statement}
When(触发条件)(触发动作体)
例1.当对sc表的grade属性进行修改时,若分数增加了10%,则将此次操作记录到另一个表sc_u中,其中oldgrade是修改前的分数,newgrade是修改
create trigger sc_t
after update of grade on sc
referencing
oldrow as oldtuple,
newrow as newtuple
for each row
when(newtuple.grade>1.1*oldtuple.grade)
insert into sc_u(sno,cno,oldgrade,newgrade)
values(oldtuple.sno,oldtuple.cno,oldtuple.grade,newtuple.grade)
查看触发器:show triggers;
查看触发器创建语句:show create trigger 触发器名字;
删除触发器:drop trigger 触发器名 on 表名
5.23将每次对表student的插入操作所增加的学生个数记录到表student-insertlog中。
CREATE TRIGGER student_count
AFTER INSERT ON student
FOR EACH ROW
INSERT INTO studentinsertlog(numbers)
SELECT COUNT(*) FROM student;