数据库触发器

触发器只能由用户对DB中表的操作（即插入，删除和修改3中操作触发）,因此可以用触发器维护表的一致性。

Note

触发器不仅可以用来维护表数据的一致性，还可以实现更加复杂的操作

触发器分为AFTER触发器和INSTEAD OF触发器两种
AFTER触发器，表示是在增，删，改这些操作之后进行执行。AFTER只能建立在表上，每个触发操作可以定义多个AFTER触发器，可以中sp_settriggerorder指定第一个和最后一个AFTER触发器的触发顺序，其他的则不确定。
INSTEAD OF 触发器可在表上或者视图上定义，每个触发操作只能定义一个INSTEAD OF 触发器。INSTEAD OF触发器主要是用来替换触发的操作（增，删，改）,即不执行触发操作，而是执行触发器。
触发器语法：
CREATE TRIGGER 触发器名
ON {表名|视图名}
{FOR|AFTER|INSTEAD OF} {[INSERT][,][UPDATE][,][DELETE]}
AS
SQL语句块
RETURN

Note

一旦某操作触发了某个触发器，系统就会将该操作与该操作的触发器作为一个事物进行提交或回滚。
系统为触发器提供两张表，即inserted和deleted表，其表结构与定义触发器的表结构一致，以便程序员编程时应用。当触发操作是“插入”时，新数据会写入到inserted;当触发操作是“删除”时，删除的操作会保存在deleted表中；当触发操作是“修改”时，会将先删除的数据保存在deleted中，将新修改的数据保存在inserted中。

由删除操作激发的主表删除触发器实例：

create trigger studentDel
on student 
after delete
as
if @@rowcount = 0 return
delete selectCourse from selectCourse as select,deleted d
where select.studentId=d.id
return

主表修改操作触发器：

create trigger studentUp
on student
after update
as
declare @num_rows INT
select @num_rows =@@rowcount
if @num_rows=0 return
if update(id)
begin
    if @num_rows > 1
    begin
        RAISERROR 53333
        ROLBACK TRANSACTION
        RETURN
    end
        update selectCourse  set studentId=in.id from selectCourse as select,inserted as in,delectd as del
where select.studentId=del.id
    end
return

A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table.Some uses for triggers are to perform checks of values to be inserted into a tabole or to perform caculations on values involved in an update.

A trigger is defined to activate when a statement inserts,updates or deletes row in the associated table.These row operations are trigger events.For example,rowscan be inserted by INSERT or LOAD DATA statements, and an insert trigger activates for each inserted row.A triger can be set to active either before or adter the trigger event.For ecample,you can have a trigger activate before each row that is inserted into a table or after each row that is updated.

Imporant

MySQL triggers activate only for changes made to tables by SQL statements.They do not activate for changes in views,nor by changes to tables made by APIS that do not transmit SQL statements to the MySQL Server.This means that triggers are not activated in the following tow cases:
- Triggers are not activated changes in INFORMATION_SCHEMA or performance_schema tables,because these tables are actually views.
- Triggers are not activated by updates made using the NDB API.

The following sections describe the syntax for creating and dropping triggers,show some examples of how to use them,and indicate how to obtain trigger metadata.

Trigger Syntax and Examples

To create a trigger or drop a trigger, use the CREATE TRIGGER or DROP TRIGGER statement.

create table account (acct_num int,amount decimal(10,2));
create trigger ins_sum 
before insert 
on account 
for each row 
set @sum=@sum+NEW.amount;

The statement following for each row defines the trigger body;that is ,the statement to execute each time the trigger activates,which occurs once for each row affected by the triggering event.In the example, the trigger body is a simple SET that accumulates into a user variable the values inserted into the amount column.The statement refers to the column as NEW.amount which means “the value of the amount column to be inserted into the new row.”
To use the trigger ,set the accumulator variable to zero, execute an INSERT statement,and then see what value the variable has afterward:

set @sum =0;
insert into account values(137,14,98),(141,1937.50)(97,-100.00);

In this case, the value of @sum after the INSERT statement has executed is 14.98+1937.50-100,or 1852.48.

To destroy the trigger,use a DROP TRIGGER statement .You must specify the schema name if the trigger is not in the default schema:
DROP TRIGGER test.ins_sum;
If you drop a table,any triggers for the table are also dropped.
Trigger names exist in the schema namespace,meaning that all triggers must have unique names within a schema.Triggers in different schemas can have the same name.
It is possible to define multiple triggers for a given table that have the same trigger event and action time.For example,you can have two BEFORE UPDATE triggers for a table.By default,triggers that have the same trigger event and action time activate in the order they were created.